Automatically populating a text box based from a combo box.

[lubbs65]

This concept is the entire basis of my database rather than its current implementation so please bare with my example.

I have a table called "names". In names there are two fields: first and last names. All last names are different.

I need a second table called "insert". "insert" will also contain the fields "first name" and "last name". Now, I want to create a form (using "form design" rather than just clicking the table im using then hitting form. this is very important for once i get this entire concept down).

In this form I need a combo box that selects the "last name" from the table "names". Upon clicking the last name, I am trying to then have a "first name" text box auto-fill with the persons first name. Then when I hit "submit" the first and last name of the person I just chose will appear in the table "insert".

How can I do this?
I've been looking around with relationships, using "look up field" as a data type in the table "insert" and setting equal to the last names in the table names. then upon creating my custom form->add existing fields->"insert" i thought I could then drag in "last name" which came in as a combo box automatically, then I dragged in "first names" which came in as a text box automatically. After selecting a last name, a first name would not auto-fill.

There were a lot of similar approaches but yea... any help is greatly appreciated.

 

[xenou]

On your form:

1) a ComboBox. It will have a row source type of table/query and a row source of names from the table names (SELECT LastName From Names Order By LastName)

2) A TextBox to hold the first name.

Now, in the ComboBox, you want to create an After Update Event. Go to the events tab in the combo box properties, and set your cursor in the after update event. Now click the expansion button (the three dots at the end) ... and select "Code" as the kind of expression you want to build. The after update event will be simple:

Me.Textbox1.Value = DLOOKUP("[FirstName]","[Names]", [LastName] = '" & Me.ComboBox1.Value & "'")


As far as inserting the record into tables Insert you can either bind the form to the table (simplest). Or you can script your inserts with a SQL statement or in code (when a command button is pressed).

I find that when learning it's best to make all fields required in the tables - less chance of incomplete inserts happening at random when you don't realize that records are being committed to the table.

ξ

Note: strongly suggest not to use a table named Insert as that is a SQL reserved word. Also, avoid table and field names with spaces - these are not allowed in many databases and makes your table data less portable/scalable if you ever move to another platform.

 

[lubbs65]

Me.Textbox1.Value = DLOOKUP("[FirstName]","[Names]", [LastName] = '" & Me.ComboBox1.Value & "'")

here is what I typed:
Me.first Name.Value = DLookup("[First Name]", "[names]", [Last Name] = "@Me.last name.Value&")

correct me where i went wrong...
also, i wasn't so confident in how you wrote the last bit:

'" & Me.ComboBox1.Value & "'")

the single and double quotes do not match up.

I changed the Me.Textbox1.Value to "Me.first name.Value" because it would highlight ".Textbox1" when saying there is an error (highlighted in blue). VBA changed the 'n' in name to 'N' (now upper cased) after making the change from Textbox. I still obviously am getting errors though. Any ideas?

 

[lubbs65]

Also, I have a made a combo box from the table names that is for "Last Names". It is set to equal "Last names" in the table initial and works correctly as far as using the last name goes. Then, if I proceed to add_existing_fields->names->first_name (which is a text box (notice how i chose from the table names rather than the table intial)) and put that on the form. Upon using the combo box, the text box that i just created becomes auto-populated correctly. Is there a way to just then have this value get set to the text box of for "first names" from the table "initial"???

 

[Montez659]

here is what I typed:
Me.first Name.Value = DLookup("[First Name]", "[names]", [Last Name] = "@Me.last name.Value&")

You have @ instead of &. Also, I think Xen missed a quote, so this would be the appropriate code:

Me.[first Name].Value = DLookup("[First Name]", "[names]", "[Last Name] = '" & Me.[last name].Value & "'")

Also, noticed that I changed Me.first Name.Value. If you are going to have a space (which I do not recommend) it would need to be:

 Me.[first Name]...

This is untested, just going off what I see.

 

[xenou]

Also, I have a made a combo box from the table names that is for "Last Names". It is set to equal "Last names" in the table initial and works correctly as far as using the last name goes. Then, if I proceed to add_existing_fields->names->first_name (which is a text box (notice how i chose from the table names rather than the table intial)) and put that on the form. Upon using the combo box, the text box that i just created becomes auto-populated correctly. Is there a way to just then have this value get set to the text box of for "first names" from the table "initial"???

I thought you wanted to populate the textbox with First Name based on the Last Name chosen in the combobox. This sounds like the other way around now. I'm confused.

As far as Me.[First Name].Value - that's looks fishy. A control name cannot have spaces at all in them (table and field names can - though I don't recommend that). I tried dropping a field onto a form that had a space in it and Access added an underscore, so you could try that: Me.First_Name.Value. Check the control properties - you should be able to see the actual name.

ξ

 

[xenou]

Actually, it's likely you don't even need to write code. Just add a textbox control to the form and then in it type:

=DLookup("[First Name]","[Names]",[Last Name] = '" & Me.Last_Name.Value & "'")

Assuming the control is called Last_Name and I've gotten the field and table names correct.

 

[Montez659]

As far as Me.[First Name].Value - that's looks fishy. A control name cannot have spaces at all in them (table and field names can - though I don't recommend that). I tried dropping a field onto a form that had a space in it and Access added an underscore, so you could try that: Me.First_Name.Value. Check the control properties - you should be able to see the actual name.

You are so right about that. I had that written originally and then second-guessed myself! VBA references to a field name on a form put that underscore in there, and that confuses stuff up.