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

[ashleybeck]

Hello!

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):

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
FROM Testing, CoATemplate, TestRecord
GROUP BY Testing.testid, Testing.TestCategory, Testing.Test
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]))
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

[NeoPa]

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?

[NeoPa]

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.

[ashleybeck]

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...

Private Sub TestInsert()
Dim numMonths As Integer
Dim intCounter As Integer
Dim strSQL As String
 
numTests = [txtTemplateID_Template]
 
For intCounter = 0 To numTests
  strSQL = "INSERT INTO testrecord ([TemplateID]) VALUES (" & _
            Me![txtTemplateID_Template] & ")"
    CurrentDb.Execute strSQL, dbFailOnError
Next
 
MsgBox (numTests + 1) & " Records have been added to the Table [TestRecords]", _
       vbInformation, "Append Data"
 
End Sub