Runtime Error 3024 Error

[PatAccess]

Hi Guys,
I am trying to create a form with a button that will run a SQL query based on designated field, but I get error code Runtime Error 3024. When I went to check on it, it is telling me to add the dbSQLPassThrough option to the OpenRecordset method, but again I have no idea what that is about. Can someone please help me?

This is the code I have
Private Sub cmdUpdate_Click()
Dim strUpdate As String
strUpdate = "UPDATE me.txttblName SET txtField1 = me.txtValue1.value WHERE txtConditionField = me.txtConditionValue "
DoCmd.RunSQL strUpdate
End Sub

Thank you,

 

[arnelgp]

trUpdate = "UPDATE me.txttblName SET txtField1 = '" & me.txtValue1.value & "' WHERE txtConditionField = '" & me.txtConditionValue & "'"

 

[isladogs]

Add the line debug.print strUpdate before the DoCmd line.
I think you will see the problem

 

[PatAccess]

Hi isladogs, I did and I am getting:
UPDATE me.txttblName.value SET txtField1.value = 'True' WHERE txtConditionField
= '160003938'

Hi arnelgp, I wrapped my field earlier and was still getting the error message. I tried again am still getting the same

 

[isladogs]

So what have you learned from the debug line?

 

[arnelgp]

because txtField1 is Boolean (yes/no field) and the other is numeric:

rUpdate = "UPDATE [" & me.txttblName & "] SET txtField1 = " & me.txtValue1.value & " WHERE txtConditionField = " & me.txtConditionValue

 

[Gasman]

Can you really have ?

UPDATE me.txttblName

 

[PatAccess]

isladogs: From the debug line, it seems it is not reading the value entered. Now it is giving me error 3078 - The Microsoft Access database engine cannot find the input table or query strTblName…" - It is reading some of my values entered
I changed the code a little:

Private Sub cmdUpdate_Click()
Dim strUpdate As String
Dim strTblName As String
Dim strField1 As String

strTblName = Me.txttblName.Value
strField1 = Me.txtField1
strUpdate = "UPDATE strTblName SET srtField1 = " & Me.txtValue1.Value & " WHERE txtConditionField = " & Me.txtConditionValue & ""
Debug.Print strUpdate
DoCmd.RunSQL strUpdate
End Sub

 

[Gasman]

You need to concatenate actual text with your variables.
Something along the lines of

strUpdate = "UPDATE " & strTblName & " SET " & strField1  .... etc

Then debug.Print strUpdate to see what is created.
Only then run the Update, for now comment it out.

HTH

 

[Gasman]

Can you really have ?

UPDATE me.txttblName

FWIW

UPDATE strTblName

does exactly the same thing.

 

[PatAccess]

Thank you Guy! It worked with concatenation and I had some type mismatched in there as well that I fixed. The final code is:
Private Sub cmdUpdate_Click()
Dim strUpdate As String
Dim strTblName As String
Dim strField1 As String

strTblName = Me.txttblName.Value
strField1 = Me.txtField1
strUpdate = "UPDATE " & strTblName & " SET " & strField1 & " = " & Me.txtValue1.Value & " WHERE " & txtConditionField & " = '" & Me.txtConditionValue.Value & "'"
Debug.Print strUpdate
DoCmd.RunSQL strUpdate
End Sub

You guys rock.
Thanks a lot

 

[arnelgp]

i thought you would say, "thanks Isladog", there are three of us here!

 

[Gasman]

i thought you would say, "thanks Isladog", there are three of us here!

I think that was meant to be 'Thank you Guys'?

 

[isladogs]

So it now works? If so, excellent and we were ALL glad to help

Just as a final check what does the debug line now show?

 

[PatAccess]

Now it shows:
UPDATE Tbl_CorporateLicDpt SET OnlineRenewal = True WHERE LicNum = '160003938'
Meaning it is now reading everything propertly

 

[PatAccess]

Yes Sorry...It was meant as Thank you Guys!

 

[isladogs]

Is LicNum really a text field despite its name and showing a number value?