Incrementing Invoice numbers. Almost there.... 1

[stillwillyboy]

I have a Report whose Record Source is “qry-OrdersQuery”. This puts all the needed info onto the report (invoice) that I need except for the Invoice number.

I have a text box on the report (invoice) whose Control Source is “InvoiceNumber”. Invoice Number is a field in my Orders table that is linked in the
qry-OrdersQuery to the tables OrdersDetail, Parts, Branches, and Customers.

When I add to the “qry-OrdersQuery” query, the table named “InvoiceNumberList” that I am using to keep track of the used invoice numbers, the only orders that appear when I run qry-OrdersQuery are those that have a corresponding number in the InvoiceNumberList table, which I believe, is correct. But, even though the Show box is checked for InvoiceNumber, the invoice numbers do not appear on the datasheet or the report.

So my question are: how do I use the following code? Where do I place it to have it work? I know that it goes into the VBA module, but how do I call it? Does it belong to the table “InvoiceNumberList” or somewhere in “qry_OrdersQuery”? How do I get the Invoice numbers to appear in both the datasheet and the report?

Sub InvoiceNumber ()
If isnull(Me!txtInvoiceNo.Value) then
Me!txtInvoiceNo.Value=DMax("field",Table")+1
End if
End sub

The above code was provided by Roy-Vidar. I added the Sub and End Sub lines. Thanks Roy.

TIA. Bill

 

[scriverb]

Your Orders Table needs to be updated with the invoice # just prior to running the report. This Invoice number can be generated using similar code to that which RoyVidar provided but it needs to be used in an Update Query.

Create an update query that selects the Orders which you want to print the invoice for. Then use the DMax function provided by Roy above to update your Invoice# field. After that runs you can then run your report(invoices) and the bound control Invoice# will be populated with the correct Invoice#.

Post back if you need more discussion about this.

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

 

[stillwillyboy]

Bob, Thanks for all the help. Here is the code that I am using, but I do not know where to put it. Does it go in the Orders table? It seems like it should. The Update query? Given that it is VBA, I think it should go in the VBA module, but how do I call it?

Sub InvoiceNumber ()
If isnull(Me!InvoiceNumber.Value) then
Me!InvoiceNumber.Value=DMax(“InvoiceNumber” ,”ar--InvoiceNumberList")+1
End if
End sub

 

[scriverb]

Create a new query and put the following code so that the null Invoice #'s get updated with the correct numbers:

UPDATE [Orders Table] as A SET A.[InvoiceNumber] = DMax(“InvoiceNumber” ,”ar--InvoiceNumberList")+1 
WHERE (A.[InvoiceNumber]) Is Null;

Now run this query just before you run your report. The records with a InvoiceNumber = Null will be updated with a sequential invoice number.

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

 

[Pampers]

Hi scriverb,
A thread from a while ago, but coping with the same problem. Try to get an incremental InvoiceNo in update query if InvoiceNo Is Null. I used your code to make the query. It runs, but it is not incremental. Say if the DMAX = 1, all the other emtpy InvoiceNo's get the value 2. Where does it go wrong? Here is my code:

UPDATE tblBolImport AS A SET A.InvoiceNo = DMax("InvoiceNo","tblBolImport")+1
WHERE (A.InvoiceNo) Is Null;

Pampers.

You're never too young to learn

 

[scriverb]

It seems that the DMax function only executes against the original recordset within this query so it will not give you a newly updated Maximum InvoiceNo each time. So, we will have to use a subroutine to loop through the Null InvoiceNo's and update them individually.

This code should work for you. The SQL I provided does not sort the Null Invoices into any order but you can add an Order By to the SQL if there is a transaction date or some other field that you may want to use to control their order. That update will not affect the code provided.

Just make a subroutine call of this code and it will update the Null InvoiceNo's appropriately.

Sub InvoiceNumber()
Dim db As DAO.Database, rs As DAO.Recordset, vSQL As String, vInvNo As Long
Set db = CurrentDb
vSQL = "Select A.* from tblInvoices as A WHERE (((A.InvoiceNo) Is Null))"
Set rs = db.OpenRecordset(vSQL, dbOpenDynaset)
vInvNo = DMax("InvoiceNo", "tblInvoices") + 1
rs.MoveFirst
Do
    rs.Edit
    rs.InvoiceNo = vInvNo
    rs.Update
    rs.MoveNext
    vInvNo = vInvNo + 1
Loop Until rs.EOF
rs.Close
db.Close
End Sub

Good luck

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Pampers]

tnx Scriverb,

It seems that the DMax function only executes against the original recordset within this query so it will not give you a newly updated Maximum InvoiceNo each time.

Yep.



I'm gonna try your new code, and let you know how things worked out.
For now I created a frm that assigns the numbers for the InvoiceNo Is Null (selected by a selectquery). It loops through the records (acNext) until a last number is asssigend to the empty InvoiceNo. The form opens when the user wants to printpreview the invoices. Looks like it works...


Pampers.

You're never too young to learn

 

[scriverb]

As I don't know your process, normally the InvoiceNo gets assigned as the record is created. That is the time to do the DMax function and assign the value +1 to the InvoiceNo field. You just must be careful that two people are not entering a record at exactly the same time and try to update the same InvoiceNo. There are ways to handle that also.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Pampers]

Tnx Scriverb,
I'm aware of that. That was the reason why I started looking for alternative ways to set the InvoiceNo's.

First I created a new InvoiceNo when a NewRecord was created. But since more people use the app for entering data, this created duplicate invoiceNo. Now I want to run the assigning of the InvoiceNo's on Opening of the rprtInvoice - since this is only done by one person (the supervisor) while the other userapps are closed. In this way the InvoiceNo's are unique and incremental.

There are ways to handle that also.

If you could point me to threats or whatever... that would be great.


Pampers.

You're never too young to learn

 

[scriverb]

One of the easiest ways I have found to do this in a multi-user environment is this. Create a Unique Index(no dups) for the InvoiceNo field so that as you then monitor the ERR value in your error trapping code behind a SAVE record button. Here is some sample code that will update the record when the user is done and ready to save with the correct Next largest invoice number even if there are multiply users creating records. The field would stay empty until the save button is clicked then be updated with the correct number. If someone has just updated to the same number it will increment the number by 1 until it finds the next vacant invoice number.

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click
    DoCmd.RunCommand acCmdSaveRecord
Exit_SaveButton_Click:
    Exit Sub
Err_SaveButton_Click:
    If Err = 3022 Then
        Me.InvoiceNo = Me.InvoiceNo + 1
        Resume
    Else
        Resume Exit_SaveButton_Click
    End If
End Sub

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Sorry but you also may want to display err messages or perform other code when it is not ERR - 3022. You will have to add that code. Just a reminder.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Pampers]

tnx bob,
I'm gonna try to put the code to work. I'll get back to you with the result.


Pampers.

You're never too young to learn