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

  • Thread starter Thread starter FALSE_EMAIL
  • Start date Start date
F

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
 
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
Click to expand...

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.
 
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
 
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
Click to expand...

You've done it wrong then. If you post your attempt then I'm sure someone
will correct it.
 
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
 
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 & ""," & _
Click to expand...

You didn't use the same number of quotes that Brian said to
use.
 
Marshall Barton said:
You didn't use the same number of quotes that Brian said to
use.
Click to expand...

Marsh is right, you did this:

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

whereas I said to do this:

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

Note the subtle but crucial difference!
 
Back
Top