To make an invoice with selected records

[mercapto]

Hello everyone.

I have a database of a chemical laboratory.



So far, I've been making one analytical report - one invoice. But some regular clients are asking us to simplify (for them ) by giving an only invoice for all the services.

I planned it this way:
- 1 new table (tblMultipleInvoice) linked to my main table (tblAnalysis) by the InvoiceNumber field in both tables. I also added a new checkbox to the analysis table that marks it as "invoiced with others" and also allows me to select it when building the invoice.
- the form is opened through the Clients form, so it filters the records that can be invoiced together.
- the records are listed, I check the box to select items and I open a new MultipleInvoice form with them that allows me to add calculation fields and date, etc.

This is theory. Practice was an epic fail.

I tried many ways to update the provided invoice number in all the checked records and I never succeeded (I know, this is not the place to post the issue).

But after solving that I'm going to face another technical problem. I will have to open a form-subform with a new tblMultipleInvoice record, provide the InvoiceNumber by code and then, refresh the subform with all the tblAnalysis records for that invoice. Too hard for me.

So before keeping on this hard way I've chosen, I want to ask you guys if this design is appropriate for what I want to do or if there is a better way.
I would also ask you for samples if possible.


Thanks in advance.

P.S.: I know I am doing it in the opposite way, because the natural in databases is to first create the invoice and then add the items by the subform.

[orange]

I think you should research NORMALIZATION. Also review attached data model for Orders and Order Line Items and see if a similar concept fits your situation.
https://www.accessforums.net/databas...rds-27460.html

Good luck with your project.

[mercapto]

Hello orange.

Thanks, but my question has "more level". I certainly know what normalization is.

I know I could do this by a form-subform and combo boxes in the subform, but It's not the way I want.

Any other idea?

Your link goes to this thread, indeed.

[orange]

Sorry for the return link
Here is the iintended link http://www.databaseanswers.org/data_...ices/index.htm

If you don't want form/subform, can you be more definitive on what exactly you want to do, and what sort of constructs you want to use?

[peterr89]

Hello, Good luck with your project

[mercapto]

Thanks... LOL

I finally did what I wanted to.

The key is to use openargs to open a invoice-building-form and update the shared invoice number in the textbox. After Me.Refresh, the subform will show all the records selected and we can now print a new invoice.

thx