Syntax Error Runtime 3144

[proballin]

MySQL = "UPDATE MkFrmFunc_Table SET MkFrmFunc_Table.Percentage = '" & Me.Percentage_Text1 & "', MkFrmFunc_Table.Volume = " & (MSize * Me.Percentage_Text1) & ", MkFrmFunc_Table.Ingredient = '" & Ingredient_Combo1 & "', WHERE ((MkFrmFunc_Table.Region = '" & Me.Region_Combo.Column(1) & "') And (MkFrmFunc_Table.SubRegion = '" & Me.SubRegion_Combo.Column(0, i) & "') And (MkFrmFunc_Table.Segment = '" & Me.Segment_Combo.Column(1) & "') And (MkFrmFunc_Table.Sector= '" & Me.Sector_Combo.Column(1) & "') AND (MkFrmFunc_Table.Product= '" & Me.Product_Combo & "'))"

With the line of code above I am getting an error. When I step through I am every variable is filled in with the correct number or word. I believe the problem is specifically here:

MkFrmFunc_Table.Volume = " & (MSize * Me.Percentage_Text1) & "

I get the right total but it gives me the syntax error 3144. MSize is a Double variable that I declared in the subroutine and Me.Percentage_Text1 comes from a form and is type Double as well. How specifically do I need to punctuate that line to get the error off?

 

[rolaaus]

can you put a breakpoint in the next line of code, and do a debug of mysql variable?

If you get an error on this line and it doesn't proceed any further, then there is a syntax problem somewhere with your &'s and "'s or something.

Post your results here and we can see what is happening.

Btw, something that I sometimes does when there are a lot of interjections is do each one on their seperate line, like this

MySQL = "UPDATE MkFrmFunc_Table SET MkFrmFunc_Table.Percentage = '" & Me.Percentage_Text1 & _
      "', MkFrmFunc_Table.Volume = " & (MSize * Me.Percentage_Text1) & _
      ", MkFrmFunc_Table.Ingredient = '" & Ingredient_Combo1 & _ '...

 

[proballin]

K...I put the break in and it stopped on the following line:

CurrentDb.Execute MySQL, dbFailOnError

dbFailOnError is equal to 128. I figured that the error is with my &'s or "'s as you've pointed out. I also will reorganize my code so that it's easier on the eyes

 

[rolaaus]

I am not sure if you would get a run-time error for a syntax problem with & and the like, but that is always my first suspicion, though in this particular case, I may be wrong.

I did however copy and paste what you originally posted into notepad myself and cleaned it up to see if I could spot anything out of the ordinary and that there might be a problem with this portion
Me.SubRegion_Combo.Column(0, i)

all of the other Column references are single parameters, like so Me.Sector_Combo.Column(1)

 

[proballin]

Me.SubRegion_Combo.Column(0,i) works actually. I should have told you before that this is taking place inside of a a for loop. SubRegion is the only variable changing inside the loop. That is why I wanted to get the correct volume for each subregion. Its something else, most likely the syntax.