added to statement and now it doesn't work
added to statement and now it doesn't work
I'm trying to find where the error is in a SQL statement, but can't seem to locate it. VBA is telling me "Compile error: Mehthod or data member not found" and highlights [RotationalServings7]= '" & Me.Text499 & "' but after looking at it over and over, it seems right to me. Also debug.print mysql2 doesn't give me the shred out showing what it's executing.
[RotationalServings7]= '" & Me.Text499 & "'
debug.print mysql2
side note; it worked just fine until I added all the 9 and 10 items. Looked through the form and all the information is correct, same with the table it's updating.
mysql2 = "UPDATE Thaw_Tags_FTI SET [RotationalItem1]= '" & Me.Combo445.Column(1) & "',[RotationalMeat1]= '" & Me.Text449 & "',[RotationalSS1]= '" & Me.Text452 & "',[RotationalServings1]= '" & Me.Text447 & "',[RotationalItem2]= '" & Me.Combo455.Column(1) & "'," _
& "[RotationalMeat2]= '" & Me.Text459 & "',[RotationalSS2]= '" & Me.Text461 & "',[RotationalServings2]= '" & Me.Text457 & "',[RotationalItem3]= '" & Me.Combo463.Column(1) & "',[RotationalMeat3]= '" & Me.Text467 & "',[RotationalSS3]= '" & Me.Text469 & "'," _
& "[RotationalServings3]= '" & Me.Text465 & "',[RotationalItem4]= '" & Me.Combo471.Column(1) & "',[RotationalMeat4]= '" & Me.Text475 & "',[RotationalSS4]= '" & Me.Text477 & "',[RotationalServings4]= '" & Me.Text473 & "',[RotationalItem5]= '" & Me.Combo479.Column(1) & "'," _
& "[RotationalMeat5]= '" & Me.Text483 & "',[RotationalSS5]= '" & Me.Text486 & "',[RotationalServings5]= '" & Me.Text481 & "',[RotationalItem6]= '" & Me.Combo489.Column(1) & "',[RotationalMeat6]= '" & Me.Text493 & "',[RotationalSS6]= '" & Me.Text495 & "',[RotationalServings6]= '" & Me.Text491 & "'," _
& "[RotationalItem7]= '" & Me.Combo497.Column(1) & "',[RotationalMeat7]= '" & Me.Text501 & "',[RotationalSS7]= '" & Me.Text503 & "',[RotationalServings7]= '" & Me.Text499 & "',[RotationalItem8]= '" & Me.Combo505.Column(1) & "',[RotationalMeat8]= '" & Me.Text509 & "',[RotationalSS8]= '" & Me.Text511 & "',[RotationalServings8]= '" & Me.Text507 & "'," _
& "[RotationalItem9]= '" & Me.Combo778.Column(1) & "',[RotationalMeat9]= '" & Me.Text789 & "',[RotationalSS9]= '" & Me.Text784 & "',[RotationalServings9]= '" & Me.Text780 & "',[RotationalItem10]= '" & Me.Combo787.Column(1) & "',[RotationalMeat10]= '" & Me.Text791 & "',[RotationalSS10]= '" & Me.Text793 & "',[RotationalServings10]= '" & Me.Text789 & "'" _
& "WHERE [ID]=Forms!Thaw_Report_FTI!Combo160"
" WHERE [ID]=" & Forms!Thaw_Report_FTI.Combo160
You have added `" _& " add the end of each line that doesn't make any sense. Removing it should fix your error. Try to format SQL in some tools (sqlformat.org) to see this type of errors.
– mx0
Sep 17 '18 at 21:42
@mx0, the
_ & are correct for constructing SQL string in VBA. The _ is line continuation character and the & is string concatenation.– June7
Sep 17 '18 at 21:55
_ &
_
&
Remove new added line and then check your query after adding each element. You probably have typo in one of the
Me.Text* elements.– mx0
Sep 17 '18 at 22:17
Me.Text*
The error you reported would happen if the form where you're building that string does not contain a control named
Text499– HansUp
Sep 18 '18 at 2:31
Text499
0
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy
Need a space in front of the WHERE so string won't run together when it compiles. Or put the space after the last apostrophe within the quote marks. Also, concatentate the criteria:
" WHERE [ID]=" & Forms!Thaw_Report_FTI.Combo160– June7
Sep 17 '18 at 21:36