doCmd.RunSQL Help

[JB]

Upon button click, I need to set the value of a control
on the current form to the max of field within a table
plus one.

I've tried the following but keep getting errors. (BTW-
the query actually works fine when run separately.) Any
help?

Me!housingInvoiceNbr = DoCmd.RunSQL("SELECT max
(housingInvoiceNbr)+1 as myInvoice from tblHouseStay")

Thanks.
JB

 

[Dirk Goldgar]

Upon button click, I need to set the value of a control
on the current form to the max of field within a table
plus one.

I've tried the following but keep getting errors. (BTW-
the query actually works fine when run separately.) Any
help?

Me!housingInvoiceNbr = DoCmd.RunSQL("SELECT max
(housingInvoiceNbr)+1 as myInvoice from tblHouseStay")

RunSQL only works for action queries that don't return a value. You can
extract the value you want, though, by using the DMax() function:

Me!housingInvoiceNbr = _
DMax("housingInvoiceNbr", "tblHouseStay") + 1

In case there is no record in the table, yet, you may want to wrap that
in an Nz() function expression:

Me!housingInvoiceNbr = _
Nz(DMax("housingInvoiceNbr", "tblHouseStay"), 0) + 1