Selecting a customer on a form

[Goody974]

Hoping someone can help with this... I am such a NooB and have tried searching and experimenting, but clearly I am doing something wrong.

At the moment I have a form that gets filled in by the staff with customer name and address details. Each of the address and name boxes are individual fields (ie companyname, address1, town etc)

This form then prints out to a report with all those details on.

I would like to have a customer database and on the form just a drop down box. The operator selects the customer from the dropdown box and this populates the fields on the form which then prints. Effectively just saving the customer details having to be input over and over again.

Ive got as far as having the dropdown box which then puts into the main table the customer ID when its changed, but I cant seem to update the form or the report with the address details? I have created a relationship between the 2 queries of customerID in the main table and ID in the customer database.

What am I missing?

 

[arnelgp]

add the address,company, etc.. in your combo's rowsource.
example of combo's rowsource:

select customerID, customerName, companyname, address1, town

next add code to the combo's after update:

Private sub combo_AfterUpdate()
Me.txtcompanyname=me.combo.column(2)
me.txtAddress1=me.combo.column(3)
end sub

 

[Goody974]

add the address,company, etc.. in your combo's rowsource.
example of combo's rowsource:

select customerID, customerName, companyname, address1, town

next add code to the combo's after update:

Private sub combo_AfterUpdate()
Me.txtcompanyname=me.combo.column(2)
me.txtAddress1=me.combo.column(3)
end sub

Thats great thank you!

 

[Pat Hartman]

In a properly designed schema, data is NOT stored in multiple places. On your form, you would have a combo box that selects the customer by name but saves the customer's unique ID. Then whenever you want to see the customer data, you join the table back to the customer table and pick up the fields. I've included a sample database that gives three examples of how to show related data on a form and labels them as Bad, Better, Best.

The ONLY reason for saving data in more than one table is because the data might change over time. For example. On an OrderEntry details table, you would commonly store the price paid at the time of the sale since price changes over time and finding the price of an item at a point in time can be complicated so just saving it generally works better. It also allows the price to be overridden on the fly. For example, you might be replacing a defective item so the price of the item will be 0 rather than what the item table says.

Arne gave you the best way to copy these data fields and save them but that doesn't alter the fact that duplicating data is wrong and will lead to data anomalies.

 

[Goody974]

Thanks Pat for the info.

In fact I think the first answer gives me exactly what I'm looking for as it's for creating a calibration certificate which of course shouldn't change.

The details stored with the cert will always be the same, but say if the customer moves address then of course we update the customer record byt the cert will always have the same details on it.

Hope that makes sense?

 

[Pat Hartman]

If you need to know the customer address when the cert was issued, then you would duplicate the address data and store it with the cert. If you always want the customer's current address, you would NOT duplicate it. The address stays only with the customer record and you simply reference the customer record to retrieve the current address.

In a slightly more complicated situation where the customer may have several addresses and the address associated with the cert is relevant, you would use the customerAddressID as the foreign key in the cert record and connect to the customer via his address record.

Glad to help. Storing data multiple places when that isn't necessary always comes back to bite you one way or another.