"Detail" button on continuous form -- How can I link it to the specific record?

[WesHarding]

Hello Wonderful Community!

I have a continuous form that runs a query to search through contacts and a little other information. Once output, the user will see your typical continuous form with name, and some other detail. I have however, added a 'detail' button to each record. I have this designed to open another form that shows the one to many relationship that each record holds with that table of information.



My trouble with this, is that I cannot seem to get this form to show the proper records. It simply is stuck showing the first record of the table. This leads me to believe it is a master-child field issue. If anyone has suggestions on how I could perhaps try to find success with my small search database I would greatly appreciate it!

Sincerely,

Wesley

[aytee111]

When you open the form, use a filter which holds the value of the record you are on:
DoCmd.OpenForm "formname",,"FieldOnNextForm=" & me!FieldOnThisForm
Add single quotes if it is a text field.

[WesHarding]

The basis of this seems perfect. I just tried however, and I'm still having the same issues. Perhaps there may be another problem.

DoCmd.OpenForm "sfrm_farm_detail", , "DetailFarmerID=" & Me!ContactFarmerID
Me.Requery

[aytee111]

Sorry, it should be "where", not filter. Add an extra comma after form name.

[WesHarding]

I believe I have it functionally working now. I am having a parameter issue though. It can't seem to find the field on my form..any suggestions?

[aytee111]

Which field are you using? Generally it is the PK for the second form, which is a field on the first form.

[WesHarding]

This is the case with me, PK in second form with secondary key in main.

However, I am record sourcing the second form directly from the table. Could this be the source of my issue possibly?

[aytee111]

I assume you mean that the second form has the same record source as the main form? No, that is not a problem.

Can you see the field that you are using to open the second form? I guess not - " It can't seem to find the field on my form". Maybe you can explain this further? If you can see it and the button that you click is on the same row then this wouldn't happen.

[WesHarding]

Currently the main form has a record source from a query, that sources from a contacts table and another table including other data. The second form record sources directly from another table. This table contains the DetailFarmerID that is linked to the FarmerContactID which is contained in a table included in the query for the first form.

My biggest confusion is that these functions are not being recognized at all, which is why I said it couldn't seem to find the field on the form.

Oh it's always the little things in Access that cause the most grief..

Could you please explain more into "If you can see it and the button that you click is on the same row then this wouldn't happen."

[aytee111]

Sure is confusing - and frustrating! OK, so you have a continuous form with multiple records. On each record of that form you have a button which says open another form and show me the details of this particular record. When you click that button it should open the second form and show that record, which is where we added that to the OpenForm statement: the form's record source is changed by Access to show the first record where that WHERE is true, in this case it will show the first record on the record source table that matches the DetailFarmerID (same as ContactFarmID in the first form's record source).

[WesHarding]

That is precisely what is happening! And it works for the first contact perfectly. I just don't see why it will not follow suit throughout the rest of the records, as they all have their own unique ContactFarmID.

Would inputting the DoCmd.OpenForm "sfrm_farm_detail", , "DetailFarmerID=" & Me!ContactFarmerID into the macro builder's "where" clause possibly give a different result?

[aytee111]

Where's the third comma?

[WesHarding]

Ah I forgot the third comma! I am getting an error now, but I believe I am very close.
Here is the VBA i'm using

Private Sub Command77_Click()
DoCmd.OpenForm "sfrm_farm_detail", , , "FarmerID = " & Me!DetailFarmerID
End Sub

[WesHarding]

Unfortunatly, now I recieve an error stating " Microscoft Access can't find the field "DetailFarmerID" referred in your expression"

[aytee111]

Clue on the error?