Display records in a combo box

[HandJT]

I have a search form that a user can search four ways, RMA, NCT, JOB, and DWG. Then when a user clicks on search, Access displays the results in another form labled "results". Example: I type in 456 under DWG, click search, and I see the results on the results form. This works great! However, if there are more than one record with that particular DWG on it, I thought then what? So, I added a combo box to the results form and coded it to where it will list all the records that have this DWG number on it. Now I'm stuck. I need to code it to where when I click on another number(record) from the list, Access displays the information on that record. Right now, it only displays the info from the first record, irregardless of what one I choose. Any suggestions on how to do this? Thanks in advance!!!

 

[M8KWR]

on your form, i think you need some code like the following: -

Sub combo1_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[IDNUMBER] = " & Me![combo1]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

If your stuck let me know

 

[HandJT]

Are you wanting me to put that in it's own private sub or in the code that I have already? This is what I have and it is giving me a compile Error: Expected End Sub. with the "Press the down arrow key to see more tickets." highlighed blue. Did I do this wrong? Thanks so much!!

'ADD THE NDW NUMBERS IN THIS COMBO BOX FOR EACH RECORD
'CONTAINING THIS DRAWING NUMBER IN IT.

Form_SearchResults.NDW_.RowSourceType = "Value List"
Form_SearchResults.NDW_.RowSource = ""
Do Until rs3.EOF
Form_SearchResults.NDW_.RowSource = _
Form_SearchResults.NDW_.RowSource & rs3![NDW] & ";"
rs3.MoveNext
Loop
Form_SearchResults.NDW_.RowSource)= _
Left(Form_SearchResults.NDW_.RowSource,_
Len(Form_SearchResults.NDW_.RowSource)-1)


'THEN WHEN I CLICK ON THE NUMBER
'DISPLAY THAT INFORMATION

If rs3.RecordCournt >= 1 Then
If rs3.RecordCount > 1 Then
Msgbox"Press the down arrow key to see more tickets." vbInformation
Sub NDW_AfterUpdate()
'Find the record that matches the control
Me.RecordsetClone.FindFirst "[NDW] = " &Me![NDW]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
End If
End If

 

[M8KWR]

The code that you have insert needs to be in its our procedure, in the afterupdate of the combo box that you are using.

Here's another option that you could use instead of using a combo box, use a list box, this way you would not have to inform the user that there are more option, because they would see them.

You need to make sure that the bound column is that of the ID number field you are using as well, it will not work otherwise.

If you want anymore help let me know.

 

[HandJT]

I understand what you want me to do now. Sorry. However, it is giving me a run time error of 7951: You entered an expression that has an invalid reference to the Recordset clone property. Any ideas on what's going on?

 

[M8KWR]

change recordsetclone to RS3.

I think that should work, cause you have changed the name in your code.

I'm lazy and do not do it this way.

 

[HandJT]

So it should be Me.RS3.FindFirst "[NDW] = " & Me![NDW}?

 

[M8KWR]

i believe so, try it and let me know if it works or not

 

[HandJT]

Sorry, didn't work. It gave me a compile error: Method or data member not found. When I typed Me. rs3 didn't show up in the list. Could be part of the reason, maybe?