Find a record on my form ... combo box not working

[thefourthwall]

I made a combo box using the built-in wizard to "find a record on my form" based on the record selected in the drop-down list.

The list is based on employees' last names, and includes first name, mi, and a value called "tzs."

This method works well until I encounter multiple last names that are the same, like 'smith,' or 'parker' and the code picks only the first entry. If I click on the 2nd instance of 'parker' the form does not update from the 1st instance, but works fine on unique last names.

I never realized the built-in code wouldn't take this into account, and need help resolving. The row source is

SELECT qryEmployeeDevice.LAST_NAME, qryEmployeeDevice.FIRST_NAME, qryEmployeeDevice.MIDDLE_INITIAL, qryEmployeeDevice.TZS_CODE FROM qryEmployeeDevice ORDER BY qryEmployeeDevice.LAST_NAME, qryEmployeeDevice.FIRST_NAME;

and the VBA code on the control's After Update event is

Private Sub Combo88_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LAST_NAME] = '" & Me![Combo88] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks for your help.

 

[Darrylles]

Hi thefourth,
[tt]
SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME & " ," & FIRST_NAME AS FULL_NAME, qryEmployeeDevice.TZS_CODE FROM qryEmployeeDevice ORDER BY LAST_NAME, FIRST_NAME;

rs.FindFirst "[FULL_NAME]=" & Me!Combo88.column(3)
[/tt]
Where column(3) is the 4th column in the combo (FULL_NAME).
The combo source will be roughly the same as the query above to build the FULL_NAME field into column(3).

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[thefourthwall]

Hi Darrylle,

Thanks for the reply - returned to work today and find that your sql code works well but am getting a run-time error 3070 when running your suggested code for the "after update" event. The test of it is "jet database engine does not recognize 'FULL_NAME' as a valid field name or expression.

Not yet finding what is missing ... help?

thanks,
thefourthwall

 

[thefourthwall]

I'm sorry if my original post is something that has been covered before - I searched but did not find a previous instance of this problem.

A combo box to select people by their last names works except when there are multiple same last names ... really need help to make it work, thanks

 

[kjv1611]

thefourthwall,

Post the VBA code that's giving the error.

--

"If to err is human, then I must be some kind of human!" -Me

 

[thefourthwall]

kjv1611,

thanks. it is

Private Sub Combo80_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[FULL_NAME]='" & Me!Combo80.Column(3) & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

and the query behind the combo box is

SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME & " ," & FIRST_NAME AS FULL_NAME, qryEmployeeDevice.TZS_CODE FROM qryEmployeeDevice ORDER BY LAST_NAME, FIRST_NAME;

 

[dhookom]

This won't work unless you have a FULL_NAME in the Record Source of your form.

I'm not sure why you don't have and use a primary key field for this.

Duane
Hook'D on Access
MS Access MVP

 

[thefourthwall]

dhookom,

I assumed this was the case, and assumed (!) that by creating this expression FULL_NAME in the query that it might be passed over to the vba code. Beginners and their assumptions!

So you're saying I can't do that - fair enough. One of fields in the query behind the form is employee id, which is unique ... guess i could use that instead of FULL_NAME to get the desired results, is that right?

 

[kjv1611]

If you just want a combo box that is based off of all the values in a table, you could just as easily use Access' built-in Combo Box wizard that will basically create it for you. That way, all of this stuff just won't even matter.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

Just use the primary key field. Anything else doens't make much sense to me when you can use a unique identifier.

Duane
Hook'D on Access
MS Access MVP

 

[thefourthwall]

kjv1611,

I had done just that, but the form wouldn't update if there were multiple users with the same last name in the combo box's list: only the first user's information would display, and that is what I was hoping to get past.

 

[thefourthwall]

dhookom,

I did that, with the following code for the combo box (using Access's built-in wizard):

SELECT qryEmployeeDevice.LAST_NAME, qryEmployeeDevice.FIRST_NAME, qryEmployeeDevice.MIDDLE_INITIAL, qryEmployeeDevice.TZS_CODE, qryEmployeeDevice.EID
FROM qryEmployeeDevice
ORDER BY qryEmployeeDevice.LAST_NAME, qryEmployeeDevice.FIRST_NAME;

The primary key is EID and the bound column is 5; it seems now when I run the combo box the form is updating correctly for each user who shares the same last name. I made the EID column width 0.02" so it doesn't actually display, and the process seems to work. Thank you.

 

[dhookom]

You can set the column width to 0".

Duane
Hook'D on Access
MS Access MVP

 

[thefourthwall]

Nice. Its working now better than I thought it would - it actually works. Appreciate the help.