Problem with SQL transfer of data from form to a second table

[FALSE_EMAIL]

Hi
I've got a form which I'm using to track defects numbers & would like to
track the defects via the sieral number as well in a seperate table, so I
have tried this code but can't get it to work. the fields types are

Vin_Num = Text
Comp_id = number
Fault_id = number
Insp_point_id = Number
Date_time = Date
Shift = Number

kev

-----------------------------------------------------------

Dim vinnumber As String
Dim Date_time As Date
Date_time = Now()
vinnumber = InputBox("Enter Vin Number")
Dim strSQL As String

Set db = CurrentDb()


strSQL = "INSERT INTO Vin_detail_tbl" & _
" (Vin_Num,Comp_id, Fault_id,Insp_point_id,Date_time,Shift)" & _
" VALUES (" & vinnumber & "," & _
Me.Component & "," & _
Me.Fault & "," & _
Me.Insp_point & ",#" & _
Date_time & "#," & _
Me.Shift & ");"

db.Execute strSQL, dbFailOnError

 

[Brian]

Hi
I've got a form which I'm using to track defects numbers & would like to
track the defects via the sieral number as well in a seperate table, so I
have tried this code but can't get it to work. the fields types are

Vin_Num = Text
Comp_id = number
Fault_id = number
Insp_point_id = Number
Date_time = Date
Shift = Number

kev

-----------------------------------------------------------

Dim vinnumber As String
Dim Date_time As Date
Date_time = Now()
vinnumber = InputBox("Enter Vin Number")
Dim strSQL As String

Set db = CurrentDb()


strSQL = "INSERT INTO Vin_detail_tbl" & _
" (Vin_Num,Comp_id, Fault_id,Insp_point_id,Date_time,Shift)" & _
" VALUES (" & vinnumber & "," & _
Me.Component & "," & _
Me.Fault & "," & _
Me.Insp_point & ",#" & _
Date_time & "#," & _
Me.Shift & ");"

db.Execute strSQL, dbFailOnError

Because vinnumber is a string, it needs to be enclosed in inverted commas in
the resulting SQL string. In order to indicate this in your VBA code, you
can use doubled inverted commas, thus:

" VALUES (""" & vinnumber & """," & _

You may need to take a little time to think about this - people often find
it tricky to get their heads around! If any of the other fields (e.g.
Component) are also text, then you will need to do the same thing with them.

 

[Fake]

Thanks for the reply,
I've tried that and it does transfer into the database, but for the
vinnumber it enters ' & vinnumber & ' and not the string I've declared
via the inputbox.

Kev

 

[Brian]

Thanks for the reply,
I've tried that and it does transfer into the database, but for the
vinnumber it enters ' & vinnumber & ' and not the string I've declared
via the inputbox.

Kev

You've done it wrong then. If you post your attempt then I'm sure someone
will correct it.

 

[Fake]

the code I've used this time is

Dim vinnumber As String
Dim Date_time As Date
Date_time = Now()
vinnumber = InputBox("Enter Vin Number")
Dim strSQL As String

Set db = CurrentDb()


strSQL = "INSERT INTO Vin_detail_tbl" & _
" (Vin_Num,Comp_id, Fault_id,Insp_point_id,Date_time,Shift)" & _
" VALUES (""& vinnumber & ""," & _
Me.Component & "," & _
Me.Fault & "," & _
Me.Insp_point & ",#" & _
Date_time & "#," & _
Me.Shift & ");"

db.Execute strSQL, dbFailOnError

Kev

 

[Marshall Barton]

the code I've used this time is

strSQL = "INSERT INTO Vin_detail_tbl" & _
" (Vin_Num,Comp_id, Fault_id,Insp_point_id,Date_time,Shift)" & _
" VALUES (""& vinnumber & ""," & _

You didn't use the same number of quotes that Brian said to
use.

 

[Brian]

You didn't use the same number of quotes that Brian said to
use.

Marsh is right, you did this:

" VALUES (""& vinnumber & ""," & _

whereas I said to do this:

" VALUES (""" & vinnumber & """," & _

Note the subtle but crucial difference!