This might be a bit of a ramble but i am going to try to describe the form/query i am trying to create.
Consignments have invoices from multiple sources attached to them using consignment_id as foreign key in the invoices table.
There is a need to be able to select a number of consignments and attach invoices with identical information other than amounts, so the reference, the company invoicing us, the status of the invoice and currency will be the same for each invoice record, the amount will be different.
I have a form allowing the user to:
I understand there are ways to have invoices when there is 2 tables 1 for the general information and then another for the lines that make up the invoice and for this specific instance it might be the better way to store the data if it was the only type of invoice but this is the only instance where the invoice could be split across multiple amounts, all the other invoices will be 1 invoice 1 amount and I don't want to have to deal with 2 different invoice tables when processing the invoice information for reports etc.
So my plan is to have an unbound form which receives a list of consignment IDs and has a few general input fields that use the DIM tables of things like company, currency, status and then a final field for the reference number. The list of consignment IDs generates a repeated rows of consignment id (read only) and an input field to allow the user to input an amount. There is then a button which allows the user to create the invoice records using the general information and looping through each of the rows and adds the amount and writes the record using the consignment ID as the foreign key into the consignments table.
But this is where my knowledge and understanding reaches its limits. What i think should happen is that there is some sort of sub form that is displaying a query using the list of consignment IDs with an unbound field (which i don't think is possible but i'm putting it here as an example of what i want to do) but i just don't know:
Happy to expand on any of the above to give a bit more information and context
Thanks in advance
Glen
Consignments have invoices from multiple sources attached to them using consignment_id as foreign key in the invoices table.
There is a need to be able to select a number of consignments and attach invoices with identical information other than amounts, so the reference, the company invoicing us, the status of the invoice and currency will be the same for each invoice record, the amount will be different.
I have a form allowing the user to:
- Filter consignments using multiple fields to filter against
- Select all filtered records, or select multiple records from the filtered list
I understand there are ways to have invoices when there is 2 tables 1 for the general information and then another for the lines that make up the invoice and for this specific instance it might be the better way to store the data if it was the only type of invoice but this is the only instance where the invoice could be split across multiple amounts, all the other invoices will be 1 invoice 1 amount and I don't want to have to deal with 2 different invoice tables when processing the invoice information for reports etc.
So my plan is to have an unbound form which receives a list of consignment IDs and has a few general input fields that use the DIM tables of things like company, currency, status and then a final field for the reference number. The list of consignment IDs generates a repeated rows of consignment id (read only) and an input field to allow the user to input an amount. There is then a button which allows the user to create the invoice records using the general information and looping through each of the rows and adds the amount and writes the record using the consignment ID as the foreign key into the consignments table.
But this is where my knowledge and understanding reaches its limits. What i think should happen is that there is some sort of sub form that is displaying a query using the list of consignment IDs with an unbound field (which i don't think is possible but i'm putting it here as an example of what i want to do) but i just don't know:
- whether what i'm trying to create is even possible
- how to achieve what i want to do
Happy to expand on any of the above to give a bit more information and context
Thanks in advance
Glen