Need help with INSERT INTO statement

J

jkendrick75

i've been working with this for a couple of days, and cannot find where the
syntax error is. maybe i've just looked at too long and am overlooking it...
would appreciate any help with this... code is below
----------------------------------------------------------------
Private Sub cmdEnterRecords_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim intCounter2 As Integer
Dim strPressNumber As String, strMoldNumber As String, strPartNumber As String
Dim strOperator As String, sngCycleTime As Single, intActualCavities As
Integer
Dim sngActualOps As Single, strDate As Date, strShift As String, strPlant As
String
'Add items from listview to table tblDefectCount
For intCounter2 = 1 To intCount
strDate = Me.Date
strShift = Me.shift
strPlant = Me.plant
strPressNumber = lsvListView.ListItems.Item(intCounter2)
strMoldNumber = lsvListView.ListItems.Item(intCounter2).SubItems(1)
strPartNumber = lsvListView.ListItems.Item(intCounter2).SubItems(2)
strOperator = lsvListView.ListItems.Item(intCounter2).SubItems(3)
sngCycleTime = CSng(lsvListView.ListItems.Item(intCounter2).SubItems(4))
intActualCavities = CInt(lsvListView.ListItems.Item(intCounter2).SubItems(5))
sngActualOps = CSng(lsvListView.ListItems.Item(intCounter2).SubItems(6))
MyDB.Execute "Insert Into tblDefectCount(Date, Shift, Plant,
PressNumber, " _
& "MoldNumber, PartNumber, Operator, CycleTime, ActualCavities, " _
& "ActualOps) values (" _
& Chr$(34) & strDate & Chr$(34) & ", " _
& Chr$(34) & strShift & Chr$(34) & ", " _
& Chr$(34) & strPlant & Chr$(34) & ", " _
& Chr$(34) & strPressNumber & Chr$(34) & ", " _
& Chr$(34) & strMoldNumber & Chr$(34) & ", " _
& Chr$(34) & strPartNumber & Chr$(34) & ", " _
& Chr$(34) & strOperator & Chr$(34) & ", " _
& Chr$(34) & sngCycleTime & Chr$(34) & ", " _
& Chr$(34) & intActualCavities & Chr$(34) & ", " _
& Chr$(34) & sngActualOps & Chr$(34) & ")"
Next
End Sub
------------------------------------------------------
 
N

Norman Yuan

The simplest way to spot what is wrong the the SQL string is to change

MyDB.Execute "Insert................."

to

Debug.Print "Insert..........."

Then run to code. Instead of the execution failure, you get all SQL strng
printed in immediate window, so that you can easily tell what is wrong.
 
J

jkendrick75

i did as you suggested, and it printed the following into the immediate window.
---------------------------------------------
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(7/28/2005, 1, 1, 01, 1234, 101454, jeremy, 1, 1, 1)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(7/28/2005, 1, 1, 02, 23456, 20006565, jerem, 2, 2, 2)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(7/28/2005, 1, 1, 03, 34567, 324017ACWW, jere, 3, 3, 3)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(7/28/2005, 1, 1, 04, 45678, 4301494, jer, 4, 4, 4)
 
N

Norman Yuan

From my point of view, the print you get from Debug.Print proves your SQL
statement IS worng.

It should look like this:

Insert Into tblDefectCount(Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#7/28/2005#, '1', '1', '01', '1234', '101454', 'jeremy', '1', '1', '1')

Note, I assume all columns are Text type except for the first one (Date
type). If some of the columns are numeric type, then remove the single quote
marks surrounding that value.

In you code, you used Chr(34), which is double quote mark. They should
really be Chr(39), single quote mark. Since you uae DAO, Date value should
included inside a pair of "#".
 
J

jkendrick75

I've tried to set the date line as #strDate# (along with setting the variable
using dim strDate as date. for whatever reason i am receiving a syntax error
when i do this. i've set a stop for when i click the add button, and the set
mydb = currentdb, when i set the cursor over the mydb part, the tooltip says
"MyDB = Nothing". iv'e copied my current code below.
-------------------------------------------------------------------------
Private Sub cmdEnterRecords_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim intCounter2 As Integer
Dim strPressNumber As String, strMoldNumber As String, strPartNumber As String
Dim strOperator As String, sngCycleTime As Single, intActualCavities As
Integer
Dim sngActualOps As Single, strDate As Date, strShift As String, strPlant As
String
For intCounter2 = 1 To intCount
'Add items from listview to variables
strDate = Me.Date
strShift = Me.shift
strPlant = Me.plant
strPressNumber = lsvListView.ListItems.Item(intCounter2)
strMoldNumber = lsvListView.ListItems.Item(intCounter2).SubItems(1)
strPartNumber = lsvListView.ListItems.Item(intCounter2).SubItems(2)
strOperator = lsvListView.ListItems.Item(intCounter2).SubItems(3)
sngCycleTime = CSng(lsvListView.ListItems.Item(intCounter2).SubItems(4))
intActualCavities =
CInt(lsvListView.ListItems.Item(intCounter2).SubItems(5))
sngActualOps = CSng(lsvListView.ListItems.Item(intCounter2).SubItems(6))
'Insert variable data into tblDefectCount
Debug.Print "Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
" _
& "MoldNumber, PartNumber, Operator, CycleTime, ActualCavities, " _
& "ActualOps) values (" _
& strDate & ", '" _
& strShift & "', '" _
& strPlant & "', '" _
& strPressNumber & "', '" _
& strMoldNumber & "', '" _
& strPartNumber & "', '" _
& strOperator & "', " _
& sngCycleTime & ", " _
& intActualCavities & ", " _
& sngActualOps & ")"
Next
End Sub
-------------------------------------------------------------------
 
B

Brendan Reynolds

MDB or ADP?

--
Brendan Reynolds (MVP)

jkendrick75 said:
I've tried to set the date line as #strDate# (along with setting the
variable
using dim strDate as date. for whatever reason i am receiving a syntax
error
when i do this. i've set a stop for when i click the add button, and the
set
mydb = currentdb, when i set the cursor over the mydb part, the tooltip
says
"MyDB = Nothing". iv'e copied my current code below.
-------------------------------------------------------------------------
Private Sub cmdEnterRecords_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim intCounter2 As Integer
Dim strPressNumber As String, strMoldNumber As String, strPartNumber As
String
Dim strOperator As String, sngCycleTime As Single, intActualCavities As
Integer
Dim sngActualOps As Single, strDate As Date, strShift As String, strPlant
As
String
For intCounter2 = 1 To intCount
'Add items from listview to variables
strDate = Me.Date
strShift = Me.shift
strPlant = Me.plant
strPressNumber = lsvListView.ListItems.Item(intCounter2)
strMoldNumber = lsvListView.ListItems.Item(intCounter2).SubItems(1)
strPartNumber = lsvListView.ListItems.Item(intCounter2).SubItems(2)
strOperator = lsvListView.ListItems.Item(intCounter2).SubItems(3)
sngCycleTime =
CSng(lsvListView.ListItems.Item(intCounter2).SubItems(4))
intActualCavities =
CInt(lsvListView.ListItems.Item(intCounter2).SubItems(5))
sngActualOps =
CSng(lsvListView.ListItems.Item(intCounter2).SubItems(6))
'Insert variable data into tblDefectCount
Debug.Print "Insert Into tblDefectCount(Date, Shift, Plant,
PressNumber,
" _
& "MoldNumber, PartNumber, Operator, CycleTime, ActualCavities, " _
& "ActualOps) values (" _
& strDate & ", '" _
& strShift & "', '" _
& strPlant & "', '" _
& strPressNumber & "', '" _
& strMoldNumber & "', '" _
& strPartNumber & "', '" _
& strOperator & "', " _
& sngCycleTime & ", " _
& intActualCavities & ", " _
& sngActualOps & ")"
Next
End Sub
-------------------------------------------------------------------
Click to expand...
 
J

jkendrick75

MDB. i've tried what yuan suggested with putting the strdate variable in
between # signs, but kept getting a syntax error. i tried the line strdate =
"#" & date & "#" and that still gave me an error. anyways, thanks for having
a look and any ideas are apprecieated.
 
B

Brendan Reynolds

To concatenate the '#' symbols into the SQL string, you want something like
....

& "ActualOps) values (#" _
& strDate & "#, '" _
& strShift & "', '" _

The reason I asked about MDB or ADP is that CurrentDb always returns Nothing
in an ADP. But as this code is running in an MDB, it is something of a
mystery to me how CurrentDb could return Nothing. Are you sure you're
setting the break-point (if that's what you mean?) after the 'Set mydb =
CurrentDb' line?

The reference to 'Me.Date' indicates that you may have a field and/or a
control named 'Date' - that often causes problems, as references to the
field or control may be interpreted as references to the VBA Date()
function. If at all possible, I'd strongly recommend renaming that field
and/or control.
 
J

jkendrick75

i changed the concatenation to what you showed and yes i did have a control
named Date. have changed the name to txtDate, as well as all references in
the code. i am setting the break-point at the 'Private Sub
cmdEnterRecords_Click()' line and stepping through the code. after i have
gone past the "set mydb = currentdb()' line, the mydb variable tooltip shows
'mydb = Nothing'. i've tried to set the breakpoint on the line 'for
intcounter2 = 1 to intcount' but i am no longer getting the tooltip to show
up for the mydb variable.
with all the changes, using a debug.print i am getting the following return
in the immediate window.

Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '01', '1234', '101454', 'jeremy', 1, 1, 1)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '02', '2345', '20006565', 'jerem', 2, 2, 2)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '03', '3456', '324017ACWW', 'jere', 3, 3, 3)

is there supposed to be anything in other modules for connecting to the
database? such as a class module or a regular module where i declare the
connection variables for the database? is there supposed to be more to the
connection parameters other than the
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
?
thanks for help.
 
B

Brendan Reynolds

OK, you've changed the name of the text box, but it looks like you still
have a field in your table named Date. If you can rename it, I'd strongly
advise you to do so, as it will continue to cause you problems if you don't.
If you can't rename it (and it may be very difficult if, for example, the
data file is shared by multiple applications, possibly maintained by
different developers or even different organisations) you may be able to
work around the problem by enclosing all references to the field name in
square brackets, e.g. ...

INSERT INTO tblDefecCount ([Date], Shift etc.

Other than that, the SQL appears to be correct provided that Date is a
Date/Time field, Shift, Plant, PressNumber, MoldNumber, PartNumber and
Operator are all Text fields, and CycleTime, ActualCavities, and ActualOps
are all numeric fields.

CurrentDb is a function that returns a reference to the current Jet
database. I'm afraid it remains a mystery to me how your variable could be
equal to Nothing in these circumstances. In an MDB, after you have assigned
a reference to CurrentDb to the variable, it should not be Nothing unless
you explicitly set it to Nothing or it goes out of scope, i.e. when the
procedure stops running. Try adding the following line immediately after the
'Set mydb = CurrentDb' line ...

Debug.Print (mydb Is Nothing)

What gets printed to the Immediate window - True, or False?
 
J

jkendrick75

ok, i changed the field name to WorkDate, and set the 'Debug.Print (mydb Is
Nothing)' line where you said to. False shows up in the immediate window. i
didn't get any errors showing so i checked the tblDefectCount and found the
data that i entered. so it appears to be working. now since this data is
only have the data for a record, how would i add more data to the same lines.
i plan on having a query based on the date, shift and plant to bring up the
data entered from the current problem, and then entering in such things as
defect codes and hours worked and such. would i use a similar insert into
statement with the new data ( i have an auto generated id number field) or is
there an append statement i would use?


Brendan Reynolds said:
OK, you've changed the name of the text box, but it looks like you still
have a field in your table named Date. If you can rename it, I'd strongly
advise you to do so, as it will continue to cause you problems if you don't.
If you can't rename it (and it may be very difficult if, for example, the
data file is shared by multiple applications, possibly maintained by
different developers or even different organisations) you may be able to
work around the problem by enclosing all references to the field name in
square brackets, e.g. ...

INSERT INTO tblDefecCount ([Date], Shift etc.

Other than that, the SQL appears to be correct provided that Date is a
Date/Time field, Shift, Plant, PressNumber, MoldNumber, PartNumber and
Operator are all Text fields, and CycleTime, ActualCavities, and ActualOps
are all numeric fields.

CurrentDb is a function that returns a reference to the current Jet
database. I'm afraid it remains a mystery to me how your variable could be
equal to Nothing in these circumstances. In an MDB, after you have assigned
a reference to CurrentDb to the variable, it should not be Nothing unless
you explicitly set it to Nothing or it goes out of scope, i.e. when the
procedure stops running. Try adding the following line immediately after the
'Set mydb = CurrentDb' line ...

Debug.Print (mydb Is Nothing)

What gets printed to the Immediate window - True, or False?

--
Brendan Reynolds (MVP)


jkendrick75 said:
i changed the concatenation to what you showed and yes i did have a control
named Date. have changed the name to txtDate, as well as all references
in
the code. i am setting the break-point at the 'Private Sub
cmdEnterRecords_Click()' line and stepping through the code. after i have
gone past the "set mydb = currentdb()' line, the mydb variable tooltip
shows
'mydb = Nothing'. i've tried to set the breakpoint on the line 'for
intcounter2 = 1 to intcount' but i am no longer getting the tooltip to
show
up for the mydb variable.
with all the changes, using a debug.print i am getting the following
return
in the immediate window.

Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '01', '1234', '101454', 'jeremy', 1, 1, 1)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '02', '2345', '20006565', 'jerem', 2, 2, 2)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '03', '3456', '324017ACWW', 'jere', 3, 3, 3)

is there supposed to be anything in other modules for connecting to the
database? such as a class module or a regular module where i declare the
connection variables for the database? is there supposed to be more to
the
connection parameters other than the
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
?
thanks for help.
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Sorry, I'm afraid I didn't understand the question.

As it seems we have solved the original problem with the error in the INSERT
INTO question, it might be best to start a new thread for the new question,
anyway.

Good luck.

--
Brendan Reynolds (MVP)

jkendrick75 said:
ok, i changed the field name to WorkDate, and set the 'Debug.Print (mydb
Is
Nothing)' line where you said to. False shows up in the immediate window.
i
didn't get any errors showing so i checked the tblDefectCount and found
the
data that i entered. so it appears to be working. now since this data is
only have the data for a record, how would i add more data to the same
lines.
i plan on having a query based on the date, shift and plant to bring up
the
data entered from the current problem, and then entering in such things as
defect codes and hours worked and such. would i use a similar insert into
statement with the new data ( i have an auto generated id number field) or
is
there an append statement i would use?


Brendan Reynolds said:
OK, you've changed the name of the text box, but it looks like you still
have a field in your table named Date. If you can rename it, I'd strongly
advise you to do so, as it will continue to cause you problems if you
don't.
If you can't rename it (and it may be very difficult if, for example, the
data file is shared by multiple applications, possibly maintained by
different developers or even different organisations) you may be able to
work around the problem by enclosing all references to the field name in
square brackets, e.g. ...

INSERT INTO tblDefecCount ([Date], Shift etc.

Other than that, the SQL appears to be correct provided that Date is a
Date/Time field, Shift, Plant, PressNumber, MoldNumber, PartNumber and
Operator are all Text fields, and CycleTime, ActualCavities, and
ActualOps
are all numeric fields.

CurrentDb is a function that returns a reference to the current Jet
database. I'm afraid it remains a mystery to me how your variable could
be
equal to Nothing in these circumstances. In an MDB, after you have
assigned
a reference to CurrentDb to the variable, it should not be Nothing unless
you explicitly set it to Nothing or it goes out of scope, i.e. when the
procedure stops running. Try adding the following line immediately after
the
'Set mydb = CurrentDb' line ...

Debug.Print (mydb Is Nothing)

What gets printed to the Immediate window - True, or False?

--
Brendan Reynolds (MVP)


jkendrick75 said:
i changed the concatenation to what you showed and yes i did have a
control
named Date. have changed the name to txtDate, as well as all
references
in
the code. i am setting the break-point at the 'Private Sub
cmdEnterRecords_Click()' line and stepping through the code. after i
have
gone past the "set mydb = currentdb()' line, the mydb variable tooltip
shows
'mydb = Nothing'. i've tried to set the breakpoint on the line 'for
intcounter2 = 1 to intcount' but i am no longer getting the tooltip to
show
up for the mydb variable.
with all the changes, using a debug.print i am getting the following
return
in the immediate window.

Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '01', '1234', '101454', 'jeremy', 1, 1, 1)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '02', '2345', '20006565', 'jerem', 2, 2, 2)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '03', '3456', '324017ACWW', 'jere', 3, 3, 3)

is there supposed to be anything in other modules for connecting to the
database? such as a class module or a regular module where i declare
the
connection variables for the database? is there supposed to be more to
the
connection parameters other than the
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
?
thanks for help.

:


To concatenate the '#' symbols into the SQL string, you want something
like
....

& "ActualOps) values (#" _
& strDate & "#, '" _
& strShift & "', '" _

The reason I asked about MDB or ADP is that CurrentDb always returns
Nothing
in an ADP. But as this code is running in an MDB, it is something of a
mystery to me how CurrentDb could return Nothing. Are you sure you're
setting the break-point (if that's what you mean?) after the 'Set mydb
=
CurrentDb' line?

The reference to 'Me.Date' indicates that you may have a field and/or
a
control named 'Date' - that often causes problems, as references to
the
field or control may be interpreted as references to the VBA Date()
function. If at all possible, I'd strongly recommend renaming that
field
and/or control.

--
Brendan Reynolds (MVP)


MDB. i've tried what yuan suggested with putting the strdate
variable
in
between # signs, but kept getting a syntax error. i tried the line
strdate =
"#" & date & "#" and that still gave me an error. anyways, thanks
for
having
a look and any ideas are apprecieated.


:

MDB or ADP?

--
Brendan Reynolds (MVP)

message
I've tried to set the date line as #strDate# (along with setting
the
variable
using dim strDate as date. for whatever reason i am receiving a
syntax
error
when i do this. i've set a stop for when i click the add button,
and
the
set
mydb = currentdb, when i set the cursor over the mydb part, the
tooltip
says
"MyDB = Nothing". iv'e copied my current code below.
-------------------------------------------------------------------------
Private Sub cmdEnterRecords_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim intCounter2 As Integer
Dim strPressNumber As String, strMoldNumber As String,
strPartNumber
As
String
Dim strOperator As String, sngCycleTime As Single,
intActualCavities
As
Integer
Dim sngActualOps As Single, strDate As Date, strShift As String,
strPlant
As
String
For intCounter2 = 1 To intCount
'Add items from listview to variables
strDate = Me.Date
strShift = Me.shift
strPlant = Me.plant
strPressNumber = lsvListView.ListItems.Item(intCounter2)
strMoldNumber =
lsvListView.ListItems.Item(intCounter2).SubItems(1)
strPartNumber =
lsvListView.ListItems.Item(intCounter2).SubItems(2)
strOperator =
lsvListView.ListItems.Item(intCounter2).SubItems(3)
sngCycleTime =
CSng(lsvListView.ListItems.Item(intCounter2).SubItems(4))
intActualCavities =
CInt(lsvListView.ListItems.Item(intCounter2).SubItems(5))
sngActualOps =
CSng(lsvListView.ListItems.Item(intCounter2).SubItems(6))
'Insert variable data into tblDefectCount
Debug.Print "Insert Into tblDefectCount(Date, Shift, Plant,
PressNumber,
" _
& "MoldNumber, PartNumber, Operator, CycleTime,
ActualCavities,
" _
& "ActualOps) values (" _
& strDate & ", '" _
& strShift & "', '" _
& strPlant & "', '" _
& strPressNumber & "', '" _
& strMoldNumber & "', '" _
& strPartNumber & "', '" _
& strOperator & "', " _
& sngCycleTime & ", " _
& intActualCavities & ", " _
& sngActualOps & ")"
Next
End Sub
-------------------------------------------------------------------
:

From my point of view, the print you get from Debug.Print proves
your
SQL
statement IS worng.

It should look like this:

Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(#7/28/2005#, '1', '1', '01', '1234', '101454', 'jeremy', '1',
'1',
'1')

Note, I assume all columns are Text type except for the first
one
(Date
type). If some of the columns are numeric type, then remove the
single
quote
marks surrounding that value.

In you code, you used Chr(34), which is double quote mark. They
should
really be Chr(39), single quote mark. Since you uae DAO, Date
value
should
included inside a pair of "#".

message
i did as you suggested, and it printed the following into the
immediate
window.
---------------------------------------------
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 01, 1234, 101454, jeremy, 1, 1, 1)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 02, 23456, 20006565, jerem, 2, 2, 2)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 03, 34567, 324017ACWW, jere, 3, 3, 3)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 04, 45678, 4301494, jer, 4, 4, 4)
-------------------------------------------------------
Which is correct, as far as getting the data. but it also
prints
out
the
Insert into tblDefectCount. so i'm still stuck. thanks for
looking
at
it,
and if anything else comes to mind, would appreciate the help.

:

The simplest way to spot what is wrong the the SQL string is
to
change

MyDB.Execute "Insert................."

to

Debug.Print "Insert..........."

Then run to code. Instead of the execution failure, you get
all
SQL
strng
printed in immediate window, so that you can easily tell
what
is
wrong.

in
message
i've been working with this for a couple of days, and
cannot
find
where
the
syntax error is. maybe i've just looked at too long and
am
overlooking
it...
would appreciate any help with this... code is below
----------------------------------------------------------------
Private Sub cmdEnterRecords_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim intCounter2 As Integer
Dim strPressNumber As String, strMoldNumber As String,
strPartNumber
As
String
Dim strOperator As String, sngCycleTime As Single,
intActualCavities
As
Integer
Dim sngActualOps As Single, strDate As Date, strShift As
String,
strPlant
As
String
'Add items from listview to table tblDefectCount
For intCounter2 = 1 To intCount
strDate = Me.Date
strShift = Me.shift
strPlant = Me.plant
Click to expand...
Click to expand...
Click to expand...
 
J

jkendrick75

ok no prob. thanks for the help.

Brendan Reynolds said:
Sorry, I'm afraid I didn't understand the question.

As it seems we have solved the original problem with the error in the INSERT
INTO question, it might be best to start a new thread for the new question,
anyway.

Good luck.

--
Brendan Reynolds (MVP)

jkendrick75 said:
ok, i changed the field name to WorkDate, and set the 'Debug.Print (mydb
Is
Nothing)' line where you said to. False shows up in the immediate window.
i
didn't get any errors showing so i checked the tblDefectCount and found
the
data that i entered. so it appears to be working. now since this data is
only have the data for a record, how would i add more data to the same
lines.
i plan on having a query based on the date, shift and plant to bring up
the
data entered from the current problem, and then entering in such things as
defect codes and hours worked and such. would i use a similar insert into
statement with the new data ( i have an auto generated id number field) or
is
there an append statement i would use?


Brendan Reynolds said:
OK, you've changed the name of the text box, but it looks like you still
have a field in your table named Date. If you can rename it, I'd strongly
advise you to do so, as it will continue to cause you problems if you
don't.
If you can't rename it (and it may be very difficult if, for example, the
data file is shared by multiple applications, possibly maintained by
different developers or even different organisations) you may be able to
work around the problem by enclosing all references to the field name in
square brackets, e.g. ...

INSERT INTO tblDefecCount ([Date], Shift etc.

Other than that, the SQL appears to be correct provided that Date is a
Date/Time field, Shift, Plant, PressNumber, MoldNumber, PartNumber and
Operator are all Text fields, and CycleTime, ActualCavities, and
ActualOps
are all numeric fields.

CurrentDb is a function that returns a reference to the current Jet
database. I'm afraid it remains a mystery to me how your variable could
be
equal to Nothing in these circumstances. In an MDB, after you have
assigned
a reference to CurrentDb to the variable, it should not be Nothing unless
you explicitly set it to Nothing or it goes out of scope, i.e. when the
procedure stops running. Try adding the following line immediately after
the
'Set mydb = CurrentDb' line ...

Debug.Print (mydb Is Nothing)

What gets printed to the Immediate window - True, or False?

--
Brendan Reynolds (MVP)


i changed the concatenation to what you showed and yes i did have a
control
named Date. have changed the name to txtDate, as well as all
references
in
the code. i am setting the break-point at the 'Private Sub
cmdEnterRecords_Click()' line and stepping through the code. after i
have
gone past the "set mydb = currentdb()' line, the mydb variable tooltip
shows
'mydb = Nothing'. i've tried to set the breakpoint on the line 'for
intcounter2 = 1 to intcount' but i am no longer getting the tooltip to
show
up for the mydb variable.
with all the changes, using a debug.print i am getting the following
return
in the immediate window.

Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '01', '1234', '101454', 'jeremy', 1, 1, 1)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '02', '2345', '20006565', 'jerem', 2, 2, 2)
Insert Into tblDefecCount (Date, Shift, Plant, PressNumber, MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps) values
(#8/3/2005#, '1', '1', '03', '3456', '324017ACWW', 'jere', 3, 3, 3)

is there supposed to be anything in other modules for connecting to the
database? such as a class module or a regular module where i declare
the
connection variables for the database? is there supposed to be more to
the
connection parameters other than the
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
?
thanks for help.

:


To concatenate the '#' symbols into the SQL string, you want something
like
....

& "ActualOps) values (#" _
& strDate & "#, '" _
& strShift & "', '" _

The reason I asked about MDB or ADP is that CurrentDb always returns
Nothing
in an ADP. But as this code is running in an MDB, it is something of a
mystery to me how CurrentDb could return Nothing. Are you sure you're
setting the break-point (if that's what you mean?) after the 'Set mydb
=
CurrentDb' line?

The reference to 'Me.Date' indicates that you may have a field and/or
a
control named 'Date' - that often causes problems, as references to
the
field or control may be interpreted as references to the VBA Date()
function. If at all possible, I'd strongly recommend renaming that
field
and/or control.

--
Brendan Reynolds (MVP)


MDB. i've tried what yuan suggested with putting the strdate
variable
in
between # signs, but kept getting a syntax error. i tried the line
strdate =
"#" & date & "#" and that still gave me an error. anyways, thanks
for
having
a look and any ideas are apprecieated.


:

MDB or ADP?

--
Brendan Reynolds (MVP)

message
I've tried to set the date line as #strDate# (along with setting
the
variable
using dim strDate as date. for whatever reason i am receiving a
syntax
error
when i do this. i've set a stop for when i click the add button,
and
the
set
mydb = currentdb, when i set the cursor over the mydb part, the
tooltip
says
"MyDB = Nothing". iv'e copied my current code below.
-------------------------------------------------------------------------
Private Sub cmdEnterRecords_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim intCounter2 As Integer
Dim strPressNumber As String, strMoldNumber As String,
strPartNumber
As
String
Dim strOperator As String, sngCycleTime As Single,
intActualCavities
As
Integer
Dim sngActualOps As Single, strDate As Date, strShift As String,
strPlant
As
String
For intCounter2 = 1 To intCount
'Add items from listview to variables
strDate = Me.Date
strShift = Me.shift
strPlant = Me.plant
strPressNumber = lsvListView.ListItems.Item(intCounter2)
strMoldNumber =
lsvListView.ListItems.Item(intCounter2).SubItems(1)
strPartNumber =
lsvListView.ListItems.Item(intCounter2).SubItems(2)
strOperator =
lsvListView.ListItems.Item(intCounter2).SubItems(3)
sngCycleTime =
CSng(lsvListView.ListItems.Item(intCounter2).SubItems(4))
intActualCavities =
CInt(lsvListView.ListItems.Item(intCounter2).SubItems(5))
sngActualOps =
CSng(lsvListView.ListItems.Item(intCounter2).SubItems(6))
'Insert variable data into tblDefectCount
Debug.Print "Insert Into tblDefectCount(Date, Shift, Plant,
PressNumber,
" _
& "MoldNumber, PartNumber, Operator, CycleTime,
ActualCavities,
" _
& "ActualOps) values (" _
& strDate & ", '" _
& strShift & "', '" _
& strPlant & "', '" _
& strPressNumber & "', '" _
& strMoldNumber & "', '" _
& strPartNumber & "', '" _
& strOperator & "', " _
& sngCycleTime & ", " _
& intActualCavities & ", " _
& sngActualOps & ")"
Next
End Sub
-------------------------------------------------------------------
:

From my point of view, the print you get from Debug.Print proves
your
SQL
statement IS worng.

It should look like this:

Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(#7/28/2005#, '1', '1', '01', '1234', '101454', 'jeremy', '1',
'1',
'1')

Note, I assume all columns are Text type except for the first
one
(Date
type). If some of the columns are numeric type, then remove the
single
quote
marks surrounding that value.

In you code, you used Chr(34), which is double quote mark. They
should
really be Chr(39), single quote mark. Since you uae DAO, Date
value
should
included inside a pair of "#".

message
i did as you suggested, and it printed the following into the
immediate
window.
---------------------------------------------
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 01, 1234, 101454, jeremy, 1, 1, 1)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 02, 23456, 20006565, jerem, 2, 2, 2)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 03, 34567, 324017ACWW, jere, 3, 3, 3)
Insert Into tblDefectCount(Date, Shift, Plant, PressNumber,
MoldNumber,
PartNumber, Operator, CycleTime, ActualCavities, ActualOps)
values
(7/28/2005, 1, 1, 04, 45678, 4301494, jer, 4, 4, 4)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update/Insert Statement 6
need help with entering listview control data into database. 0
Need help with code and query 1
Need help with Macro to format comments 1
Need help with deleteing sheets using VBA 4
Need Help Handling Apostrophes with New Data 9
Need some help with this code: 2
Help with INSERT INTO statement 10

Top