Combo Box to find a number not going to matching entry

[sdbsadmin]

I have two combo boxes that are looking up the Client ID and Client Name in a local table. One sorts by name and the other by ID. I'm encountering a very strange problem in that the one that sorts by name finds the first matching entry as I type (I put it a "w" and it goes to the W's) like it's supposed to, but the one that goes by ID (it's a number field), does NOT go to the first matching entry and gives me a "not-in-list" error when it really is further down in the list.

Any insights? I already tried recreating the combo box, but I had the same problem.



Thanks.

[rpeare]

are you sorting your ID combo box by the ID?

Are you sure you're not lopping off any part of the ID when you are typing in your value? Combo boxes by default are limited to the list of values you supply it (whether that be a table or query) and you will only get that 'not in list' error if you have typed in a value that does not exist in your data source.

Are both of your combo boxes unbound? (They should be) if they're bound that's likely to be a problem as well, particularly if you are choosing two different clients from each of your lists.

[sdbsadmin]

The combo boxes are sorted by the autonumber ID (which is hidden), and then there's an internal ID, which is a number field, that we're trying to pull up. I'm sorting on that internal ID. Both combo boxes are bound to a client ID field, but that's good, since I only want one client. The two combo boxes are just two different methods of showing it.

[rpeare]

yeah, don't have two fields that are bound to the same value. That's really not a great idea. Not only that, but if you are using BOUND combo boxes you are not actually looking up anything, you're changing the value on the current record. personally I'd have an unbound list box that showed both their internal ID and their name and have a single search box and in the ON EXIT property of the search field have the list box limited to items that match your search criteria, then in the ON CLICK event of the list box bring up the record you're interested in.

If you're actually trying to do data entry with these combo boxes only ONE of them should be bound to your data source. In the UNBOUND one if you had something like

[NameComboBox] = [IDComboBox]

Then you can visually still have two combo boxes you're just forcing the bound combo box to be equal to the unbound combo box in the ON EXIT or AFTER UPDATE property.

[sdbsadmin]

So let's say that the combo box is unbound (I redid it and didn't bind it for testing). Is it not going to be possible to use that combo box as a lookup?

[rpeare]

No, you never, ever want to use a bound field as a lookup, any time you change a bound field it's actually changing your data.

If you want to use a combo box or a list box as a lookup it's got to be unbound then use a property (on exit, after update, etc) to requery your form based on your selection.

[sdbsadmin]

Ok. It's unbound now. I can't get the combo box list to work properly still I'll type in a 1 and it won't go to the 1s.

Thanks for the hwlp.

[rpeare]

click the down arrow of the combo box that's using the client ID, is it actually showing client ID's or is it showing client names? if it's still showing client names you've got to change the query driving that combo box so it shows the client ID rather than the client name.

In other words you'll have two combo boxes

In combo box one (the one where you want to show client ID you'd have the PK of the client table in the first column and the internal clientID in the second column, you'd set the widths of columns on your combo box to 0 and 1" (for example) so that only the client ID showed. Your BOUND COLUMN would be the PK of the client table.

Similarly for the second combo box instead of the second column holding the clientID you'd have the client name instead. but the bound column would still be the PK of the client table.

[sdbsadmin]

Got it. Thanks!