Solved

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

Posted on 2013-06-07
8
337 Views
Last Modified: 2013-06-10
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
0
Comment
Question by:Blackbeltrrf
  • 4
  • 4
8 Comments
 
LVL 84
ID: 39230718
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
 

Author Comment

by:Blackbeltrrf
ID: 39230916
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
 
LVL 84
ID: 39231439
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Blackbeltrrf
ID: 39231678
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
 
LVL 84
ID: 39233447
Okay - so the user would select an item in the combo, and they would then select one or more Checkboxes?
0
 

Author Comment

by:Blackbeltrrf
ID: 39234414
That is correct.  This combined information is then a new table
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39234831
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
 

Author Closing Comment

by:Blackbeltrrf
ID: 39235661
TYVM. That works great!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question