Add Items from Form to table

[swalsh84]

I attached my database to the thread. It should be self explanatory, but basically I have a drink name and a combobox of ingredients. I want to be able to navigate through the records and add ingredients to specific drinks. Right now I'm stuck on how to add the items from the form. There are both "Add" and "Delete" ingredient buttons that are available. I'm stuck on the syntax. Thanks in advance.

[TheShabz]

try this out.

[Access_Blaster]

This seems like a classic 1:M one drink name many ingredients? The original model doesn't seem very flexible. See attachment. Also you could add combo boxes to the fields in the subform.

[swalsh84]

The reason I set up the original with three tables is so that later down the road, I can look in the pantry or fridge and ask the database what I can make with the available ingredients. In your model, it doesn't look like you can query the database in this way. I'd rather have an indexed table in the middle of the drinks and ingredients so that I can query that way.

Make sense? I was hoping to get some VBA in my add and delete buttons to add a record in the middle table that included the drinkID and ingredientID.

[maximus]

here i see you have three tables Drink, Ingredients and List. TheShabaz exmaple allows you to select a drink and add ingredients to it the data is inserted in the table list. now you are saying the you want to query your records. I have some questions
1) Have to tried out The Shabaz's example.
2) Does it do what you want.
3) If No what do you want
4) if yes what more do you want.

how would you like to query your records.

[swalsh84]

1) Yes I did try the example.
2) It does not do what I want.
3) As stated in my previous reply, I would like to be able to query the records. TheShabaz example is simple and would work, but I want to be able to query records based on selection of a few ingredients. So later down the road I could have a form with some combo boxes. I could select 2-3 ingredients that I currently have at the house and determine what I could make with said ingredients. By keeping the ingredients table separate from the drinks table it makes this possible. When they are combined as in TheShabaz example you don't have this flexibility.

As well, I think I mentioned VBA script for the Add and Delete buttons. This was the original request. Basically I want the "Add" button to add a record in theList for each ingredient addedd to a drink. In psuedocode form:

insert into theList where drinkID = drinkIDFromForm and ingredientID = ingredientIDFromForm

I don't know how to reference the combo box on my current form so that I can write a sql statement to do the above.

[maximus]

Regarding referencing combos:

here is an example:

Dim strSQL as String

strSQL="Insert Into Table1 (Employee_ID,Employee_Name) Values(" & Me.Combo1 & ",'" & Me.Text1 & "');"

Here Combo1 Gives me the Employee_ID and Text1 Gives me the Name. Strings has to be i ''.