Saving invoices with services filtered by date for future reference

[Wingnut74]

Hi Everyone.
I am currently trying to solve a problem with invoicing. The idea is to create an invoice that looks up all the work completed for one customer in the previous month and then create the invoice. I have no issues creating the invoice based on the work records, I can even search for invoices by Owner which will give me all services but only by adding the InvoiceID to the services table manually. how can I save the services details with the invoice ID/Number to be able to to review the invoice at a later date. I have a feeling this is an easy fix but I just can't see it.

Tables:
Customer
Invoice
InvoiceDetails
Services
All linked with their respective ID's

I have looked at various examples on the internet but they all seem to concentrate on sales invoices created by using a drop down box to select items but I don't want to have to select 30+ services each time I create an invoice. So my question is: How do I get the invoice number or ID to register on each individual service OR am I just going about it the wrong way.



I would be grateful for any advice.

Many thanks in advance.

Wingnut74

[kd2017]

Can you post a screenshot of your relationships?

[Minty]

I'm guessing your services records have an empty field for InvoiceRef?

In which case

SELECT * FROM Services 
WHERE InvoiceRef Is Null And CustomerID = EnterYourCustRef

Will select the records you want. Then

UPDATE Services SET InvoiceRef = [Enter the invoice ref here] 
WHERE InvoiceRef Is Null And CustomerID = EnterYourCustRef

Would update the same records with the InvoiceRef

Obviously, you could use a form to get the criteria and automate the invoice number value from your invoices table.