Help with INSERT INTO statement

[jkendrick75]

I am needing help with an INSERT INTO statement.
I am using Access 2000 and i have three tables that are all linked together
on a particular field. I altered the following code from someone else's post
(as far as the MyDB.execute parts go).

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
MyDB.Execute "Insert Into DBA_cs_part_profile_vw1 (Part, " _
& "Customer_Part) Values (" _
& [txtInternalPartNumber] & ", " _
& [txtExternalPartNumber] & ")"
MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"
MyDB.Execute "Insert Into DBA_part_machine1 (Part, parts_per_cycle
" _
& "cycle_time, crew_size) Values (" _
& [txtInternalPartTool] & ", " _
& [txtNumberCavities] & ", " _
& [txtCycleTime] & ", " _
& [txtNumberOperators] & ")"
Exit_cmdSaveRecord_Click:
Exit Sub
Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub

When i click on the Save Record command button on my form, i get the
following error: "Compile Error. Userdefined type not defined." and the "Dim
MyDB as DAO.Database line is highlighted. how do i define MyDB as a
Database, or is this problem being generated from something else.

 

[Dirk Goldgar]

I am needing help with an INSERT INTO statement.
I am using Access 2000 and i have three tables that are all linked
together on a particular field. I altered the following code from
someone else's post (as far as the MyDB.execute parts go).

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
MyDB.Execute "Insert Into DBA_cs_part_profile_vw1 (Part, " _
& "Customer_Part) Values (" _
& [txtInternalPartNumber] & ", " _
& [txtExternalPartNumber] & ")"
MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part,
Machine " _ & "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"
MyDB.Execute "Insert Into DBA_part_machine1 (Part,
parts_per_cycle " _
& "cycle_time, crew_size) Values (" _
& [txtInternalPartTool] & ", " _
& [txtNumberCavities] & ", " _
& [txtCycleTime] & ", " _
& [txtNumberOperators] & ")"
Exit_cmdSaveRecord_Click:
Exit Sub
Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub

When i click on the Save Record command button on my form, i get the
following error: "Compile Error. Userdefined type not defined." and
the "Dim MyDB as DAO.Database line is highlighted. how do i define
MyDB as a Database, or is this problem being generated from something
else.

You need to define a reference to the DAO object library. While viewing
the code, click Tools -> References..., locate "Microsoft DAO 3.6 Object
Library" in the list, and put a check mark in the box next to it. Then
close the References dialog, and compile your project.

 

[jkendrick75]

ok, thanks. did what you suggested, but now am getting an error of

"Syntax error in INSERT INTO statement."

any ideas? i am trying to find something, but no luck so far. thanks for
your help and any ideas on this new error would be appreciated.

 

[Dirk Goldgar]

ok, thanks. did what you suggested, but now am getting an error of

"Syntax error in INSERT INTO statement."

any ideas? i am trying to find something, but no luck so far. thanks
for your help and any ideas on this new error would be appreciated.

You don't say which of the MyDB.Execute lines raises the error, but I do
notice this one:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

It seems to be missing a comma between "Machine" and "Tool" in the field
list. Aside from that, all I can say is that all of the statements
appear to assume that the fields involved are numeric fields, not text
or date/time fields. If they are not numeric, the values being embedded
must be surrounded by the appropriate delimiters.

 

[Van T. Dinh]

... and comma is missing from the next INSERT SQL also.


--
HTH
Van T. Dinh
MVP (Access)

ok, thanks. did what you suggested, but now am getting an error of

"Syntax error in INSERT INTO statement."

any ideas? i am trying to find something, but no luck so far. thanks
for your help and any ideas on this new error would be appreciated.

You don't say which of the MyDB.Execute lines raises the error, but I do
notice this one:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

It seems to be missing a comma between "Machine" and "Tool" in the field
list. Aside from that, all I can say is that all of the statements
appear to assume that the fields involved are numeric fields, not text
or date/time fields. If they are not numeric, the values being embedded
must be surrounded by the appropriate delimiters.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[jkendrick75]

Ok, added the commas, now getting a message box saying
"Too few parameters. Expected 3."
i went through the tables and found that 3 of the fields in one table are
set for decimal. all other fields are text. i am getting the information
into the table
DBA_cs_part_profile_vw1. i have tried to put a ' and a " around the text
fields, but they are giving me the error of "Syntax error (missing operator)
in query expression 'Press 20'."
"Press 20" is the data that i am putting into txtMachine on the form.
not sure where to go from here.

ok, thanks. did what you suggested, but now am getting an error of

"Syntax error in INSERT INTO statement."

any ideas? i am trying to find something, but no luck so far. thanks
for your help and any ideas on this new error would be appreciated.

You don't say which of the MyDB.Execute lines raises the error, but I do
notice this one:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

It seems to be missing a comma between "Machine" and "Tool" in the field
list. Aside from that, all I can say is that all of the statements
appear to assume that the fields involved are numeric fields, not text
or date/time fields. If they are not numeric, the values being embedded
must be surrounded by the appropriate delimiters.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[jkendrick75]

i went through and checked everything, then i put data into the form, and for
the text box asking for the machine number, as long as i don't put in the
word "Press" it works fine. however, in order to keep the data similar to
what is already in the table, i would like the users to be able to put in the
word "Press" for the machine number. i have checked the table that
corresponds to this field, and it is set as text.
i would try to put in some code to automatically add the word "Press" except
that not all machines are presses. any ideas, help, is appreciated. thanks

Ok, added the commas, now getting a message box saying
"Too few parameters. Expected 3."
i went through the tables and found that 3 of the fields in one table are
set for decimal. all other fields are text. i am getting the information
into the table
DBA_cs_part_profile_vw1. i have tried to put a ' and a " around the text
fields, but they are giving me the error of "Syntax error (missing operator)
in query expression 'Press 20'."
"Press 20" is the data that i am putting into txtMachine on the form.
not sure where to go from here.

ok, thanks. did what you suggested, but now am getting an error of

"Syntax error in INSERT INTO statement."

any ideas? i am trying to find something, but no luck so far. thanks
for your help and any ideas on this new error would be appreciated.

You don't say which of the MyDB.Execute lines raises the error, but I do
notice this one:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

It seems to be missing a comma between "Machine" and "Tool" in the field
list. Aside from that, all I can say is that all of the statements
appear to assume that the fields involved are numeric fields, not text
or date/time fields. If they are not numeric, the values being embedded
must be surrounded by the appropriate delimiters.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

i went through and checked everything, then i put data into the form,
and for the text box asking for the machine number, as long as i
don't put in the word "Press" it works fine. however, in order to
keep the data similar to what is already in the table, i would like
the users to be able to put in the word "Press" for the machine
number. i have checked the table that corresponds to this field, and
it is set as text.
i would try to put in some code to automatically add the word "Press"
except that not all machines are presses. any ideas, help, is
appreciated. thanks

That doesn't make sense to me. If it's presented as part of a quoted
string, I can't think why the word press would have special
significance. Are you inserting this into a text field, or trying to
put it in a numeric field (where, of course, it cannot go)? Are you
sure the value you are trying to insert doesn't contain the quote
character you are using?

Please post the complete statement as it is now, and tell which fields
in the table have which data types.

 

[jkendrick75]

I know, it's not making sense to me either. the code is as follows:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine, " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

and the table structure for DBA_part_machine_tool1 is:

part - text (25 characters, Indexed (duplicates OK))
machine - text (10 characters)
tool - text (25 characters)

Could this be a probelm with the connection to the database being set as DAO?

 

[Douglas J Steele]

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine, " _
& "Tool) Values (" _
& Chr$(34) & Me.txtInternalPartMachine1 & Chr$(34) & ", " _
& Chr$(34) & Me.txtMachine & Chr$(34) & ", " _
& Chr$(34) & Me.txtToolNumber & Chr$(34) & ")", dbFailOnError

The addition of the dbFailOnError allows you to trap any errors that may
occur.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I know, it's not making sense to me either. the code is as follows:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine, " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

and the table structure for DBA_part_machine_tool1 is:

part - text (25 characters, Indexed (duplicates OK))
machine - text (10 characters)
tool - text (25 characters)

Could this be a probelm with the connection to the database being set as DAO?

That doesn't make sense to me. If it's presented as part of a quoted
string, I can't think why the word press would have special
significance. Are you inserting this into a text field, or trying to
put it in a numeric field (where, of course, it cannot go)? Are you
sure the value you are trying to insert doesn't contain the quote
character you are using?

Please post the complete statement as it is now, and tell which fields
in the table have which data types.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I know, it's not making sense to me either. the code is as follows:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine, " _
& "Tool) Values (" _
& [txtInternalPartMachine1] & ", " _
& [txtMachine] & ", " _
& [txtToolNumber] & ")"

and the table structure for DBA_part_machine_tool1 is:

part - text (25 characters, Indexed (duplicates OK))
machine - text (10 characters)
tool - text (25 characters)

Could this be a probelm with the connection to the database being set
as DAO?

Nope.

I thought you said you'd put the quotes around the text fields. They
aren't there in what you just posted. See Doug Steele's response for
one way to do it. If these fields will never contain the single-quote
character, here's an alternative:

MyDB.Execute "Insert Into DBA_part_machine_tool1 (Part, Machine, " _
& "Tool) Values ('" _
& [txtInternalPartMachine1] & "', '" _
& [txtMachine] & "', '" _
& [txtToolNumber] & "')"