Population text field from related data entered in another text field.

[Accessnoob11]

Hello all. I have literally been searching this forum and the internet all day to try and resolve my problem and have not found an answer that worked. So, I am sorry if this is a repeated question but I am looking for an answer that will work for my exact issue. To start off, I have never used access before today and I am trying to solve a problem that I believe to be somewhat complex. And here it is. For the company I work for, we sell products and use access for our database mainly for our warranty calls. So when a warranty call is taken, we open up a "form" within access, and use that form with drop down menus and other text fields etc. etc. for that certain call and customer. Obviously, they are all filled in as best as possible from the information that the customer gives us, and it is then stored. For our products, there is a name and a part number for everything that we sell. These both are part of the form that is used on a warranty call. I am wanting to make the form in access so we can write in the PRODUCT NUMBER in its designated text box, and when that is entered, I want the text box for the PART NAME to be populated automatically, depending on the part number entered. The reason for this is less time to enter, less questions we need to ask, and consistency with the data we need for different failure analysis for parts. (Many times the part name is misspelled and when doing a search, it will not be found). So, I know this is possible, but I do not have a clue where to start. If a formula is needed, please use "part name" and "part number" for the variables. Thank you so much in advance and I do apologize if this is a repeated question. Any help will be awesome.

[June7]

Yes, a common topic.

Options:

1. Form RecordSource is a query that joins tables so the related info from products table is available. Bind textboxes to fields of products table and set properties Locked Yes and TabStop No.

2. The product number control is a multi-column combobox with product number and product description. Textbox ControlSource expression refers to the column with the description.

Is it PRODUCT NUMBER or PART NUMBER that is entered?

[Accessnoob11]

Is it PRODUCT NUMBER or PART NUMBER that is entered?

It is the part number that is entered. Thanks for the reply.

[June7]

Okay, then change 'product' to 'part' in my suggestion. Have any luck with it?

[Accessnoob11]

1. Form RecordSource is a query that joins tables so the related info from products table is available. Bind textboxes to fields of products table and set properties Locked Yes and TabStop No.

I am not quite sure where to start when you say bind text boxes. What settings would I go to to perform this step? Thanks. Also where is the Tab Stop option?

[Accessnoob11]

Ok I have figured out how to do all the steps you have told me this far, but I know need to know how to make the relationship between the two textboxes. Right now I have it so they are related to the specific coloumns that have the correct information in them, but it will not let me enter the part number. It says "#NAME?" in the box instead.

[June7]

Are you using option 1?

The box (combobox?) for PartNumber is a data entry control and should be bound to the PartNumber field of the parent table, not Parts table. Bind textboxes to the other related fields of Parts table.

That error means Access doesn't recognize a name reference, possibly a misspelling. If you are using option 1 I expect there are two PartNumber fields in the RecordSource. Look at the field list in the ControlSource dropdown and pick the one from primary table.

[Accessnoob11]

How exactly do I bind the PartNumber text box to the PartNumber field in the table they are in?

[June7]

The table is part of form RecordSource? Set ControlSource property of textbox.

[Accessnoob11]

In the ControlSource I set the path with the (...) button to the field where the part numbers are in the part table and I did the same for the part name text field. Still nothing. The "#Name?" error keeps coming up in the text field.

[June7]

From my earlier post "The box (combobox?) for PartNumber is a data entry control and should be bound to the PartNumber field of the parent table, not Parts table. Bind textboxes to the other related fields of Parts table."

Why use expression builder to bind ControlSource? Also from earlier post "Look at the field list in the ControlSource dropdown and pick the one from primary table."

[Accessnoob11]

Ok I have everything linked up now. The Part # field in the table is linked to the Part # text box and I have updated both the RecordSource and the ControlSource. The only trouble is that when I now enter the part #, it updates the table, not the text box in the form. How do I get the text box to populate in the form? thanks

[June7]

The control must be bound to the field in the primary table, not the the Part # field from Parts table. Want to provide your project for analysis? Follow instructions at bottom of my post.