Update fields in a query with the value from the latest available invoicenumber

I generate the latest available invoice number with a button on a workorder form.

It opens another form look at the value and copies it in the 3 fields in the workorder form.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Query_laatste factuur"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 Me.[factuurnummer] = Format(1 + Forms![Query_laatste factuur]![factuurnummer], "0000")
 Me.[Invoice] = DLookup("[Invoice]", "My Company Information") & Format(Me.[factuurnummer], "0000")
 Me.[Invoice date] = Int(Now())
Me.[Invoice].Requery

 DoCmd.Close acForm, stDocName

Open in new window


Now I'm wondering if I can generate with a button a query called "WorkordersInvoiceQuery" With the same fields in it.
I have filtered the query CustomerID to the WorkorderCustomer ID  form and a filtered the "factuurnummer" with: Is Null Or "" And in each available empty factuurnummer the previous  value +1

I want to generate more invoice numbers at once
mvdwalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you're asking.

You can create a Query, if needed, by creating and saving a QueryDef object. Or, you can just create the SQL of that query, and use that to do what is needed.

You also mention you want to "generate more invoice numbers at once". If that's the case, then just use the output from your form here:

Me.[factuurnummer] = Format(1 + Forms![Query_laatste factuur]![factuurnummer], "0000")

Since the value of factuurnummer would now contain your "next" invoice, you can simply increment that by 1 to increment the invoice number:

Dim MyInvoiceNum As Integer
MyInvoiceNum = Forms![Query_laatste factuur]![factuurnummer]

Now the variable MyInvoiceNum could be used over, and incremented when needed. So to create your first invoice:

Me.[Invoice] = DLookup("[Invoice]", "My Company Information") & Format(MyInvoiceNum, "0000")
Me.[Invoice date] = Int(Now())
Me.[Invoice].Requery

To create the second:

Me.[Invoice] = DLookup("[Invoice]", "My Company Information") & Format(MyInvoiceNum + 1, "0000")
Me.[Invoice date] = Int(Now())
Me.[Invoice].Requery

And the third:

Me.[Invoice] = DLookup("[Invoice]", "My Company Information") & Format(MyInvoiceNum + 3, "0000")
Me.[Invoice date] = Int(Now())
Me.[Invoice].Requery
0
mvdwalAuthor Commented:
I have uploaded an image.

example
I'll hope it will make things clear. I need to generate several invoices. Now I do this one by one with the code in another form. The latest value is in the subform above. I need to generate with a button the result below in another subform. This is what I had in mind.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you're incrementing both the Invoice and the factuurnummer value?

Would the Invoice number be exactly the same, except for the last 4 digits (which would increment by one)?

Finally - the ONLY thing you do to create an Invoice is set those 3 values? There is no other process that actually creates a record, or anything of that nature? I ask because that's somewhat odd - an "Invoice" is a distinct group of data, and typically does not tie in with other items (like Sales Orders, Packing Lists, etc). It may be created from that data, but it generally should be a separate and distinct record, with references back to the documents from which it was created.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mvdwalAuthor Commented:
Hi Scott McDaniel

Yes al the data is in the workorder table. We create a workorder for each packing list. When the  products for the customer are selected, we check automatically the order with an after update event. It writes the price in the table Workorder parts. When the workorder does not have an invoice number, we need to create this and print out the docs to send to our customer. So actually you 'll see the latest workorder with the latest invoice number and invoice date in the above subform. And beneath it you see the workorders wich are send with a packinglist and need an invoice number and finally a printed invoice.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you're using a WorkOrder as an Invoice? That sounds troublesome to me (a WorkOrder is not an Invoice in almost every case), but it's your data.

As mentioned, we could likely help with this, but you'd have to let us know what "drives" that subform you showed in the image. You're doing this in a form-based manner, and it needs to be done is a set-based manner (and we need to know the recordsource of that form before we can help much further).
0
mvdwalAuthor Commented:
Yeah it works fine! We work with it for 8 years now. We call it a workOrder but we create the packing list in it. At the end we fill in an invoice number and invoice date.  

It's working like an ERP system.

It's working! Here is the code I've used. I have to push severall times on the button and it will jump to the next record and it fills in. Thanks for your help.

  Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Query_laatste factuur"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 [SubformWorkordersInvoiceQuery].Form![factuurnummer] = Format(1 + Forms![Query_laatste factuur]![factuurnummer], "0000")
 [SubformWorkordersInvoiceQuery].Form![Invoice] = DLookup("[Invoice]", "My Company Information") & Format([SubformWorkordersInvoiceQuery].Form![factuurnummer], "0000")
 [SubformWorkordersInvoiceQuery].Form![Invoice date] = Int(Now())
[SubformWorkordersInvoiceQuery].Form![Invoice].Requery

 DoCmd.Close acForm, stDocName
 
 

    'Save the Record
    Me.[SubformWorkordersInvoiceQuery].SetFocus
DoCmd.GoToRecord , , acNext
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'SendKeys "+{ENTER}", True  'will work also
     
    'Go to the Next Record. If on the Last Record,
    'will Add a Record

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.