I have a combobox for the "customers" last name, when selected it autofills the rest of the information about the customer into other fields on the same form. My issue is the database was set up on a fly and the primary key is "order number" so the customers name is listed multiple times. When the user selects the combobox there are hundreds of smiths. Do I have to make a new form with a subform so it's done right or can I just add a new field to somehow limit the customer information to one record? The thought of having to redo the relationships throughout the entire database is killing me.

I used
Me.First = DLookUp("[First]", "Request_Information", "[Last] = '" & Me.Last & "'")

Thanks,
Steffani

Steffani,


I don't follow that.

The correct way is to have a separate customer table. The user would select the customer from the customer table with the combo box. This would make what you need to do very easy.

While your current method may work, I am worried about how reliable it will be. I would avoid the Dlookup() for what you need.




It should not be that difficult to fix the database's table structure to be correct.

I have fixed many databases that have poor table design. I have also tries to work with database that have a poor table design.


If you keep a poor table design then simple things become difficult and time consuming. The database also becomes very fragile and easy to mess up the data. In the long run you spend a lot ore time maintaining a database with a a poor table design.


It may seam like a lot of work to correct the tables and update all the objects. The advantage is that once it is done then everything gets a lot easier. Sure it requires a lot of work up front. The benefits are definitely worth it. It also allows you to make your database a lot more user friendly. This generally helps the users be a lot more productive.


This may help: Database Normalization Resources

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015