How do I run an append query in Access VBA to create a certain number of identical records based on a quantity in a field on the form?

Question

first thanks for any help you can provide.

Below is the append query I'm working with - straight forward. On the same form I'm pulling the values referenced from, there is a field that holds a quantity. I want this quantity to be referenced to perform this append query that number of times. If the quantity in the field is 6, then I want the append query to run 6 times. I know this would be a loop, but I'm rusty and I cannot find any clear examples of this on stack or elsewhere on the web.

Thanks again,

Shawn

Code thus far:

Private Sub Command20_Click()

Dim strSQL As String

'*****Append record to table*****
strSQL = "INSERT INTO tblStructuresNoDAworking (StructName, State, Brand, Flavor_Sub, _
            Type, Size, Bottles_Case, Entry_Date) VALUES ('" & Me.Text90 & "','" & Me.Text26 _
            & "','" & Me.Text28 & "','" & Me.Text30 & "','" & Me.Text91 & "','" & Me.Text92_
            & "','" & Me.Text93 & "', '" & Now() & "')"

DoCmd.RunSQL (strSQL)

End Sub

score 0 · Accepted Answer · edited Jan 08 '20 at 17:07

0

Figured it out. Shortened the number of fields for ease of testing.

Private Sub Command20_Click()

DoCmd.SetWarnings False

Dim strSQL As String

'Define Loop Count
Dim I As Integer
For I = 1 To Me.ComboDeal.Value

'**Append record to table**
strSQL = "INSERT INTO tblStructuresNoDAworking (Structure_Name) _
          VALUES ('" & Me.Combo121 & "')"

DoCmd.RunSQL (strSQL)

Next I

DoCmd.SetWarnings True

End Sub

edited Jan 08 '20 at 17:07

Community

1
1

answered Jan 02 '20 at 04:00

ackdaddy

131
3
5
16

Just for fun, set the value of Combobox to a string that contains a quote`'` . That ts called SQLInjection and can lcause major trouble!. If you need to create a dynamic query, use Parameters, ! Thwy also avoid format issues (e.g. with dates)to use Parameters you have to use DAO or Adodb to connect. Avoid DoCmd.f.. Be aware that you can ionsert many rows with one staement at once, but usualay a Recordset is faster dor inserts. But why do you need to insert the same vaue multiple timers? That smells like bad normalization. – ComputerVersteher Jan 02 '20 at 06:48
@ComputerVersteher thanks for the information, and I will keep that in mind. I haven't worked on a project in quite a while and was asked to see what I could come up with. The same values are being inserted multiple times for these fields, however there are other fields that will be changed manually by the user via a pop-up form.These fields will always contain the same values, whereas the others may change. – ackdaddy Jan 02 '20 at 18:57

score 0 · Answer 2 · answered Jan 02 '20 at 07:44

0

First, you should name your controls to have meaningful names.

Second, Now should not be casted to text and then to date as you do. Insert it directly:

& "','" & Me.Text93 & "', Now())"

Then, using a loop in DAO is much faster and cleaner. See the example here:

Insert multiple records with a date range in MS Access

which you easily could adopt to use your Me.ComboDeal.Value for the loop.

answered Jan 02 '20 at 07:44

Gustav

53,498
7
29
55

Thanks for the, protip on the control names, the link and the correction on the Now(). – ackdaddy Jan 02 '20 at 18:59

How do I run an append query in Access VBA to create a certain number of identical records based on a quantity in a field on the form?

2 Answers2