Solved

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

Posted on 2013-06-07
8
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 85
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 85
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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 85
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 85

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

688 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