Combo Box saves wrong information after combo box selection

[Alhassani]

I am trying to build a database to replace the Excel databases I created months ago. I've been googling and working through most of the issues that have come up, but this one has completely stumped me.

The main form to record the audit has a single combo box that populates and records 3 other fields based on the selection. The combo box contains the employee's name. A selection here then automatically updates and adds the employee's supervisor, manager, and employee ID. This was working exactly as intended when I built it an hour ago. Now, it saves the correct supervisor, manager, and employee ID but not the employee selected. Instead, it saves the employee immediately above in the combo box list.


So, if the options were "John Smith", "Mike Adams", "Luke Picard", and I select "Mike Adams" all of Mike Adams' additional information will be correct, but his name will be recorded as "John Smith". The specifics of my form is below.

Combo Box: "RMsCB", 4 columns with widths at 1",0",0",0". Control source is "RMs" and Row Source is from a "RefManagers Query" that returns the information given below. There is an "AfterUpdate" event with the following code:

Private Sub RMsCB_AfterUpdate()
txtEmpID = RMsCB.Column(1)
txtTeamLead = RMsCB.Column(2)
txtSectLead = RMsCB.Column(3)
End Sub

Any help is appreciated. Thank you.

[snipe]

Had a similar issue. If you remove the AfterUpdate event completely, does the record you clicked still not appear as selected?

If so, copy the combo box and paste it. Use the new one in place of the old. Don't try to select a record. If it works, rename it to the name of the old, and re-enable the event. Should work. I'm sure there is an actual reason for this issue, but this seems to work for me when needed.

If this doesn't work, I'm stumped as well.

[Alhassani]

I removed the AfterUpdate event as well as a "On Dirty" and "On Unload" event that was refreshing and saving the record. Didn't work. Then copied combo box and selected an employee with the same results. What did you mean by "Don't try to select a record"? Was that a typo? If not, how can I verify that it is working or not??

Had a similar issue. If you remove the AfterUpdate event completely, does the record you clicked still not appear as selected?

If so, copy the combo box and paste it. Use the new one in place of the old. Don't try to select a record. If it works, rename it to the name of the old, and re-enable the event. Should work. I'm sure there is an actual reason for this issue, but this seems to work for me when needed.

If this doesn't work, I'm stumped as well.

[snipe]

What did you mean by "Don't try to select a record"? Was that a typo? If not, how can I verify that it is working or not??

Oops, definitely a typo. Mind was elsewhere. I meant "Then try to select a record."

So you're saying that even on the copied combo box, with no events attached to it, selecting a record seems to select the next one on in the list?

Just for grins, If your answer is "yes" to my questions just confirm that this is a real issue. On the after update event, comment everything that you have out, and add:

 debug.print me.RMsCB.Column(0)

See if that is the same as the record that shows to be selected.

[Alhassani]

Yes, even with no events attached to it, it was still selecting the next record.

What is the debug.print command supposed to do? Is there supposed to be a box or something? I'm asking because nothing happens. It displays the person on the form that I selected, and it doesn't update anything on the database.

I can still select the record, but now there is a disconnect between the database and the input form. For example, the database shows 7 records while the input form shows 5. I deleted all the test records and they sync'd back up. But a quick entry of 4 test records throws it off again.

[snipe]

debug.print just outputs to the immediate window (ctrl-g). Since you commented everything else out, that is the only code that is firing. Just to see what access actually sees in that combo box.

[Alhassani]

Oh, thats neat. Yes, access is seeing what I am selecting correctly. I did notice that some of the backend doesn't seem to line up.

The control source is "RMs" while the name of the field it is actually populating is "Referral Management Specialist", not "RMs". The row source is "RefManagers Query". The list that comes up in RMsCB is the same list that comes up in "RefManagers Query". However, the list that is saving is what is pulled in the lookup function of the "Referral Management Specialist" field.

[Alhassani]

Fixed it! The lookup values in the table were conflicting with the query running to generate the names. In the table design section I redefined the row source properly. Once that was completed and the adjustments for bound columns and column widths were made to reflect the correct process, the database is now working as intended again. Thanks for the help snipe!