combobox value changes when record selected

Question

Access2007.accdb

In the main form, with 2 sub forms, there are several comboboxes set up via the wizard to select the main form's record.  The user might want to look up via address or look up via name - which is why there is more than one.  These all work fine.

One of the comboboxes, look up by Name, is going to select a main form record; the record in this case is a property.  That property can have more than one name related to it.  So one can select that record by either selecting Jones or Smith or Johnson.  And this works ok too.

But if you use Smith (or Jones) to select the record; upon record select the combobox also changes value to Johnson (lowest alpha).  It is a relatively minor thing - but the user typically is going to look up several Smiths in a row and so is wondering why the combobox can't stay at Smith. 

The initial wizard set up of the combobox was a macro event - that isn't a big deal and can remake to VBA event; but I'm not sure of an approach on this....

Answer 1

Hi. I think this is why you're seeing the combobox change value. Let's say the combo has the following columns:

ID Name
1  Johnson
1  Smith
1  Jones

So, if you select Johnson, the combo uses the ID value of 1 to find the property record. Also, if you select Smith, the combo uses the ID value of 1 to find the same property record. However, since the combo value is 1, it displays the "first" matching value in the list.

Hope that makes sense...

Answer 2

agreed.  now can somehow the combobox either stay at the initial selection or return to it.... 

probably will need to change this to a vba combobox as I'm not that well versed in macro actions

Answer 3

You could try setting the combo bound column to a unique value column but still use the ID field for your filtering or searching feature. Hope that helps...

Answer 4

good point - that should work; will take a look....

Answer 5

good point - that should work; will take a look....

Good luck!