SQL statement w/in VBA 1

[MsMope]

Good Morning,
The following is a transcript from the Access Forum I am hoping someone in this forum will be able to pinpoint the problem with the statement. The SQL statment is not updating the field.


MsMope (IS/IT--Manageme) Sep 16, 2004
Alright, here is the last mod to this, I found a module that gives me the current ID, works great, but it is still not updating:

strSQL = "UPDATE user_tblJobProp" & " " & _
                  "SET user_tblJobProp.[Delivery_Charge]= '1000'" & " " & _
                  "WHERE user_tblJobProp.[ID]= is_modoutline_getcurrentid();"

DRIVING ME BONKERS

 

[MsMope]

This is all I have

Private Sub frmDelivery_AfterUpdate()
Dim strSQL As String
Dim strJobID As String

strSQL = "UPDATE user_tblJobProp" & " " & _
"SET user_tblJobProp.[Delivery_Charge]= 1000" & " " & _
"WHERE user_tblJobProp.[ID]= '" & is_modoutline_getcurrentid() & "';"
Debug.Print strSQL

for the event

Yes, the query runs as expected in the db, the field updates.

 

[cLFlaVA]

well, you're telling me that you create a SQL statement, but never pass it to a connection to actually query the database?!

You need to do something with the SQL, you can't just create the statement.

Do you have an ADORecordset opened, or something similar? What's holding your DB connection? Your recordset connection?

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[MsMope]

Ok I open the db connection:

 Dim db as Database
Dim rs as RecordSet

Set db as CurrentDb
strSQL = "UPDATE user_tblJobProp" & " " & _
                    "SET user_tblJobProp.[Delivery_Charge]= 1000" & " " & _
                    "WHERE user_tblJobProp.[ID]= '" & is_modoutline_getcurrentid() & "';"
Set rs = db.OpenRecordset(strSQL)

I recieve the following error with the above code:
Run-time error 3219: Invalid Operation

 

[MsMope]

I have resolved issues related to this, thank-you to everyone who contributed their time