Insert Into Question

D

DawnP277

I am trying to insert into a table from vb after one record has been added
above through a form.

My problem I need all the information on the form to be duplicated except
the block number needs to be modified based on the orginal block number.
My form has a combo box for Employee, list boxes for sheeter, shift , and
printline, text box for work order , block number, I also have a memo box for
comments.

My code is below I keep getting a syntax issue at the execute line.

If some one can look at my code and let me know what is wwrong that would be
great.


My values are all variables that have held before I save the record of the
one on the form.

strSQL = "INSERT INTO NomexSheeter ([Sheeter], [Shift], [Employee],
[WorkOrder],[BlockNumber],[BlockSection],[Comments], [FoilNum_1],[
FoilNum_2], [FoilNum_3],[PrintLine],[Second Section],[SecondBlock]) " & _
" VALUES ('" & sSheeter & "','" & sShift & " ',' " & sEmployee & " ',' " &
sWorkOrder & " '," & SecBlock & ",' " & SecSection & " ',' " & Comments & "
',' " & sCoil1 & "',' " & sCoil2 & " ',' " & sCoil3 & " ',' " & sPrintLine &
" ',' " & Blank & " ',' " & Blank2 & ")"


Thanks
 
J

John W. Vinson

I am trying to insert into a table from vb after one record has been added
above through a form.

My problem I need all the information on the form to be duplicated except
the block number needs to be modified based on the orginal block number.
My form has a combo box for Employee, list boxes for sheeter, shift , and
printline, text box for work order , block number, I also have a memo box for
comments.

My code is below I keep getting a syntax issue at the execute line.

If some one can look at my code and let me know what is wwrong that would be
great.


My values are all variables that have held before I save the record of the
one on the form.

strSQL = "INSERT INTO NomexSheeter ([Sheeter], [Shift], [Employee],
[WorkOrder],[BlockNumber],[BlockSection],[Comments], [FoilNum_1],[
FoilNum_2], [FoilNum_3],[PrintLine],[Second Section],[SecondBlock]) " & _
" VALUES ('" & sSheeter & "','" & sShift & " ',' " & sEmployee & " ',' " &
sWorkOrder & " '," & SecBlock & ",' " & SecSection & " ',' " & Comments & "
',' " & sCoil1 & "',' " & sCoil2 & " ',' " & sCoil3 & " ',' " & sPrintLine &
" ',' " & Blank & " ',' " & Blank2 & ")"


Thanks
Click to expand...

Please step through your code and post the actual value of strSQL after this
step has been run. Also indicate the datatypes of these various fields.

You may have some extra blanks or misplaced quotes.
 
J

John Spencer

All the field names have no spaces except for Second Section. Is that the
correct field name?

You are adding leading spaces and trailing spaces in most places in the values
clause.

Are Blank and Blank2 zero length strings or nulls? If so, will Second Section
and Second block accept a zero-length string?

Is SecBlock a number field? If not, you have failed to supply the quote marks
around it.

Can any of your text values contain an apostrophe ' as part of the string? If
so, you need to handle that situation by changing the apostrophe to in the
string to two apostrophes in a row.
strSQL = "INSERT INTO NomexSheeter ([Sheeter], [Shift], [Employee], " & _
"[WorkOrder],[BlockNumber],[BlockSection],[Comments], [FoilNum_1]," & _
"[FoilNum_2], [FoilNum_3],[PrintLine],[Second Section],[SecondBlock]) " & _
" VALUES ('" & sSheeter & "','" & sShift & "','" & sEmployee & _
"','" & sWorkOrder & "'," & SecBlock & ",'" & SecSection & _
"','" & Comments & "','" & sCoil1 & "','" & sCoil2 & "','" & sCoil3 & _
"','" & sPrintLine & _
"','" & Blank & "','" & Blank2 & ")"

You can try using Debug.Print strSQL to print the actual SQL string. You can
then cut and paste that into a query SQL view and attempt to run the query.
That should give you an idea of where and/or what the specific error is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
D

Daryl S

Dawn -

You are missing your final single quote after the Blank2 variable. Try this:

strSQL = "INSERT INTO NomexSheeter ([Sheeter], [Shift], [Employee], " & _
"[WorkOrder],[BlockNumber],[BlockSection],[Comments], [FoilNum_1]," & _
"[FoilNum_2], [FoilNum_3],[PrintLine],[Second Section],[SecondBlock]) " & _
" VALUES ('" & sSheeter & "','" & sShift & "','" & sEmployee & _
"','" & sWorkOrder & "'," & SecBlock & ",'" & SecSection & _
"','" & Comments & "','" & sCoil1 & "','" & sCoil2 & "','" & sCoil3 & _
"','" & sPrintLine & _
"','" & Blank & "','" & Blank2 & "')"
 
D

DawnP277

Daryl
Thanks for noticing my problem.

I had an issue with some other syntax as well.

Thanks for pointing me in the right direction.

Dawn

Daryl S said:
Dawn -

You are missing your final single quote after the Blank2 variable. Try this:

strSQL = "INSERT INTO NomexSheeter ([Sheeter], [Shift], [Employee], " & _
"[WorkOrder],[BlockNumber],[BlockSection],[Comments], [FoilNum_1]," & _
"[FoilNum_2], [FoilNum_3],[PrintLine],[Second Section],[SecondBlock]) " & _
" VALUES ('" & sSheeter & "','" & sShift & "','" & sEmployee & _
"','" & sWorkOrder & "'," & SecBlock & ",'" & SecSection & _
"','" & Comments & "','" & sCoil1 & "','" & sCoil2 & "','" & sCoil3 & _
"','" & sPrintLine & _
"','" & Blank & "','" & Blank2 & "')"

--
Daryl S


DawnP277 said:
I am trying to insert into a table from vb after one record has been added
above through a form.

My problem I need all the information on the form to be duplicated except
the block number needs to be modified based on the orginal block number.
My form has a combo box for Employee, list boxes for sheeter, shift , and
printline, text box for work order , block number, I also have a memo box for
comments.

My code is below I keep getting a syntax issue at the execute line.

If some one can look at my code and let me know what is wwrong that would be
great.


My values are all variables that have held before I save the record of the
one on the form.

strSQL = "INSERT INTO NomexSheeter ([Sheeter], [Shift], [Employee],
[WorkOrder],[BlockNumber],[BlockSection],[Comments], [FoilNum_1],[
FoilNum_2], [FoilNum_3],[PrintLine],[Second Section],[SecondBlock]) " & _
" VALUES ('" & sSheeter & "','" & sShift & " ',' " & sEmployee & " ',' " &
sWorkOrder & " '," & SecBlock & ",' " & SecSection & " ',' " & Comments & "
',' " & sCoil1 & "',' " & sCoil2 & " ',' " & sCoil3 & " ',' " & sPrintLine &
" ',' " & Blank & " ',' " & Blank2 & ")"


Thanks
Click to expand...
Click to expand...
 
Top