Max of recordset (Recordset Newbie) 1

[buzzboychicago]

Below is some code that I want to use to programatically add data to a table. However, where it says "Insert Calculated Field Here" I want to find the max of the INVOICERESEND table. Not sure what the syntax would be of if I have to use some sort of SQL statement.



Private Sub Command2_Click()
Dim db As Database
Dim rst1 As Recordset
Dim newnumber As Long

Set db = CurrentDb
Set rst1 = db.OpenRecordset("invoiceresend")

rst1.AddNew
rst1!invoice = [INSERT CALCULATED VARIABLE HERE]
rst1.Update
rst1.Close
db.Close

End Sub

 

[PHV]

Have a look to the DMax function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jebry]

Hi!

You can use the DMax function:

rst1!invoice = DMax("invoice", "invoiceresend") + 1

I added the + 1 because it looks like some sort of invoice number so it shouldn't be repeated.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[buzzboychicago]

Thanks for your help jebry.

That worked.

Can I use a SQL string to asign data to variables or manipulate a recordset?

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886