Access Form to merge select data from table 1 &2 to 3

I need to do the following with an Access Form:

1. Look up data with a combo box from "Table A"
2. Based on what was chosen from "Table A" User will  choose up to 5 different check boxes on form.
3. This record with choice from "Table A" and check boxes picked needs to be saved into "Table B" which will have 6 fields.

This seems simple but I am struggling for some reason.

Any help would be GREATLY Appreciated
BlackbeltrrfAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
So you'd then use my example #3, above (the INSERT statements).

Currentdb.Execute "INSERT INTO YourTable(Col1, Col2, Col3) VALUES('" & me.YourCombo & "'," & Me.CheckBox1 & "," & Me.Checkbox2 & ")"

Of course, you'd have to change YourTable, Col1, etc etc to match the fields in your tables.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What are you having trouble with?

1 is fairly simple - in fact, a combo is typically based on data looked up from a table. If you need to look up data based on the user's selection, you'd do that with a recordset, perhaps:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM SomeTable WHERE SomeField=" & Me.YourComboBox

The "rst" variable would contain all fields from "SomeTable" for the record(s) selected by the WHERE clause. You can refer to any field in the recordset like this:

rst("NameOfTheField")

So if you had a Field named Employee:

rst("Employee")

2 - Do you mean you want to show/hide checkboxes based on what was chosen? Your request there is not clear.

3. You can use a standard INSERT statement to do this:

Currentdb.Execute "INSERT INTO YourTable(Col1, Col2, Col3) VALUES(Val1, Val2, Val3)"

You can use variables for the Val calls, and you can also refer to the form where this code runs:

Currentdb.Execute "INSERT INTO YourTable(Col1, Col2, Col3) VALUES(" & Me.YourCombo1 & "," & rst("Field3") & "," & rst("Field5") & ")"

Note too that the INSERT statement must use the correct delimiters:

For Text fields, you would use a Single or Double quote:

VALUES('" & Me.YourCombo1 & "')"

For Date fields, use the Hash mark:

VALUES(#" & Me.YourCombo1 & "#')"
0
 
BlackbeltrrfAuthor Commented:
I am sorry, I was not clear on exactly what I am doing.

1. I have a form with a combo box that is pulling training categories, for example(DOT, Lift Truck training, Machine Guarding, etc....) from "TableA"  (This is done  and doing what I want it to.)
2. Also on this form I have 5 unbound checkboxes, (Supervisor, Manager, General, Electrician, Safety).  These are different employee titles.
3. Based on the training category picked from the combo box I need to check all the checkboxes that need to have this training.
4. After or during the selections are made for that record I need them to be added to a new table called "TableB" with these fields example:
Training..................Supervisor...... Manager..... General..... Electrician...... Safety
Mach Guarding..................0....................-1..................-1..................-1..................-1
DOT.....................................0.....................0....................0..................0...................-1

I hope this helps
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm okay on everything except #3.

How do you determine which checkboxes should be checked after I choose an item in the combo? In other words, if I select Lift Truck Training, how would I determine which items to check.
0
 
BlackbeltrrfAuthor Commented:
From an external list.  It is determined manually and changes from time to time so this form is a way for them to change it when needed.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Okay - so the user would select an item in the combo, and they would then select one or more Checkboxes?
0
 
BlackbeltrrfAuthor Commented:
That is correct.  This combined information is then a new table
0
 
BlackbeltrrfAuthor Commented:
TYVM. That works great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.