Update query in VB

[tagteam]

I am trying to run the following code from a button in a continuous form. The button is inline with the record



Private Sub AssignInvoice_Click()
Dim dbs As Database
Dim qdf As QueryDef


Set dbs = CurrentDb
dbs.Execute "Update tbl_ImportedRepairs " _
& "SET tbl_ImportedRepairs.Invoice = 'me.[UpdateInvoice]'" _
& "WHERE tbl_ImportedRepairs.Batch = Me.Batch AND PaymentRespID=me.PaymentRespId;"

dbs.Close
End Sub





I get the error Run-Time error '3061' Too few parameters. Expected 2

I am trying to run an update query to update any item in the database that matches the Batch and PaymentRespId in the current record to be the value of UpdateInvoice which is a unbound text box in the row.

The Invoice Field is text field and the Batch and PaymentRespId fields are number

[ItsMe]

Maybe you can store your SQL statement in a string variable to help you troubleshoot.

Dim strSQL as string

strSQL = "Update tbl_ImportedRepairs " _
& "SET tbl_ImportedRepairs.Invoice = 'me.[UpdateInvoice]'" _
& "WHERE tbl_ImportedRepairs.Batch = Me.Batch AND PaymentRespID=me.PaymentRespId;"
debug.print strSQL


Then you can look at your string in the Immediate Window Ctrl+G

The problem you are having is that you are including Controls/Variables within your string, literally. You need to concatenate them. So you are missing a couple ampersands and maybe a quotation here and there.

[rivereridanus]

I'm not sure that that is going to give you what you want. I would typically do something like:

Dim newinvoice As String, batch As String, payid As String
newinvoice = Me.UpdateInvoice
batch = Me.batch
payid = Me.payid
DoCmd.RunSQL("UPDATE tbl_ImportedRepairs SET tbl_ImportedRepairs.Invoice = '" + newinvoice + "' WHERE tbl_ImportedRepairs.Batch = " + batch + " AND tbl_ImportedRepairs.PaymentRespID = " + payid)

[orange]

I agree with itsMe. You can see what Access renders from your strSQL with the Debug.Print strSQL
That way you can check your SQL syntax before actually running the query.

[tagteam]

When i run this one

Dim NewInvoice As String, TrinityBatch As String, payid As String
NewInvoice = Me.NewInvoice
batch = Me.TrinityBatch
payid = Me.PaymentRespID

DoCmd.RunSQL ("UPDATE tbl_ImportedRepairs SET tbl_ImportedRepairs.TrinityInvoice = '" + NewInvoice + "' WHERE tbl_ImportedRepairs.TrinityBatch = " + batch + " AND tbl_ImportedRepairs.PaymentRespID = " + payid)

I get Run-time error '13" Type Mismatch and the debug goes to line

DoCmd.RunSQL ("UPDATE tbl_ImportedRepairs SET tbl_ImportedRepairs.TrinityInvoice = '" + NewInvoice + "' WHERE tbl_ImportedRepairs.TrinityBatch = " + batch + " AND tbl_ImportedRepairs.PaymentRespID = " + payid)

I tried manually assigning the invoice
NewInvoice = 1500 or NewInvoice = "IN1500" but neither worked

[ItsMe]

I am not at a computer right now but...

Try concatenating with the ampersand rather than the plus sign. It looks like batch is a number type and SQL may be trying to do some math. Might have some issues with placement of your quotations too.

Employ the debug.print technique previously suggested.

[tagteam]

cool looks like the ampersand did the trick it is working now.
Thanks