Compile Error - need assistance please

[cstickman]

Hello Everyone!!! I am new to the forum. I came across it trying to find a solution to my problem. I have some code and it is giving me an compile error and I cannot find it for the life of me. I need someone else to look at it and tell me what I am missing. Below is the code

<code>
CurrentDb.Execute "INSERT INTO TravelerQA(defecttype, defecttopic, defectsubtopic, questiontype, requestor, questiondate, question, answer, answerdate, answeredby, approval) " & _
" VALUES('" & Me.cmbdefecttype & "','" & Me.cmbdefecttopic & "','" & _
Me.cmbdefectsubtopic & "','" & Me.cmbgeneraltype & "','" & _
Me.txtrequestedby & "','" & Me.questiondate & "','" & _
Me.txtquestion & "','" & Me.txtanswer & "','" & _
Me.txtanswerdate & "','" & Me.txtansweredby & "','" & _
Me.cmbapproved & "')"
</code>

I checked all the labels in the properties box under name and they all match. It errors out and hightlights Me.txtrequestedby and then that is all I get. I tried everything to get it to work and I have nada. Any help or suggestions would be greatly appreciated. Thanks

 

[RuralGuy]

Try removing most of the double quotes from the VALUES section and just leaving the single quotes. I suspect it is driving the Access parser nuts. You might try just inserting one field to get the syntax correct.

 

[Minty]

Put you SQL into a string before executing it. then debug.print the string before running the execute statement. Post the debug result here if you can't fathom it from what it shows !

 

[cstickman]

So what I did was I removed all the "Me." and it worked fine. Does anyone have an idea why that worked? Thanks

 

[smig]

Me. Refer to an object on a form or report, not data from table or query.

 

[cstickman]

So should I put them back in and try removing some of the quotes instead? Thanks

 

[JHB]

If you get the right result now why putting them back?
If you want to put in (in the table) the value from some controls on your form, then place a ME. in front.
But you need to be sure the name of them are really what you call them.
According to your post #1, I have doubt you really have a control with the name "txtrequestedby" on your form!

 

[spikepl]

You are trying to do too many things at the same time. The proper way of constructing and verifying SQL in VBA code is shown here: http://www.baldyweb.com/immediatewindow.htm

When you can't even construct the SQL string, then construct only one bit of it and make that work. Then add one more bit and so on.

And I agree with JHB - you don't have a textbox with that name,. People often confuse the name of the control with the Control Source. Just because a textbox is bound to a db field with the desired name does not necessarily mean that the name of the textbox is the same. Check that.

 

[cstickman]

Thanks everyone as I completely confused myself and to make life easier I just wrote a query and everything works perfectly now. Thanks everyone!!