VBA Insert Into SQL help (1 Viewer)

[aron.ridgway]

I have the following SQL string that i want to run, but i get an application or object defined error.

Can anyone notice a mistake with my string?

mySQL2 = "INSERT INTO [tblReceive] (OrderDetailFK, Qty, DateReceived, UserFK, ReceiveFK) VALUES (" & _
Me.OrderDetailFK & ", " & minusNum & ", " & "Now()" & ", " & Forms!frmReceive.Fields("UserFK") & ", " & Forms!frmReceive.Fields("ReceivePK") & ")"

 

[pr2-eugin]

Why Now() and why is this surrounded with double quotes?

mySQL2 = "INSERT INTO [tblReceive] (OrderDetailFK, Qty, DateReceived, UserFK, ReceiveFK) VALUES (" & _
                                    Me.OrderDetailFK & ", " & minusNum & ", [COLOR=Red][B]" & Date() & "[/B][/COLOR], " & Forms!frmReceive.Fields("UserFK") & ", " & _
                                    Forms!frmReceive.Fields("ReceivePK") & ")"

 

[aron.ridgway]

I have a simular SQL which uses now() and it works fine, i did try the update just incase but i still get the same error.

 

[pr2-eugin]

Try a Debug.Print and then copy the generated SQL into the Query window. It will help you identify the problem ! (Also post it here)

How to use the immediate window : http://www.baldyweb.com/ImmediateWindow.htm

 

[aron.ridgway]

I have tried the following, but as the code breaks at mySQL2 beofre the debug.print, i cant get it to display in the window??

mySQL2 = "INSERT INTO [tblReceive] (OrderDetailFK, Qty, DateReceived, UserFK, ReceiveFK) VALUES (" & _
Me.OrderDetailFK & ", " & minusNum & ", " & "Now()" & ", " & Forms!frmReceive.Fields("UserFK") & ", " & Forms!frmReceive.Fields("ReceivePK") & ")"
Debug.Print mySQL2

 

[pr2-eugin]

Any reason why you have not tried my code in Post#2?

 

[aron.ridgway]

i tried it but it produced the same error, i have an insert which uses now(), so i know that it works.

 

[pr2-eugin]

What would someone have to do, to make you just try it?

 

[aron.ridgway]

I have re tried see bellow, still getting the same error. I cant make it get to the debug.Print stage!

mySQL2 = "INSERT INTO [tblReceive] (OrderDetailFK, Qty, DateReceived, UserFK, ReceiveFK) VALUES (" & _
                                    Me.OrderDetailFK & ", " & minusNum & ", " & Date & ", " & Forms!frmReceive.Fields("UserFK") & ", " & Forms! _
                                    frmReceive.Fields("ReceivePK") & ")"
Debug.Print mySQL2

 

[pr2-eugin]

Where is this Form "frmReceive"?

 

[aron.ridgway]

frmReceive is the master form, the code is running in a subform on frmReceive (the Master), i have minusNum which is an integer variable.

could this be causing the error? The value im trying to insert it into is a number field.

minusNum = Me.txtQty.Value * -1

 

[pr2-eugin]

frmReceive is the master form, the code is running in a subform on frmReceive (the Master),

Well then why not use the Parent property to refer the controls? Like

mySQL2 = "INSERT INTO [tblReceive] (OrderDetailFK, Qty, DateReceived, UserFK, ReceiveFK) VALUES (" & _
                                    Me.OrderDetailFK & ", " & minusNum & ", " & [COLOR=Red][B]CDbl([/B][/COLOR]Now()[COLOR=Red][B])[/B][/COLOR] & ", " & [COLOR=Red][B]Me.Parent!UserFK[/B][/COLOR] & ", " & _
                                    [COLOR=Red][B]Me.Parent!ReceivePK[/B][/COLOR] & ")"

i have minusNum which is an integer variable.

could this be causing the error?

No ! Integers span from Negative to Positive. Range about: -32,768 and 32,767. So you are safe to use minus.

 

[aron.ridgway]

I have worked it out! It was a conflicting userFK in the query! i removed the extra FK and its working fine! thanks for the help!