Add a combo box selection to another table

[hmassey]

Here is what I want to do:

Table 1 is a list of Sites (Site Name, Description, Address information). I have already successfully created a form that can be used to add additional sites to this table.
Table 2 is a list of Locations within the sites. I want to have a form, "Add a location", that will have a combo box that lists all the Site Names from table 1 and a text box to enter a room name. On this form is an add button that I want to add the selected site name and combo box to table 2.

The part where I get stuck is when I click the add button it does not add the data to a new record. It just keeps updating and changing the first record in table 2. Is what I want to do possible?

 

[jdraw]

The subject you are dealing with is called Cascading Comboboxes.
Here's another article

 

[hmassey]

jdraw - the information you provided is very fascinating and I will definitely note if for future reference, but I don't think it quite helps me with my issue.

My form has a single combo box, a single text box and an add button. I want to select a site from the combo box and then type a room or location in the text box. When I click the add button, I want the information displayed in the 2 boxes to be added to a second table as a new record. Right now when I click the add button it does not add the information as a new record and build the table. It only changes the first record in the table.

Example: I select Site A from the combo box and enter Room 1 in the text box. When I click Add, this information is added to the second table. Now I go back to the form and select Site B from the combo box and enter Room 2 in the text box. This time when I click Add the first record is changed to the new information instead of adding a second record.

The information you provided unfortunately does not help me with the add button.

 

[jdraw]

OK. I jumped ahead--sorry.

Your combo is populated with your Sites, correct?
Instead of using the name of the site, you should use a number (autonumber) as PK of the site table,
and your combo would have the number and name of the site. The user would select by name.

In the text box, the user types Location. (How do you prevent typos?)

Using the combo selection and the textbox value - when you click the button you invoke vba code for the onclick event.

You want to INSERT a record in the tblLocation
that includes the SiteID (combo value) and the location value from the textbox.

Show us your table designs so we can be specific with the associated code.

 

[hmassey]

Yes, the combo box is a list of Site Names.
I hide the autonumber for the combo box. What do you mean by "PK"?
Typos is not a big issue since only a couple people, including myself, will be using this database. But that is an issue for another day.

This excel files show how I have the two tables designed.
View attachment Sites.xls

 

[hmassey]

I think I just solved my problem.
I was looking into how to have the form load with the combo box and text box blank instead of showing the first record.

I found the following code that I entered into the On Load Event for the Form.

Private Sub Form_Load()
On Error Resume Next
RunCommand acCmdRecordsGoToNew
End Sub

I opened the form after adding this code and tested to see how it affects the data and now when I click Add, a new record is created instead of changing the first one.

jdraw - I thank you for trying to help. The information you provided sparked some other ideas I may try to implement later.