A simple answer please...

[stillwillyboy]

I have looked at so many DMax, SQL and recordset articles that my brain is oozing from my ears. It seems that for every programmer, there is a different method to achieve the same objective. The following example returns the highest value from the invoicenumber field. The domain is the ar--InvoiceNumberList table.

Sub IncrementingInvoiceNumbers()
Dim curY As Integer
curY = DMax("[invoicenumber]", "[ar--InvoiceNumberList]") + 1
MsgBox curY
End Sub

The above works. It adds "1" to the current highest number in the table "ar--InvoiceNumberList". It shows in the Msgbox the number "5" if the highest number in the table is "4". OK.

I want the new highest number, "5" in this case, to be added to the "ar--InvoiceNumberList" table. Then when I run it again, "6", then "7", you get the picture.

A simple line or two of code would be very greatly appreciated.

 

[jrbarnett]

What you need to do is to add a record containing that number to the table. Something like

DoCmd.RunSQL "Insert into [ar--InvoiceNumberList] ([invoicenumber]) values (" & curYY & ")"

will do the trick, provided that all the table constraints are met (ie all required fields are filled in etc).

John

 

[stillwillyboy]

It's not working. I get the message that I am about to append 1 record.... But the number is not being added to the table.

There is only one field in the table
"ar--InvoiceNumberList", the invoicenumber field. It is the PK. Plus, how do I keep the append msg from appearing. I need this for assigning invoice numbers to new invoices.

Dim curY As Integer
curY = DMax("[invoicenumber]", "[ar--InvoiceNumberList]") + 1
DoCmd.RunSQL "Insert into [ar--InvoiceNumberList]" _
& "([invoicenumber]) values (" & curY & ")"
End Sub

 

[stillwillyboy]

John,

It's working. I didn't realize that I had to close the table and reopen it to see the change.

Thanks.

 

[jrbarnett]

You shouldn't need to close and reopen the table to see the change, pressing F5 to refresh it should also do the trick.

John

 

[scriverb]

There is a very similiar thread in the ACCESS Forms forum that deals with this same type of issue. An answer was provided that can be used here. By inserting an expression in the Default property of the field on a form the next obvious value can be provided whenever a new record is created for the table.

thread702-827975

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]