Method or data member not found (1 Viewer)

[Gazzyy]

Keep getting "compile error: Method or data member not found" on this insert into statement. It is highlighting the "Me.txtSM3". The textbox exists and the reference is spelled correctly so I'm not sure where to go from here.

sqlStr = "INSERT INTO SavedRecords(Inspector, OrderNum, PCNumber, LogoPart, PhSpec, OrderName, TotalPieces, TotalIssues, GlueIssues, SeamIssues, PassFail, FPY, SizeIssues, ColorIssues, Comments, ProductIssues, SeamMsr1, SeamMsr2, SeamMsr3, SeamPull1, SeamPull2, SeamPull3, CutFibers, MissingCoating) " & _
" VALUES('" & Me.txtQI & ",'" & Me.txtJobnum & "','" & Me.txtpc & "','" & Me.txtLogopart & "','" & Me.txtPH & "','" & Me.txtjobdescription & "','" & Me.txtTotalpieces & "','" & Me.txtTotalissues & "','" & Me.txttotalglue & "','" & Me.txttotalseam & "','" & Me.txtPassfail & "','" & Me.txtFPY & "','" & Me.txttotalsize & "','" & Me.txtcolorissues & "','" & Me.txtComments & "','" & Me.txtproductissues & "','" & Me.txtSM1 & "','" & Me.txtSM2 & "','" & Me.txtSM3 & "','" & Me.SP1 & "','" & Me.txtSP2 & "','" & Me.txtSP3 & "','" & Me.txtcf & "','" & Me.txtmissingcoating & "')"

 

[isladogs]

Your post was moderated due to the attached file. Until you have 10 posts you need to zip any attachments.

I also added code tags (# button on toolbar) to make the code easier to read

 

[Gazzyy]

Is it good now?

 

[Gasman]

It appears to be something to do with your string.
If you put MSGBOX Me.txtSM3 before it, it does now complain?

Intelllisense finds it.

 

[Gazzyy]

I have had an issue in the past where it would throw a compile error, but it would end up being a completely different reference from what it was highlighting. I have triple checked all of my references to my form and table however I cannot find anything wrong. Could be just overlooking something simple. Do I need to post my entire code?

 

[Gasman]

No,
I've spotted one mistake but that does not make a difference

" VALUES('" & Me.txtQI & "[COLOR="Red"]'[/COLOR],'"

 

[The_Doc_Man]

Gasman's discovery of a missing apostrophe earlier in the string might be relevant. Is there a chance that either SM2 or SM3 might at that point contain an apostrophe as a natural data element or component?

 

[Gasman]

This is wrong as well

 Me.SP1

should be Me.txtSP1

When that is changed as well, it complains about txtSM2

 

[Gasman]

OK,

I have it at least able to compile. Note I changed sqlstr to strSQL

I tend to use bound forms as that is how Access works best I believe.
You are just creating work for yourself this way.

Anyway give it a go, and report back.
I also put Option Explicit at the top of the form module, and LResponse was not declared.?

Good luck

Private Sub Command164_Click()
Dim LResponse As Integer
Dim strDelim As String, strSQL1 As String, strSQL2 As String, strSQL As String

strDelim = "' , '"

If Me.txtJobnum = "" Then

MsgBox ("No job number. Your data was not saved.")

Exit Sub

End If

Dim sqlStr As String
'MsgBox Me.txtSM3
'sqlStr = "INSERT INTO SavedRecords(Inspector, OrderNum, PCNumber, LogoPart, PhSpec, OrderName, TotalPieces, TotalIssues, GlueIssues, SeamIssues, PassFail, FPY, SizeIssues, ColorIssues, Comments, ProductIssues, SeamMsr1, SeamMsr2, SeamMsr3, SeamPull1, SeamPull2, SeamPull3, CutFibers, MissingCoating) " _
'sqlStr = sqlStr & " VALUES('" & Me.txtQI & ",'" & Me.txtJobnum & "','" & Me.txtpc & "','" & Me.txtLogopart & "','" & Me.txtPH & "','" & Me.txtjobdescription & "','" & Me.txtTotalpieces & "','" & Me.txtTotalissues & "','" & Me.txttotalglue & "','" & Me.txttotalseam & "','" & Me.txtPassfail & "','" & Me.txtFPY & "','" & Me.txttotalsize & "','" & Me.txtcolorissues & "','" & Me.txtComments & "','" & Me.txtproductissues & "','" & Me.txtSM1 & "','" & Me.txtSM2 & "','"
'sqlStr = sqlStr & "'" & Me.txtSM3 & "','" & Me.SP1 & "','" & Me.txtSP2 & "','" & Me.txtSP3 & "','" & Me.txtcf & "','" & Me.txtmissingcoating & "')"
strSQL1 = "INSERT INTO SavedRecords(Inspector, OrderNum, PCNumber, LogoPart, PhSpec, OrderName, TotalPieces, TotalIssues, GlueIssues, SeamIssues, PassFail, FPY, SizeIssues, ColorIssues, Comments, ProductIssues, SeamMsr1, SeamMsr2, SeamMsr3, SeamPull1, SeamPull2, SeamPull3, CutFibers, MissingCoating) "
strSQL2 = " VALUES('" & Me.txtQI & strDelim & Me.txtJobnum & strDelim & Me.txtpc & strDelim & Me.txtLogopart & strDelim & Me.txtPH & strDelim & Me.txtjobdescription & strDelim & Me.txtTotalpieces & strDelim & Me.txtTotalissues & strDelim & Me.txttotalglue & strDelim & Me.txttotalseam & strDelim & Me.txtPassfail & strDelim & Me.txtFPY & strDelim & Me.txttotalsize & strDelim & Me.txtcolorissues & strDelim & Me.txtComments & strDelim & Me.txtproductissues & strDelim & Me.txtSM1 & strDelim & Me.txtSM2 & strDelim & Me.txtSM3 & strDelim & Me.txtSP1 & strDelim & Me.txtSP2 & strDelim & Me.txtSP3 & strDelim & Me.txtcf & strDelim & Me.txtmissingcoating & "')"

strSQL = strSQL1 & strSQL2
Debug.Print strSQL

CurrentDb.Execute strSQL

LResponse = MsgBox("Record saved sucessfully. Do you want to clear the form's contents?", vbYesNo, "Success!")

If LResponse = vbYes Then
Me.txtQI = ""
Me.txtJobnum = ""
Me.txtpc = ""
Me.txtLogopart = ""
Me.txtSM1 = ""
Me.txtSM2 = ""
Me.txtSM3 = ""
Me.txtSP1 = ""
Me.txtSP2 = ""
Me.txtSP3 = ""
Me.txtcolorissues = ""
Me.txttotalsize = ""
Me.txtproductissues = ""
Me.txtTotalpieces = ""
Me.txtTotalissues = ""
Me.txttotalglue = ""
Me.txttotalseam = ""
Me.txtcf = ""
Me.txtPHissue = ""
Me.txtmissingcoating = ""
Me.txtBack = ""
Me.txtPassfail = ""
Me.txtComments = ""
Me.txtPH = ""
Me.txtjobdescription = ""
Me.txtstockcode = ""
Me.txtProduct = ""


Else
Exit Sub

End If

End Sub

 

[Gazzyy]

Getting syntax error in query expression with the new code.

 

[Gasman]

debug print each of the sql strings, strSQL1 should be OK.

I take it you copied and pasted?

I cannot test the form as I do not have the Excel files

 

[Gasman]

OrderNum and FPY are numbers in the table, so remove the quotes from those variables that will populate those fields.
You do not appear to populate InspectDate? that variable would be enclosed with # character.

 

[Gazzyy]

Decided to go bound form route instead. Thanks all for the help!

 

[arnelgp]

wait... you forgot your bill!