By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Insert many values into separate table (VBA) from Multiple Items Form

P: 2

I have a multiple value form: sbfrmTests_multi
*Please ignore sbfrm - at present that is the name, but it has no parent.

- Form use: select tests and insert them into a template with the same templateID.

I have a static table: tblTests
-A table of tests, to populate the selection options (Checkboxes) on sbfrmTests_multi

I have a data entry table: TestRecord
-A table to hold the templateID and the tests selected from sbfrmTests_multi

When you select the available options, the form returns with the tests selected:

The form's data source is a Select Totals query (to prevent duplicate records):
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Testing.testid, Testing.TestCategory, Testing.Test, Max(Testing.TestMethod) AS MaxOfTestMethod, Max(Testing.NPN) AS MaxOfNPN, Max(Testing.Cosmetic) AS MaxOfCosmetic, Max(TestRecord.TemplateID) AS MaxOfTemplateID, Max(TestRecord.TestRecordID) AS MaxOfTestRecordID
  2. FROM Testing, CoATemplate, TestRecord
  3. GROUP BY Testing.testid, Testing.TestCategory, Testing.Test
  4. HAVING (((Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_hm] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_hma] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_mb] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_mb2] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_pe] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_rs] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_pc] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_me] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_bo] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_ad1] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_ad2] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_wa] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_cu1] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_cu2]))
  5. ORDER BY Testing.TestCategory, Testing.Test;

I added two unbound textboxes to the form: (TemplateID_1) that will eventually auto-populate from a parent form (header), (TemplateID_2) in the data field entry of the multiple items form.

TemplateID_2 updates to = TemplateID_1 (working as desired).

What I need is a command button to insert all visible records from the multi-select form into another table.

So if I select test a, b and c, the multiple items form shows test a,b, and c - and templateID updates to be the same for all the results that display.

I need help inserted all displayed records on the form into table: TestRecord.

I can only get the first row to insert with a command button right now.

I'm sorry for the convoluted questions and truly appreciate any help! :) <3
Attached Files
File Type: docx Tests Form Example.docx (52.1 KB, 8 views)
1 Week Ago #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,419
Let's use your existing code to design a solution as that's likely to be the type of code you're most comfortable with. You'll need to post it of course.

NB. When duplicating data across from one table to another it's important not to de-normalise your database, so we typically copy the index values across rather than the raw data. Does that make sense to you?
1 Week Ago #2

Expert Mod 15k+
P: 31,419
PS. Nothing in your attached document is ever likely to be read by anyone here. If you want to post a picture then the facility is available for that. Otherwise the whole of the single question must be included in the forum as text. Any other questions will need to be posted in separate threads. If posted in existing threads then they will be removed.

FYI: I've removed the attachment from your original post. If there's anything in there you feel is necessary for the question then please post as instructed above.
1 Week Ago #3

P: 2
First - I'm extremely grateful for you reviewing my post.

Secondly - I'm a poorly taught amateur so my interpretation of your question is that I should not associate a relationship between the data, but rather input the values via VBA considering my needs? (I'm sorry if incorrect!).

I have attached my DB and the code below needs some tweaking to work correctly...

Expand|Select|Wrap|Line Numbers
  1. Private Sub TestInsert()
  2. Dim numMonths As Integer
  3. Dim intCounter As Integer
  4. Dim strSQL As String
  6. numTests = [txtTemplateID_Template]
  8. For intCounter = 0 To numTests
  9.   strSQL = "INSERT INTO testrecord ([TemplateID]) VALUES (" & _
  10.             Me![txtTemplateID_Template] & ")"
  11.     CurrentDb.Execute strSQL, dbFailOnError
  12. Next
  14. MsgBox (numTests + 1) & " Records have been added to the Table [TestRecords]", _
  15.        vbInformation, "Append Data"
  17. End Sub
Attached Files
File Type: zip CoA (1.24 MB, 2 views)
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.