CODEWITHSUNDEEP
sqlvbams-access
Nashid Iftekhar
Nashid Iftekhar

Reputation: 65

Problem regarding For Loop using SQL in Access VBA

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.

Upvotes: 0

Views: 72

Answers (2)

Applecore
Applecore

Reputation: 4099

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

Upvotes: 2

Nashid Iftekhar
Nashid Iftekhar

Reputation: 65

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.

Upvotes: 0

Related Questions