Receive a 3134 error

  • Thread starter Thread starter dst393
  • Start date Start date
D

dst393

I am getting an error with my insert statement. The code is very long but
here it is.....

strInsert = "Insert into Maintable (CourseTitle, Assignee, Location,
StartDate, EndDate, " & _
"LocationRvedDate, DateInstructorCleared, PostMTB,
PostTCRS, PostSATERN, SWRDte, RoomCapacity, " & _
"CoordProvDte, CloseOutDte, InvoiceRecved, EvalTechSent,
DCNConfirm, Vendor, VendorAddress, " & _
"VidRema, MaxOfStudents, EstimatedCost, EquipmentNeeded,
EntranceG, POC, POCPhone, POCFax," & _
"PostCCLog, EvalRecved, MatRetToNSTC, CopyToSPLC,
FinanSent, TVViewDte, " & _
"Cancelled, Postponed, Starttime, Endtime, Remarks,
Category, Targetaudience, " & _
"Rescheduled, CoursNumber, Coursetype, Instructor,
ScheduleOffID, SiteMap, SelfReg) VALUES ("

If IsNull(txtCT.Value) Then 'Course Title
MsgBox "Please enter the course title"
strInsert = ""
Exit Sub
Else
strCrsNme = txtCT.Value
strInsert = strInsert & "'" & strCrsNme & "', "
End If
If IsNull(cboAss.Value) Then 'Assignee
MsgBox "Please select an assignee"
strInsert = ""
Exit Sub
Else
lAssi = cboAss.ListIndex
strAss = cboAss.Value
strInsert = strInsert & "'" & strAss & "', "
End If

If IsNull(cboLoc.Value) Then 'Location
MsgBox "Please select a location"
strInsert = ""
Exit Sub
Else
lLoca = cboLoc.ListIndex
strLoc = cboLoc.Value
strInsert = strInsert & "'" & strLoc & "', "
End If
If IsNull(txtStartDate.Value) Then 'Start Date
MsgBox "Please enter the start date"
strInsert = ""
Exit Sub
Else
dtStrtDte = txtStartDate.Value
strInsert = strInsert & "#" & dtStrtDte & "#, "
End If
If IsNull(txtEndDate) Then 'End Date
MsgBox "Please enter the end date"
strInsert = ""
Exit Sub
Else
dtEndDte = txtEndDate.Value
strInsert = strInsert & "#" & dtEndDte & "#, "
End If
If IsNull(txtDteLocRes.Value) Then 'Date Location Reserved
txtDteLocRes.Value = ""
strInsert = strInsert & ", "
Else
dtLocResved = txtDteLocRes.Value
strInsert = strInsert & "#" & dtLocResved & "#, "
End If
If IsNull(txtDteInstructClred.Value) Then 'Date Instructor Cleared
dtInstClred = ""
strInsert = strInsert & ", "
Else
dtInstClred = txtDteInstructClred.Value
strInsert = strInsert & "#" & dtInstClred & "#, "
End If
If IsNull(txtMTBPost.Value) Then 'Post in MTB
dtPostMTB = ""
strInsert = strInsert & ", "
Else
dtPostMTB = txtMTBPost.Value
strInsert = strInsert & "#" & dtPostMTB & "#, "
End If
If IsNull(txtPostTCRS.Value) Then 'Post in TCRS
dtPostTCRS = ""
strInsert = strInsert & ", "
Else
dtPostTCRS = txtPostTCRS.Value
strInsert = strInsert & "#" & dtPostTCRS & "#, "
End If
If IsNull(txtPostSAT.Value) Then 'Post in SATERN
dtPostSATERN = ""
strInsert = strInsert & ", "
Else
dtPostSATERN = txtPostSAT.Value
strInsert = strInsert & "#" & dtPostSATERN & "#, "
End If
If IsNull(txtDteSWRVB.Value) Then 'Date SWR Provided
dtSWRProv = ""
strInsert = strInsert & ", "
Else
dtSWRProv = txtDteSWRVB.Value
strInsert = strInsert & "#" & dtSWRProv & "#, "
End If
lblMaxNum = Val(MaxCap_lbl.Caption) 'Maximum Capacity
strInsert = strInsert & lblMaxNum & ", "
If IsNull(txtCooProDte.Value) Then 'Coordinate Provided Date
dtCooProv = ""
strInsert = strInsert & ", "
Else
dtCooProv = txtCooProDte.Value
strInsert = strInsert & "#" & dtCooProv & "#, "
End If
If IsNull(txtClsOut.Value) Then 'Close Out
dtClsOut = ""
strInsert = strInsert & ", "
Else
dtClsOut = txtClsOut.Value
strInsert = strInsert & "#" & dtClsOut & "#, "
End If
If IsNull(txtInvRecved.Value) Then 'Invoice Receive
dtInvRecevd = ""
strInsert = strInsert & ", "
Else
dtInvRecevd = txtInvRecved.Value
strInsert = strInsert & "#" & dtInvRecevd & "#, "
End If
If IsNull(txtEvalTechMon.Value) Then 'Date Evaluation sent to Technical
Monitor
dtEvalTechSent = ""
strInsert = strInsert & ", "
Else
dtEvalTechSent = txtEvalTechMon.Value
strInsert = strInsert & "#" & dtEvalTechSent & "#, "
End If
If IsNull(txtDCNCon.Value) Then 'DCN Confirm
dtDCNConfirm = ""
strInsert = strInsert & ", "
Else
dtDCNConfirm = txtDCNCon.Value
strInsert = strInsert & "#" & dtDCNConfirm & "#, "
End If
If IsNull(txtVendor.Value) Then 'Vendor
strVen = ""
strInsert = strInsert & ", "
Else
strVen = txtVendor.Value
strInsert = strInsert & "'" & strVen & "', "
End If
If IsNull(txtVenAddr.Value) Then 'Vendor Address
strVenAddr = ""
strInsert = strInsert & ", "
Else
strVenAddr = txtVenAddr.Value
strInsert = strInsert & "'" & strVenAddr & "', "
End If
If IsNull(txtVBRem.Value) Then 'Video Broadcast Remarks
strVidBrRem = ""
strInsert = strInsert & ", "
Else
strVidBrRem = txtVBRem.Value
strInsert = strInsert & "'" & strVidBrRem & "', "
End If
lblMaxNum = Val(MaxCap_lbl.Caption) 'Maximum Number of Students
strInsert = strInsert & lblMaxNum & ", "
If IsNull(txtCost.Value) Then 'Cost
strCost = ""
strInsert = strInsert & ", "
Else
strCost = txtCost.Value
strInsert = strInsert & "'" & strCost & "', "
End If
If IsNull(txtEquipNeed.Value) Then 'Equipment Need
strEquipNeed = ""
strInsert = strInsert & ", "
Else
strEquipNeed = txtEquipNeed.Value
strInsert = strInsert & "'" & strEquipNeed & "', "
End If
If IsNull(cboGate.Value) Then 'Gate Entrance
'cboGate.Value = ""
strInsert = strInsert & ", "
Else
lEntGate = cboGate.ListIndex
strGate = cboGate.Value
strInsert = strInsert & "'" & strGate & "', "
End If
If IsNull(txtPOC.Value) Then 'POC
strPOC = ""
strInsert = strInsert & ", "
Else
strPOC = txtPOC.Value
strInsert = strInsert & "'" & strPOC & "', "
End If
If IsNull(txtPOCPhoneNum.Value) Then 'POC Phone Number
strPOCPhoNum = ""
strInsert = strInsert & ", "
Else
strPOCPhoNum = txtPOCPhoneNum.Value
strInsert = strInsert & "'" & strPOCPhoNum & "', "
End If
If IsNull(txtFaxNum.Value) Then 'POC Fax Number
strPOCFax = ""
strInsert = strInsert & ", "
Else
strPOCFax = txtFaxNum.Value
strInsert = strInsert & "'" & strPOCFax & "', "
End If
If IsNull(txtCCPost.Value) Then 'Date Posted in Credit Card
dtPostCC = ""
strInsert = strInsert & ", "
Else
dtPostCC = txtCCPost.Value
strInsert = strInsert & "#" & dtPostCC & "#, "
End If
If IsNull(txtEvalRecved.Value) Then 'Evaluation Received
dtEvalRecevd = ""
strInsert = strInsert & ", "
Else
dtEvalRecevd = txtEvalRecved.Value
strInsert = strInsert & "#" & dtEvalRecevd & "#, "
End If
If IsNull(ckMatRetNSTC) Then 'Material sent to NSTC
ckNSTC = False
strInsert = strInsert & "False, "
Else
ckNSTC = ckMatRetNSTC.Value
strInsert = strInsert & "True, "
End If
If IsNull(ckCopySPLC.Value) Then 'Copy received for the SPLC
ckSPLC = False
strInsert = strInsert & "False, "
Else
ckSPLC = ckCopySPLC.Value
strInsert = strInsert & "True, "
End If
If IsNull(txtFinMail.Value) Then 'Mailed to Finance
dtMailedFinance = ""
strInsert = strInsert & ", "
Else
dtMailedFinance = txtFinMail.Value
strInsert = strInsert & "#" & dtMailedFinance & "#, "
End If
If IsNull(txtDteTVView.Value) Then 'Date TV View
dtTVView = ""
strInsert = strInsert & ", "
Else
dtTVView = txtDteTVView.Value
strInsert = strInsert & "#" & dtTVView & "#, "
End If
If IsNull(txtCan.Value) Then 'Cancel
ckCan = False
strInsert = strInsert & "False, "
Else
ckCan = txtCan.Value
strInsert = strInsert & "True, "
End If
If IsNull(ckPostP.Value) Then 'Postponed
ckPP = False
strInsert = strInsert & "False, "
Else
ckPP = ckPostP.Value
strInsert = strInsert & "True, "
End If
If IsNull(txtStarttime) Then 'Start time
MsgBox "Please enter the start time"
strInsert = ""
Exit Sub
Else
tiStrtTim = txtStarttime.Value
strInsert = strInsert & "#" & tiStrtTim & "#, "
End If
If IsNull(txtEndtime) Then 'End Time
MsgBox "Please enter the end time"
strInsert = ""
Exit Sub
Else
tiEndTim = txtEndtime.Value
strInsert = strInsert & "#" & tiEndTim & "#, "
End If
If IsNull(txtRemarks) Then 'Remarks
strRem = ""
strInsert = strInsert & ", "
Else
strRem = txtRemarks.Value
strInsert = strInsert & "'" & strRem & "', "
End If
If IsNull(cboCat.Value) Then 'Category
MsgBox "Please select a category"
strInsert = ""
Exit Sub
Else
lCat = cboCat.ListIndex
strCat = cboCat.Value
strInsert = strInsert & "'" & strCat & "', "
End If
If IsNull(txtTargetaud) Then 'Target Audience
strTarAud = ""
strInsert = strInsert & ", "
Else
strTarAud = "'" & txtTargetaud.Value & "', "
strInsert = strInsert & strTarAud
End If
If IsNull(ckReS.Value) Then 'Reschedule
ckReSch = False
strInsert = strInsert & "False, "
Else
ckReSch = ckReS.Value
strInsert = strInsert & "True, "
End If
If IsNull(txtCN.Value) Then 'Course Number
MsgBox "Please enter the course number"
strInsert = ""
Exit Sub
Else
strCrsNum = txtCN.Value
strInsert = strInsert & "'" & strCrsNum & "', "
End If
If IsNull(lstCoursetype.Value) Then 'Course Type
MsgBox "Please select a course type"
strInsert = ""
Exit Sub
Else
lCsetype = lstCoursetype.ListIndex
strCrsTyp = lstCoursetype.Value
strInsert = strInsert & "'" & strCrsTyp & "', "
End If
If IsNull(txtInstructor.Value) Then 'Instructor
strInstruct = ""
strInsert = strInsert & ", "
Else
strInstruct = txtInstructor.Value
strInsert = strInsert & "'" & strInstruct & "', "
End If
If IsNull(txtSCOFFID.Value) Then 'Schedule ID
iSchOffID = ""
strInsert = strInsert & ", "
Else
iSchOffID = txtSCOFFID.Value
strInsert = strInsert & iSchOffID & ", "
End If
If IsNull(ckSiteMap.Value) Then 'Site Map
ckSitMap = False
strInsert = strInsert & "False, "
Else
ckSitMap = ckSiteMap.Value
strInsert = strInsert & "True, "
End If
If IsNull(ckbxSR.Value) Then 'Self Registration
ckSelReg = False
strInsert = strInsert & "False)"
Else
ckSelReg = ckbxSR.Value
strInsert = strInsert & "True)"
End If

Am I missing anything? A date is not always present. Should I use null
instead of ''?
 
The easiest method of debugging an SQL string is to use a MsgBox or Debug.
Print to the immediate window and see what it looks like. Usually you can
spot the error.
 
I did that, but I can't find any thing. I was wondering did my error have
something to do with not placing a date in some of the values. Do I suppose
to put null when a date is not present?
The easiest method of debugging an SQL string is to use a MsgBox or Debug.
Print to the immediate window and see what it looks like. Usually you can
spot the error.
I am getting an error with my insert statement. The code is very long but
here it is.....
Click to expand...
[quoted text clipped - 353 lines]
Am I missing anything? A date is not always present. Should I use null
instead of ''?
Click to expand...
Click to expand...
 
You never did say what the error is!
I did that, but I can't find any thing. I was wondering did my error have
something to do with not placing a date in some of the values. Do I suppose
to put null when a date is not present?
The easiest method of debugging an SQL string is to use a MsgBox or Debug.
Print to the immediate window and see what it looks like. Usually you can
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
dst393 said:
I am getting an error with my insert statement. The code is very
long but here it is..... [code snipped]
Am I missing anything? A date is not always present. Should I use
null instead of ''?
Click to expand...

Without studying it too hard, I think the answer is yes -- or, rather,
you should use the Null literal. You may find the function below handy,
to relieve you of all those repetitive "If IsNull(<control value>) Then
.... Else ..." blocks:

'----- start of code -----
Public Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace( _
ArgValue, """", """""", , , vbBinaryCompare _
) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function

'----- end of code -----

With the function stored in a standard module, you can write your code
like this:

strInsert = strInsert & _
fncSQLLiteral(txtDteLocRes.Value, dbDate) & ", " & _
fncSQLLiteral(txtDteInstructClred.Value, dbDate) & ", " & _
fncSQLLiteral(txtMTBPost.Value, dbDate) & ", " & _
fncSQLLiteral(txtPostTCRS.Value, dbDate) & ", " & _
fncSQLLiteral(txtPostSAT.Value, dbDate) & ", " & _
fncSQLLiteral(txtDteSWRVB.Value, dbDate) & ", " & _
fncSQLLiteral(Val(MaxCap_lbl.Caption), dbLong) & ", " & _
fncSQLLiteral(txtCooProDte.Value, dbDate) & ", " & _
fncSQLLiteral(txtClsOut.Value, dbDate) & ", " & _
fncSQLLiteral(txtInvRecved.Value, dbDate) & ", " & _
fncSQLLiteral(txtEvalTechMon.Value, dbDate) & ", " & _
fncSQLLiteral(txtDCNCon.Value, dbDate) & ", "

strInsert = strInsert & _
fncSQLLiteral(txtVendor.Value, dbText) & ", " & _
fncSQLLiteral(txtVenAddr.Value, dbText) & ", " & _
fncSQLLiteral(txtVBRem.Value, dbText) & ", " & _
fncSQLLiteral(Val(MaxCap_lbl.Caption), dbLong) & ", " & _
fncSQLLiteral(txtCost.Value, dbText) & ", " & _
fncSQLLiteral(txtEquipNeed.Value, dbText) & ", "

' ... and so on ...

The function takes care of delimiting the values appropriately, and
substitutes the literal Null where appropriate.
 
Thanks for the reply, but I it might have something to do with my table. In
my table in one of the columns, there is a drop down menu. In the the menu,
the data is not the data that belongs in that column. It looks likes the
index number instead of the locations. How do I delete filters from the
table? I am still getting the insert into error (error 3134).

Dirk said:
I am getting an error with my insert statement. The code is very
long but here it is..... [code snipped]
Am I missing anything? A date is not always present. Should I use
null instead of ''?
Click to expand...

Without studying it too hard, I think the answer is yes -- or, rather,
you should use the Null literal. You may find the function below handy,
to relieve you of all those repetitive "If IsNull(<control value>) Then
... Else ..." blocks:

'----- start of code -----
Public Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace( _
ArgValue, """", """""", , , vbBinaryCompare _
) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function

'----- end of code -----

With the function stored in a standard module, you can write your code
like this:

strInsert = strInsert & _
fncSQLLiteral(txtDteLocRes.Value, dbDate) & ", " & _
fncSQLLiteral(txtDteInstructClred.Value, dbDate) & ", " & _
fncSQLLiteral(txtMTBPost.Value, dbDate) & ", " & _
fncSQLLiteral(txtPostTCRS.Value, dbDate) & ", " & _
fncSQLLiteral(txtPostSAT.Value, dbDate) & ", " & _
fncSQLLiteral(txtDteSWRVB.Value, dbDate) & ", " & _
fncSQLLiteral(Val(MaxCap_lbl.Caption), dbLong) & ", " & _
fncSQLLiteral(txtCooProDte.Value, dbDate) & ", " & _
fncSQLLiteral(txtClsOut.Value, dbDate) & ", " & _
fncSQLLiteral(txtInvRecved.Value, dbDate) & ", " & _
fncSQLLiteral(txtEvalTechMon.Value, dbDate) & ", " & _
fncSQLLiteral(txtDCNCon.Value, dbDate) & ", "

strInsert = strInsert & _
fncSQLLiteral(txtVendor.Value, dbText) & ", " & _
fncSQLLiteral(txtVenAddr.Value, dbText) & ", " & _
fncSQLLiteral(txtVBRem.Value, dbText) & ", " & _
fncSQLLiteral(Val(MaxCap_lbl.Caption), dbLong) & ", " & _
fncSQLLiteral(txtCost.Value, dbText) & ", " & _
fncSQLLiteral(txtEquipNeed.Value, dbText) & ", "

' ... and so on ...

The function takes care of delimiting the values appropriately, and
substitutes the literal Null where appropriate.
Click to expand...
 
dst393 via AccessMonster.com said:
Thanks for the reply, but I it might have something to do with my
table. In my table in one of the columns, there is a drop down menu.
In the the menu, the data is not the data that belongs in that
column. It looks likes the index number instead of the locations.
How do I delete filters from the table? I am still getting the
insert into error (error 3134).
Click to expand...

You have a lookup field in your table, and that's definitely something
you have to deal with. However, I don't believe that is the source of
your 3134 error ("Syntax error in INSERT INTO statement"). Your lookup
field is likely to give you a type mismatch error when you execute the
INSERT, but you are not yet getting that far.

The code you posted will *definitely* give you the 3134 error, because
it results in a VALUES clause like this:

VALUES(123, , 'ABC', , #8/21/2006#)

The missing fields represented by consecutive commas are invalid syntax.
Instead, the clause must look like this:

VALUES(123, Null, 'ABC', Null, #8/21/2006#)

That's what the function I posted is designed to do: put the proper
delimiters around a value, and substitute the "Null" literal when there
is no value.

Dealing with the lookup fields is a separate issue. Most professional
developers don't like lookup fields, because of just this sort of
problem -- what is stored is not what is displayed. It has nothing to
do with filters, unless I'm completely misunderstanding what you've
posted. With lookup fields, you have to make sure that the value you
actually insert in the table is what the field is *storing*, not
necessarily what it is displaying. I don't know enough about your
database application to give you more explicit advice than that. Maybe
after you've got past the 3134 error, and start getting a type mismatch
error instead <g>, you can make a new post on the subject.
 
That Worked!!!! Thank you so much! As far as the lookup column, I can't
stand that myself. I am finishing what someone started and they didn't know
any vba. I am kind new to access myself. How do I remove the lookup column?
 
dst393 via AccessMonster.com said:
That Worked!!!! Thank you so much! As far as the lookup column, I
can't stand that myself. I am finishing what someone started and
they didn't know any vba. I am kind new to access myself. How do I
remove the lookup column?
Click to expand...

Open the table in design view, click on the field in question, go down
to the tabbed property sheet and click on the Lookup tab, and change the
Display Control property from "Combo Box" to "Text Box".
 
Thanks!!!!!

Dirk said:
Open the table in design view, click on the field in question, go down
to the tabbed property sheet and click on the Lookup tab, and change the
Display Control property from "Combo Box" to "Text Box".
Click to expand...
 
Back
Top