Solved

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

Posted on 2013-06-07
8
336 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now