Insert Into Problems 1

[leshok77]

I am using a form to read data from one table to write to a completely new table. The problem I am having is that on my string data type my code is able to read the value of the field (Field is named "BP_Batch) but it does not write to the table, insted I receive a commnad prompt that ask for the value of field "BP_Batch". All other fields write to the new table with no problem. Please see my code below, any suggestions or comments wold be appreciated.

Thank you,


Private Sub cmdRun_Enter()

Dim DBS As Database
Dim RST As Recordset
Dim Transmission_Date As Date
Dim BP_Batch As String
Dim Doc_Count As Single
Dim StartBook As Single
Dim EndBook As Single
Dim I As Single
Dim INTableName As String
Dim OutTableName As String

INTableName = "Inventory"
OutTableName = "Complete_Inventory"

Set DBS = CurrentDb
Set RST = DBS.OpenRecordset(INTableName)
If RST.BOF = False Then RST.MoveFirst
While RST.EOF = False

Transmission_Date = Left(RST.Fields("Transmission_Date").Value, 8)
BP_Batch = Left(RST.Fields("BP_Batch").Value, 10)
Doc_Count = Left(RST.Fields("Doc_Count").Value, 10)
StartBook = Left(RST.Fields("Start").Value, 6)
If IsNull(RST.Fields("End").Value) = False Then
EndBook = Left(RST.Fields("End").Value, 6)
DoCmd.SetWarnings False
For I = StartBook To EndBook
DoCmd.RunSQL ("Insert Into " & OutTableName & " (Transmission_Date, BP_Batch, Doc_Count, Start) Values (" & Transmission_Date & "," & BP_Batch & "," & Doc_Count & "," & I & ")")

Next I

Else

DoCmd.RunSQL ("Insert Into " & OutTableName & "(Transmission_Date, BP_Batch, Doc_Count, Start) VALUES (" & Transmission_Date & "," & BP_Batch & "," & Doc_Count & "," & Right(RST.Fields("Start").Value, 5) & ")")
End If

RST.MoveNext
I = 0
Wend
DoCmd.SetWarnings True


End Sub

 

[mharroff]

Since bp_Batch is data type string you will need to enclose it in quotes. To do this you need to change

DoCmd.RunSQL ("Insert Into " & OutTableName & " (Transmission_Date, BP_Batch, Doc_Count, Start) Values (" & Transmission_Date & "," & BP_Batch & "," & Doc_Count & "," & I & ")")

to

DoCmd.RunSQL ("Insert Into " & OutTableName & " (Transmission_Date, BP_Batch, Doc_Count, Start)Values (" & Transmission_Date & ",[red]'[/red]" & BP_Batch & "[red]'[/red]," & Doc_Count & "," & I & ")")

and

DoCmd.RunSQL ("Insert Into " & OutTableName & " (Transmission_Date, BP_Batch, Doc_Count, Start) VALUES (" & Transmission_Date & "," & BP_Batch & "," & Doc_Count & "," & Right(RST.Fields("Start").Value, 5) & ")")

to

DoCmd.RunSQL ("Insert Into " & OutTableName & " (Transmission_Date, BP_Batch, Doc_Count, Start) VALUES (" & Transmission_Date & ",[red]'[/red]" & BP_Batch & "[red]'[/red]," & Doc_Count & "," & Right(RST.Fields("Start").Value, 5) & ")")

Note: the changes are in red.

 

[leshok77]

Thanks for your help, Your suggestions worked perfectly.