Going to a record from a combo box 2

[McLiguori]

I have used the MSAccess wizard in the tools dialogue box to create a combo box that takes me to the record I select in the combo. Everything works fine unless the record I am searching for is not unique. For example I have 5 John Smiths in my data base. The name field is identical, but they are different people. In the combo box, I chose the name field to display. The names appear correctly, but regardless of which of the five John Smith records I click on, it takes me to the first John Smith record. The only way to move to the other 4 John Smith records is by going to the next record on the form.

Can someone help me with this problem?

Thanks,

Ligs

 

[BlaineR]

Hi Ligs,

One of the basic rules of database design is that each row in a table should have a unique identifier. The purpose of that is to avoid ambiguity. In this case, Access has no way of knowing that you want to select the 3rd John Smith rather than the 5th John Smith. It just knows you want John Smith, so it presents you with the first one in the table.

I don't know if you have a unique identifier (such as a unique number that is automatically generated by Access) for each record in your table. If you do, then you'd need to bind your combobox to that unique identifier rather than to the name. You can still choose to display the name in the combobox.

If you don't have a unique identifier, then you really need to create one in your table. This can be done by adding a single field and having Access autogenerate a numerical unique identifier for each record. Alternatively, you could create what's called a concatenated key. A "key" is simply a unique identifier for the record; by "concatenated," we mean that you select more than one field as the "key" so that together the two or more fields uniquely identify any given row. For example, in your case you might have "Name" and "Phone Number" as your concatenated key. You may have more than one John Smith, but chances are you don't have more than one with the same phone number (well, unless it's father and son!...that tells you why it's a good idea simply to use a sequential autogenerated number to identify each row). But this all raises a question, how is your user supposed to know which John Smith to select from the combobox? If you go with the concatenated key idea, then you should include all the fields that make up your concatenated key in the drop down too so that the user doesn't have to pick John Smith by trial and error.

Good luck!

Blaine

 

[McLiguori]

Hi Blaine,

Thank you very much for you reply. Thanks especially for being so clear in your explanation.

I neglected to mention in my original post that I had tried the work-around of tying the box to my auto number primary key field, after which I displayed the Name.

This works fine as one can choose the precise record one is searching for. However, using this work-around prevents the user from typing in the name in the combo box and having it auto-complete or at least displaying 'nearby records'. The box only completes the field it is tied to. In this case the ID number which in my case is not useful as a search field. Its pretty much of a toss up as to which of these two approaches has 'fewer negatives' for my purposes.

I will try your suggestion to create a 'concatanate a key'.

Thanks again! Your replay was very helpful

Ligs

 

[banjogary]

Ligs,

If you make the width of your first column "0", basically hiding the autonumber field, you can then type in the name and have it go to that record. Sounds like you need to put a third column in as well that would allow the user to differentiate the different John Smith's

Regards,

Gary

 

[Parkroyal]

lig's

You don't mention why you would need to do this search but I think banjogary is right a 3rd field would be helpful.

A lot of companys will ID you by your zip or post code but a careful thought should help you to decide what that third field needs to be.

I am working on a similar prob and use last name and either telephone number or Post code.

good luck

 

[McLiguori]

Thanks everyone. Your suggestions are really helpful. Yep! The third column is a must. Thanks All!

Ligs

 

[Parkroyal]

Here's a thought. If you use a combo box to run a quiery then you can display all the results of that queiry in a list box you can then use the list box to oick the record you want and use that to poulate your form.

Sounds comlex but it is actually quite easy and the result not only looks profesional but is very slick and very quick.

I now have a combo box that will run a quiery based on any one of three pieces of information and display one line records of full name, post code and an account number. I've sized this list box to display about ten lines and scroll.

By clicking on the appropriate displayed records in the list box I then get the full verrsion of that record on screen and read to eddit.

You don't even need to be a whiz wiv the code 'cause a little bit of lateral thinking and VB will almost construt it for you.

Hope this may help

Good Luck

Pete