On a form,I have a record showing ID, firstname and lastname. I created a combo box to find a record based on the last name. The problem is that we may have three "Smiths". The drop down box will show all three "Smiths", but the first "Smith" record is displayed even if I choose the second or third "Smith." Any sugguestions on how to correct this? I appreciate any help you can offer.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Combo box will not pull up the correct record?
- Thread starter MariaL
- Start date
- Thread starter
- #3
I appreciate your help. OK, I know how to get to the query design view from the properties where the two fields are displayed. I'm assuming the expression will go in the criteria field, but what would the expression be for each field? Thank You.
An AND workaround at this early level of name tracking is a bad habit. In an extreme case, there might be two people with similar first/surnames (obviously different surnames). I have one database with 15 John Ryans (2 of them have an address differing only in house number!!)
You need get into the habit of using unique IDs (eg primary key index on table) as a hidden, bound column in the combo and search for surname via that ID. The ID will be unique for that combination of name+surname.
I suggest that you access the excellent Microsoft article called 'Ten Tips for Microsoft Access Developers' (chapter: Populate form fields based on a combo box selection). Teach a man to fish!!!!
In the meantime here's something to use. When you select a name on the combo, the form moves to that record (if you have used the built-in form utility).
TABLE: tbl_NAME; Fields are ID, NAME, SURNAME
COMBO: comb_NAME; ROWSOURCE SQL IS: SELECT [tbl_NAME].[ID],[tbl_NAME].[SURNAME],[tbl_NAME].[NAME]FROM [tbl_NAME]
After update event code on combo:
Private Sub comb_NAME_AfterUpdate()
'MOVE TO RECORD CHOSEN IN COMBO
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
If Nz(Me.combo_NAME, ""
<> "" Then
RS.FindFirst "[ID]=" & combo_NAME
Me.Bookmark = RS.Bookmark
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
End If
End If
End Sub
NOTE: Ensure that the DAO object model (3.6)is selected in the references section of the VBE.
Let me know how it goes.
Ceart go leor!
John
You need get into the habit of using unique IDs (eg primary key index on table) as a hidden, bound column in the combo and search for surname via that ID. The ID will be unique for that combination of name+surname.
I suggest that you access the excellent Microsoft article called 'Ten Tips for Microsoft Access Developers' (chapter: Populate form fields based on a combo box selection). Teach a man to fish!!!!
In the meantime here's something to use. When you select a name on the combo, the form moves to that record (if you have used the built-in form utility).
TABLE: tbl_NAME; Fields are ID, NAME, SURNAME
COMBO: comb_NAME; ROWSOURCE SQL IS: SELECT [tbl_NAME].[ID],[tbl_NAME].[SURNAME],[tbl_NAME].[NAME]FROM [tbl_NAME]
After update event code on combo:
Private Sub comb_NAME_AfterUpdate()
'MOVE TO RECORD CHOSEN IN COMBO
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
If Nz(Me.combo_NAME, ""
<> "" ThenRS.FindFirst "[ID]=" & combo_NAME
Me.Bookmark = RS.Bookmark
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
End If
End If
End Sub
NOTE: Ensure that the DAO object model (3.6)is selected in the references section of the VBE.
Let me know how it goes.
Ceart go leor!
John
- Status
Similar threads
