Using Variables in an update query

[David]

I have 2 variables that I got out of the dmax function in vb access.

strfoundinvnum = DMax("INVOICENUMBER", "INVOICES")
strfoundcust = DMax("customercode", "invoicetemptable")

strfoundinvnum
strfoundcust

I need to do a query that searches one of my tables for all fields where the
customer matches strfoundcust then places the strfoundcust value in the
invoicenumber field of the same record. What would be the best and simplest
way to accomplish this?

 

[David]

Can I use a

DoCmd.RunSQL

and place my sql statement with the variables in it?

 

[kingston via AccessMonster.com]

Create an SQL string in VB:
SQLstring = "UPDATE

SET

.[InvoiceNumber] = '" & strfoundcust &
"' WHERE

.[customercode] = '" & strfoundcust & "';"

Then run the SQL:
DoCmd.RunSQL SQLstring

Obviously, you need to calculate strfoundcust prior to creating the SQL
string. I'm not sure how strfoundinvnum plays into any of this from your
description.

 

[David]

Thanks Kingston. I had a feeling i could use variables in the sql string.
I got it to work using the expression builder and the dmax in there. Here
is the code I came up with.

UPDATE OrderEntry SET OrderEntry.InvoiceNumber =
DMax("INVOICENUMBER","INVOICES"), OrderEntry.Invoiced = Yes
WHERE (((OrderEntry.CUSTOMERCODE)=DMax("customercode","invoicetemptable")));

It works perfect. Thanks for the help!

Create an SQL string in VB:
SQLstring = "UPDATE

SET

.[InvoiceNumber] = '" &
strfoundcust &
"' WHERE

.[customercode] = '" & strfoundcust & "';"

Then run the SQL:
DoCmd.RunSQL SQLstring

Obviously, you need to calculate strfoundcust prior to creating the SQL
string. I'm not sure how strfoundinvnum plays into any of this from your
description.

I have 2 variables that I got out of the dmax function in vb access.

strfoundinvnum = DMax("INVOICENUMBER", "INVOICES")
strfoundcust = DMax("customercode", "invoicetemptable")

strfoundinvnum
strfoundcust

I need to do a query that searches one of my tables for all fields where
the
customer matches strfoundcust then places the strfoundcust value in the
invoicenumber field of the same record. What would be the best and
simplest
way to accomplish this?