Syntax error with Insert into statement 2

[BrianLe]

In AC97, I get a syntax error with the following

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
 DoCmd.SetWarnings True

 
   Dim AddTrainNo As Variant, tbx1 As TextBox, tbx2 As TextBox, tbx3 As TextBox, SQL1 As String, DQ As String, itm As Variant
   
   
   Set tbx1 = Me!tbxTrainNoStart
   Set tbx2 = Me!tbxTrainNoFinish
   Set tbx3 = Me!tbxTrainYear
   
   
   DQ = """"
  
   
 For AddTrainNo = Me!tbxTrainNoStart To Me!tbxTrainNoFinish
      
 SQL1 = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
                 "VALUES( & DQ & tbx3.Value & DQ & "," _
                         " & DQ & tbx1.Value & DQ & ");"
                            
 
         Debug.Print SQL1
         DoCmd.RunSQL SQL1
       
 Set Me!tbxTrainNoStart = Me!tbxTrainNoStart + 1
 
 Next
    
   Set tbx1 = Nothing
   Set tbx2 = Nothing
   Set tbx3 = Nothing
   
   
   
Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
   MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click

 End Sub

I'm trying to insert a range of values into a table. I have frmAddNewTrainNo with tbxTrainYear, tbxTrainNoStart, and tbxTrainNoFinish. I would like to add records to tblTrain that has fields TrainID, TrainYear, and TrainNo. When the user enters a year and starting and ending train numbers, and hits the cmdAddRecord button, I'd like a record added for each TrainYear and TrainNo.

I'm not even sure if I'm using the best code.

Any suggestions?

Thanks,

Brian

 

[dhookom]

I wouldn't create the textbox object variables. I would simply dim Long or String or whatever type of memory variables and set them equal to the values of the controls.

Then it is important to understand the data types of TrainYear and TrainNo. You are treating them as if they are both text.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

For the syntax error:

 SQL1 = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
                 "VALUES(" & DQ & tbx3.Value & DQ & "," _
                           & DQ & tbx1.Value & DQ & ");"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Thanks Duane and PH. For now I'm going to try to use my original method since it is the one I'm most familiar with. In addition to PH's syntax fix, I also had to change:

Set Me!tbxTrainNoStart = Me!tbxTrainNoStart + 1

to

Me!tbxTrainNoStart = Me!tbxTrainNoStart + 1

Now I have the following that seems to work OK.

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
 DoCmd.SetWarnings False

   Dim AddTrainNo As Variant, tbx1 As TextBox, tbx2 As TextBox, tbx3 As TextBox, SQL1 As String, DQ As String, itm As Variant
   
   Set tbx1 = Me!tbxTrainNoStart
   Set tbx2 = Me!tbxTrainNoFinish
   Set tbx3 = Me!tbxTrainYear

   DQ = """"
   
 For AddTrainNo = Me!tbxTrainNoStart To Me!tbxTrainNoFinish
                      
 SQL1 = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
                 "VALUES(" & DQ & tbx3.Value & DQ & "," _
                           & DQ & tbx1.Value & DQ & ");"
 
         Debug.Print SQL1
         DoCmd.RunSQL SQL1
       
Me!tbxTrainNoStart = Me!tbxTrainNoStart + 1
 
 Next
    
   Set tbx1 = Nothing
   Set tbx2 = Nothing
   Set tbx3 = Nothing
   
Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
   MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click

 End Sub

Duane, if you want to give me more details on your suggestion for my future efforts, both TrainYear and TrainNo are long integer numbers in tblTrain.

Thanks again,

Brian

 

[dhookom]

I think this code should work. At least it will give you an idea of how I might create it. Since the fields are numeric, you shouldn't need DQs. BTW, you should set warnings back on.

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
   DoCmd.SetWarnings False

   Dim AddTrainNo As Long, SQL1 As String
   Dim lngTrainNoStart as Long
   Dim lngTrainNoEnd as Long
   Dim lngTrainYear as Long
   
   lngTrainNoStart = Me!tbxTrainNoStart
   lngTrainNoEnd = Me!tbxTrainNoFinish
   lngTrainYear = Me!tbxTrainYear
   
   For AddTrainNo = lngTrainNoStart To lngTrainNoFinish                      
 	   SQL1 = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
                 "VALUES(" & lngTrainYear & "," _
                           & AddTrainNo & ");"
 
         Debug.Print SQL1
         DoCmd.RunSQL SQL1 
   Next 
   DoCmd.SetWarnings True
   
Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
   MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click

 End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]