problem with Editing a record with a combobox

[Icehousman2]

I have a form for the user to edit sales rep information. Things like first last name, phone number, and email. Well, I set it up so that the user selects a rep by name in a combobox (I used the combobox wizard to create it) which fills out the rest of the form with the rep's information, which can then be edited. The problem is that if there are two reps with the same name the system blows up. Well not really, but you know what I mean. Anyway, having two reps with the same name is very rare, actually probably won't happen, but I still think this is something I should plan for. Any Ideas on how to get around this problem. Thanks for all the help in advance, I don't know what I would do with out this forum, it is great.

 

[smercer]

This really is about the way your table is layed out and should be in the table forum.

First of all you should not have the primary key on the surname or even the first name of the sales rep because of this reason (how many names do you think you will come across with the name John Smith?).

what you need to do is create a sales rep id that is unquie for all records. the fast and dirty way to do this is to create a field with a autonumber and that is it.

I have a tutoral on how to do this but is too large to put in the forum. its 107.0kb but the forum limit is 100.0kb. can email instead.

 

[Icehousman2]

I already have an autonumber field used as the Primary Key in the table. But what is going on is, I want to fill out the form for editing using a combobox. The combobox uses a field that I created in a query that displays the firstname and lastname fields as one. The problem is that the combobox only uses this new field to relate it to the rest of the info in the query. But if there are two reps with the same name, then the combobox get confused. I tried to attach the repID field into the combobox row source, but it then stopped filling out the rest of the form with the correct info for editing. Does this make sense? Thanks for the response.

 

[smercer]

The problem is that if there are two reps with the same name the system blows up.

when there is two names the same use the middle name in combination with their given name or at least have initals. example:
Given field
John J

or

John jose

Surname field
Smith

Does this help?

 

[Rich]

The repID should be the BoundColumn since it should also be the ForeignKey in your other table

 

[Icehousman2]

Thanks again for the responses. But I have already tried to make the RepId the bound column for the combobox, but it then stopped updating the rest of the form. I created the combobox with the wizard, and don't exactly know what causes it to update the rest of the form. When I go into the row source for the combobox, the only field in the query is RepName, and when I tried to insert the RepID into the query, the combobox stopped working. How do I put the RepID into the query, and still make the combobox update the rest of the form with data ready for editing. Thanks again.

 

[smercer]

you have to put in how many columns you want in the combo box (in this case two) but the bound column should be the key field. hint: you have to have the fields the combo box looks up to the first and second in the list of fields in you table or query otherwise it wont work.