Problem regarding For Loop using SQL in Access VBA

Question

I am working on a employee DB in which there is a training form. I have over 2000 employees who need trainings from time to time. I need to create batches of predetermined number (i.e. 50) of employees in that form. Also, I need to create them for each new training. So trainings are linked to TrainingID. I have created a for loop statement just as the following:

Dim StartC As Integer
Dim EndC As Integer

Set StartC = Nz(DMax("BatchNo", "T25TrnBatch", "T25TrnBatch.TrnID= " & Forms!F13NewTraining!TrnId), 0) + 1
Set EndC = Me.TBatchtxt

StrSQL = "INSERT INTO T25TrnBatch(TrnID,BatchNo) VALUES((Forms!F13NewTraining!TrnId),StartC);"

For BCounter = StartC To EndC
    DoCmd.SetWarnings False
    DoCmd.RunSQL StrSQL
    DoCmd.SetWarnings True
Next BCounter

(Note: TBatchtxt is the total batches need to be created)

But when I run it a pop up appears saying StartC.

Also I want the result to like the following:

BatchNo TrnID

1 101

2 101

3 101

Please HELP. Thanks.

halfer · Accepted Answer · 2023-12-16 15:05:42Z

2

The SQL statement that you are executing doesn't "know" what values you are trying to insert - you must concatenate the values into the SQL string. As a tip for debugging, you could place a Debug.Print StrSQL on the line after you have concatenated the values in to see what it actually is.

Also, the SQL statement is not inside the loop, so the values will never change. Conversely, there is no need to change SetWarnings inside the loop - you can just change it outside the loop.

Dim StartC As Integer
Dim EndC As Integer

Set StartC = Nz(DMax("BatchNo", "T25TrnBatch", "T25TrnBatch.TrnID= " & Forms!F13NewTraining!TrnId), 0) + 1
Set EndC = Me.TBatchtxt


DoCmd.SetWarnings False
For BCounter = StartC To EndC
    StrSQL = "INSERT INTO T25TrnBatch(TrnID,BatchNo) " _   
        & " VALUES(" & Forms!F13NewTraining!TrnId & "," & BCounter & ");"
    DoCmd.RunSQL StrSQL
Next BCounter
DoCmd.SetWarnings True

edited Dec 16, 2023 at 15:05

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Jul 31, 2021 at 9:47

Applecore

4,0792 gold badges11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nashid Iftekhar Over a year ago

I tried but it doesn't work. The Batch no doesn't increase.

Applecore Over a year ago

Ooops - the SQL statement used StartC as in your original code. I've modified it to use BCounter which is being incremented by the loop.

Nashid Iftekhar Over a year ago

That's even better. Thanks again.

Nashid Iftekhar · Accepted Answer · 2021-08-02 15:31:16Z

0

Ok, I have figured it out. I inserted the DMax function again in the SQL. It is as follows:

Dim BCounter As Integer '''I forgot to mention this line in my question
Dim StartC As Integer
Dim EndC As Integer

StartC = Nz(DMax("BatchNo", "T25TrnBatch", "T25TrnBatch.TrnID= " & Forms!F13NewTraining!TrnId), 0) + 1
EndC = Me.TBatchtxt


DoCmd.SetWarnings False
For BCounter = StartC To EndC
StrSQL = "INSERT INTO Table1Test(TID,BID) " _
    & " VALUES(" & Forms!TestForm1!TIDtxt & "," & Nz(DMax("BatchNo", "T25TrnBatch", "T25TrnBatch.TrnID= " & Forms!F13NewTraining!TrnId), 0) + 1 & ");"
DoCmd.RunSQL StrSQL
Next BCounter
DoCmd.SetWarnings True

Thanks @Applecore for pointing me in the right direction.

answered Aug 2, 2021 at 15:31

Nashid Iftekhar

657 bronze badges

Collectives™ on Stack Overflow

Problem regarding For Loop using SQL in Access VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related