Command Button to Open form from different record in MS Access 2007

0
votes

I have a multi-record form in Access 2007 with a command button at end of the line.

The current record could be the first for instance but I want to click on the button on the 4th record (without actually having to go into record) and open a form with info related to 4th record.

Currently it brings up info related to 1st record because the cursor was in a field on that 1st record.

When I click on the button on the 4th record it still seems to think its on the first record.

My code is:

Dim stDocName As String 
DoCmd.OpenForm stDocName, , , "WorkAddressId = forms!Persons!WorkAddressId"
3
ms-accessms-access-2007
How does the command button on row 1 differ from row 4? How does it know which row it is on? You're referencing [WorkAddressId], where is that control? Do you have [WorkAddressid1], [WorkAddressid2] ... [WorkAddressid4]? - MoondogsMaDawg
Command button is on every line at end of the fields - Mike
Ok is [WorkAddressId] one of the controls on each line, or is there just one on the form that's changing value when you make selections? Based on your answer I should be able to solve. - MoondogsMaDawg
it's one of the fields on each line (record) - Mike

3 Answers

0
votes

I want to click on the button on the 4th record (without actually having to go into record)

But you will go into the 4th record, when you click that button.

Adjust your secret code of the button to open the form filtering on the ID of the form. This ID will be the ID of the record you are clicking - like this where you concatenate the Id into the criteria string:

Dim stDocName As String 
Dim Id As Long

' Name of the form to open.
stDocName = "NameOfYourFormToOpen"
' Pick the Id of the current record.
Id = Me!WorkAddressId

DoCmd.OpenForm stDocName, , , "WorkAddressId = " & Id & ""
0
votes

You need custom code in each command button. The code you have now is not getting the value of the control on that line, they are all looking at the same control, which I assume has the ID of the first record.

First, make sure the names of each [WorkAddressId] control on each row has a separate name, like: [WorkAddressId1], [WorkAddressId2]...

Then go to each command button and make the form reference match which row it's on. For example, the button on row 1 should be:

DoCmd.OpenForm stDocName, , , "WorkAddressId = " & [Forms]![Persons]![WorkAddressId1]

Where row one's [WorkAddressId] control name is actually called [WorkAddressId1].

Put this in the code for button on row 2:

DoCmd.OpenForm stDocName, , , "WorkAddressId = " & [Forms]![Persons]![WorkAddressId2]

and so on.

0
votes

Apologies to all. I had started to experiment with different things in Access and on that form had created a label (click on it to see work address) to see how it would look on the form. When I put back in a control button it worked fine. So much for experimenting!!!

Thanks to all who tried to help in any way. Much appreciated.

Mike