GoToRecord Help

[rayhawk]

I am trying to set up an easy way to navigate through records. I would like to be able to type in to a combo box and automatically search records, as well as navigate to the record. Right now I have a combo box that will look at the table for part number, and as you type it will fill in based on the available part numbers. Same for the description. What I want is to use the onchange event to go to the record that contains that part number/description. I have tried this:

Private Sub cmbPMAlookup_Change()
    DoCmd.GoToRecord , , acGoTo = cmbPMAlookup
End Sub

This is not working, I am just getting an error. Also, I want to be able to do the same with a text string (description) as well. What is the best way to accomplish this?

[weekend00]

Please try:
Private Sub cmbPMAlookup_Change()
form.Recordset.findfirst "[part number]='"+cmbPMAlookup +"'"
End Sub

if [part number] is a number, then take off the two '

[pbaldy]

Have you tried the combo box wizard, choosing the third option, "Find a record..."?

[rayhawk]

I only get two options:

I want the combo box to look up values in a table or a query

OR

I will type in the values that I want.

[pbaldy]

The form would need to be bound to the table to see the third option. Here's the code it will produce:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AccNum] = '" & Me![cmbFindAccNum] & "'"
Me.Bookmark = rs.Bookmark

You would replace the field and combo names with yours. If the field is numeric, you'd also drop the single quotes surrounding it:


rs.FindFirst "[AccNum] = " & Me![cmbFindAccNum]

[rayhawk]

The form would need to be bound to the table to see the third option. Here's the code it will produce:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AccNum] = '" & Me![cmbFindAccNum] & "'"
Me.Bookmark = rs.Bookmark

You would replace the field and combo names with yours. If the field is numeric, you'd also drop the single quotes surrounding it:


rs.FindFirst "[AccNum] = " & Me![cmbFindAccNum]

My form pulls data from more than one table, so I don't know if I can bind the form to the table directly.

What field should be where you have "AccNum"? Your routine is finding the index of the item I pick in the combo box (a number) but what I want to do is match the text of the item I pick with the text in a particular field on the form, which I can't seem to get to work right. I tried adding .Text at the end which does retrieve the text value of the field, but nothing is happening. Any idea what I am missing here?

    Set rs = Me.Recordset.Clone
    rs.FindFirst Me![pmaPartNumber] = Me![cmbPMAlookup].Text
    Me.Bookmark = rs.Bookmark

[weekend00]

you may want to try what I suggested. but need to change [part number] to [pmaPartNumber].

[rayhawk]

you may want to try what I suggested. but need to change [part number] to [pmaPartNumber].

Thanks, I missed that before. I got an error that I needed to add the table name, and it was giving me the index (not the text), so I tried this:

Form.Recordset.FindFirst "Projects." + "[pmaPartNumber]='" + cmbPMAlookup.Text + "'"

I added what is shown in bold, and now it works. Thanks guys!

[bundy75]

HELP!!!

My problem is the same but can't seem to get working with wizard or manually

I have a form "Existing Clients" and a combo box "Surname_combo" and wish to populate the form when a surname is selected from the "Client" table

Have tried
Form.Recordset.FindFirst "[Client.Surname]='" + Surname_combo.Text+ "'"
and
Form.Recordset.FindFirst "Existing Clients." + "[Client.Surname]='" + Surname_combo.Text+ "'"

and multiple others but still no luck any suggestions???

[bundy75]

Latest try

Form.Recordset.FindFirst "Clients." + "[Surname]='" + Surname_combo.Text+ "'"

compiles and runs just doesn't do anything

[rayhawk]

Latest try

Form.Recordset.FindFirst "Clients." + "[Surname]='" + Surname_combo.Text+ "'"

compiles and runs just doesn't do anything

Put a break on the next line of code to stop it while running and hover your mouse over the parameters to check the values at runtime. You can't check them all but this may tell you something.

Also check the spelling of the table name, you say your table is named "Client" but your example says "Clients".