How to find record in a bound form.

[joel009]

Hi all!!
Situation. I have a combo box on my main form that I force users to select the Plant Name and Plant Code. When it is selected other buttons are made visible. One of the buttons on the form is for tblPlantInfo maintenance. The query for the combo box and the form address the same table, the combo box only brings in 2 fields while the form is a select * on the table.
The form works fine for editing and such but what I want to do is have the form open up with the entire recordset for tblPlantInfo but to be set on the record selected from the combo box.
Since the recordsource is the same for the 2 objects I thought I could use the ListIndex from the combo box (stored in a public variable) to dictate the record being viewed in the maintenance form but I can't figure out how to change rows in the Recordsource until the index's match. Everything I try on the form is read only!!

What am I missing?

Thanks,

Joel


Joel

 

[dhookom]

I would use the value property of the combo box (not listindex) to open the form to the specific record using the WHERE CONDITION of the DoCmd.OpenForm. You can then add a button to the opened form to Set the FilterOn to False.

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

Duane, This worked for me. The code is on the onload event.

Me.txtPlantCode.Value = PubstrPlantCode
Me.txtModelYear.Value = PubstrModelYear

Do Until Me.Recordset!PlantCode = PubstrPlantCode
Me.Recordset.MoveNext
Loop

Joel

 

[MajP]

The loop is not necessary. Use the findfirst method of the recordset object.

me.recordset.findfirst "PlantCode = " & pubStrPlantCode

Assuming numeric or:
me.recordset.findfirst "PlantCode = '" & pubStrPlantCode & "'

 

[PHV]

Why not simply use the Me.Recordset.FindFirst method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[joel009]

Thanks to you both!!! That works fine. I don't know what I was thinking. Too long a day already.

Joel

 

[dhookom]

I think it is better to open a form filtered and then only display more records if the user needs to. This method seems more efficient.

Duane
Hook'D on Access
MS Access MVP