Solved Multi Record Creation Form (1 Viewer)

LGDGlen

Member
Local time
Today, 17:10
Joined
Jun 29, 2021
Messages
229
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:
  • Filter consignments using multiple fields to filter against
  • Select all filtered records, or select multiple records from the filtered list
Once the user has filtered the records to what they want there are a number of actions they can carry out on the selected records, and in this case we are creating invoice records in the invoice table that are linked to the consignments with a number of identical bits of information but the amounts will be different.

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
I hope this all makes sense and i hope there is a solution, i'm almost certain that it is probably just a re-pointing of how i'm looking at this situation and it will all suddenly make sense and be straight forward.

Happy to expand on any of the above to give a bit more information and context

Thanks in advance

Glen
 

LGDGlen

Member
Local time
Today, 17:10
Joined
Jun 29, 2021
Messages
229
ok after reading a bit more i think the following might be the right way to do things:
  • Form bound to invoices table showing no records
  • A number of unbound fields for the general information
  • Button to generate a number of records based on the list of consignment IDs passed to the form as a parameter
  • When the button is pressed the continuous form displays the new records with the amount field being the only thing enabled to be edited and the unbound fields now locked
  • The user can then add the amounts as the require
hope that makes sense, does that sound like the right way to process things?
 

LGDGlen

Member
Local time
Today, 17:10
Joined
Jun 29, 2021
Messages
229
i actually think this - https://stackoverflow.com/questions...in-one-form-only-one-field-changes-per-record - covers everything i need, it even shows a way to show NO records when the form opens by finding the latest record and making the filter be 1 more than that (it was a comment a bit down the page)

i think i'll try to implement the solution documented at that link but if anyone can give me a bit of support in that "yeah that seems like it does what you want" i'd appreciate it
 

Users who are viewing this thread

Top Bottom