Populate text boxes based on a combo box selection, then save those to a table

[Bagels]

Quick background is that I've used Access for a long time, but never attempted my own database. Therefore despite my best attempts at research and learning, there's probably a good chance i'm doing something fundamental wrong.



I'm trying to make a fairly simple RMA/Return goods database.

I have a table called PartNumbers with 4 columns

1. OurCompanyPartNumber (this is the key, it is a unique number)
2. CustomerPartNumber (affiliated 1:1 with Our part number)
3. Customer (the customer name)
4. Description (short description of the part)

I have a table called RMA data that is storing all my fields

I have a form called RMA entry

I have a combo box in the form called OurCompanyPartNumber, where the user would select the part number from the drop down. I have all columns showing, so the user can see the other information for reference.

I understand the combo box can only ultimately display one column after selection. I'd like to use text boxes to display CustomerPartNumber, Customer, and Description based on the selection in the combo box. I'd also like to save those 3 fields to the RMA data table.

Originally I used the DLookUp function in the Control Source of the 3 fields I want to auto-populate. It did work, but with 2 issues:
1- it obviously isn't saving to the table since the DLookUp is the control source
2-when i close the database and re-open it, the DLookUp returns the #Name? error

I saw a suggestion elsewhere to make the DLookUp expresson the Default Value, then make the control source where in the table I want it saved, but that didn't work. Am I way off here ? Below is the DLookUp expression i have . Also if it matters all the "numbers" are short text fields since they often contain letters.

=DLookUp("Customer","PartNumbers","[OurCompanyPartNumber] = '" & [Forms]![RMA entry]![OurCompanyPartNumber] & "'")

[ranman256]

make sure the combo has 4 column too, (user doesnt have to see them all)
then when they pick 1 item, fill in boxes from combo.
NOTE: in vb, columns begin with Zero, so column 1 is :cboBox.column(0)

sub cboBox_afterUpdate()
txtCustomerPartNumber=cboBox.column(1)
txtCustomer =cboBox.column(2)
txtDescriptio =cboBox.column(3)
end sub

[June7]

Normally only need to save the key value. Saving other info is duplicating data between tables. Why do you want to do this duplication?

textbox ControlSource expression can reference combobox columns: =cboBox.Column(1)

Saving the values will require code (macro or VBA):

Me!fieldname = Me.cboBox.Column(1)

or

Me!fieldname = Me.tbxName

[Bagels]

Thanks to both of you!
I used the control source to reference the combo box columns, works perfect. Closed and re-opened it and it worked fine.

In regard to redundant data- you are correct and i THOUGHT I understood the concept, but i think i truly do now

I only want to show those 3 other fields on the form as a reference to the user, since Our Part# isn't very descriptive. Also if they switch to datasheet view it would be easier to see the history. So all of that is fine now.

I originally thought I needed to save the 3 other fields for reporting purposes - but I understand I can call up that information on the report if I need it, it doesn't need to be saved being redundant.


I should say in my research/learning i think i've learned a lot of individual things (DLookUp and other functions for example) but sometimes tying it altogether in the big picture is the issue. You have a very nice forum here I'll definitely be spending some time to learn more

[June7]

Generally, duplication is not needed. Occasionally this can be justified in situation where data can change over time, such as product price. Saving price will prevent changing historical record when a product price is modified. The alternative is to create new record for product in Products table and set the old record as 'inactive' and apply filter so it is not available for selection in combobox for new orders.