Solved

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

Posted on 2013-06-07
8
338 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

860 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