I have a form to create a new record in an Invoice table. I have a combo box to a linked table for the Invoice Status. The link between the two tables is the invoice number. I can select a status in the combo box but when I save the new invoice record, the record in the linked Invoice Status table is not created. I am stumped on how to get this record created.
Create record in linked table
- Thread starter phoust
- Start date
There are approx 20 different statuses. There is a field in the Status Lookup table with another field that groups these status codes into different groups. I am then able to pull up lists of invoices by the status group.
The invoice records are imported from a csv file which does not include a status.
Gasman
Enthusiastic Amateur
- Local time
- Today, 13:32
- Joined
- Sep 21, 2011
- Messages
- 15,351
Well you must be applying the status somewhere?
I still believe that should be in the invoice table.
Wait until some experts respond.
You're doing a few things wrong.
1. 1-1 relationship. There's no reason to have tblInvoiceStatus if it only goes to 1 record in tblTPAGInvoices and vice versa.
That solves your issue easily. Your form is already bound to tblTPAGInvoices, so put your status field in that table and bind the control to it. After that though, you are doing a few things that you should fix as well:
2. Not using ID fields as foreign keys. You knew to put them in your tables--every table has an ID field primary key-- but not one is used correctly--or at all. That is the value you should use to JOIN tables together, not the ones you have actually used throughout. One example is InvoiceQuote and PackingLookup--they are joined QuoteBoxNo to BoxSize which is wrong. The ID value of PackingLookup should be used, not BoxSize. You've done this in every table you have joined.
3. Poor names. You should only use alphanumeric and underscores in table/field names. When you use spaces and special characters (e.g. Invoice Status Lookup, Offs/Rolls, U-Line#, etc) it makes coding/querying difficult later on. Additionally, I would rename all your ID fields by prefixing them with the entity they are for (e.g. Payment_ID, Quote_ID, etc.)
4. Storing Calculated values. Payments and Balance shouldn't be fields in tblTPAGInvoices if you can calculate them with other fields. I bet you can get Payments by adding up PmtAmount in InvoicePmt and Balance by adding up additional fields you have. Don't store when you can calculate.
5. How do you know what quote to use? If you give 5 quotes on an invoice how do you know which one to go with? There's no date field in that table, no field to designate 'Accepted', how will you know which one to use?
6. Is status calculable? If a status isn't included, you must be looking at other data and determining it's status. If that's the case, status might not even be needed as a field. Similar to Pmts and Balance you would use logic to determine the status which would free you from ever having to set or update it.
1. 1-1 relationship. There's no reason to have tblInvoiceStatus if it only goes to 1 record in tblTPAGInvoices and vice versa.
That solves your issue easily. Your form is already bound to tblTPAGInvoices, so put your status field in that table and bind the control to it. After that though, you are doing a few things that you should fix as well:
2. Not using ID fields as foreign keys. You knew to put them in your tables--every table has an ID field primary key-- but not one is used correctly--or at all. That is the value you should use to JOIN tables together, not the ones you have actually used throughout. One example is InvoiceQuote and PackingLookup--they are joined QuoteBoxNo to BoxSize which is wrong. The ID value of PackingLookup should be used, not BoxSize. You've done this in every table you have joined.
3. Poor names. You should only use alphanumeric and underscores in table/field names. When you use spaces and special characters (e.g. Invoice Status Lookup, Offs/Rolls, U-Line#, etc) it makes coding/querying difficult later on. Additionally, I would rename all your ID fields by prefixing them with the entity they are for (e.g. Payment_ID, Quote_ID, etc.)
4. Storing Calculated values. Payments and Balance shouldn't be fields in tblTPAGInvoices if you can calculate them with other fields. I bet you can get Payments by adding up PmtAmount in InvoicePmt and Balance by adding up additional fields you have. Don't store when you can calculate.
5. How do you know what quote to use? If you give 5 quotes on an invoice how do you know which one to go with? There's no date field in that table, no field to designate 'Accepted', how will you know which one to use?
6. Is status calculable? If a status isn't included, you must be looking at other data and determining it's status. If that's the case, status might not even be needed as a field. Similar to Pmts and Balance you would use logic to determine the status which would free you from ever having to set or update it.
GPGeorge
George Hepworth
- Local time
- Today, 05:32
- Joined
- Nov 25, 2004
- Messages
- 2,300
In the Northwind Developers Edition template, we used Status for Orders and for Purchase Orders. In VBA, we implemented rules to determine the current status of an order or purchase order, depending on what events had taken place, such as submitting a PO to an approver. The user does not have direct ability to change statuses because that would create a risk of non-compliant modifications to the procedures established by the company.
Check out how it's done. I think it will work well for you as well. https://support.microsoft.com/en-us...emplates-e14f25e4-78b6-41de-8278-1afcfc91a9cb
Also, just because the source data in the csv does not have a status, that doesn't mean you can't, or shouldn't, manage it in your Access table.
GPGeorge
George Hepworth
- Local time
- Today, 05:32
- Joined
- Nov 25, 2004
- Messages
- 2,300
Could you re-read Post #10 with a suggested way to learn about managing Status for Orders and Purchase Orders, which would also apply to Status for Invoices?
Gasman
Enthusiastic Amateur
- Local time
- Today, 13:32
- Joined
- Sep 21, 2011
- Messages
- 15,351
The only reason I can see for a status in another table is if you wanted to keep track of the status and when it changed?
Even for that I would only add a record to the status table when the status was changed in the invoice table.
I can see that you are just ignoring the advice here, so I will load the gun for you.
Run an append query for your status table when the status changes.
Now you can go and shoot yourself in the foot.
Even for that I would only add a record to the status table when the status was changed in the invoice table.
I can see that you are just ignoring the advice here, so I will load the gun for you.
Run an append query for your status table when the status changes.
Now you can go and shoot yourself in the foot.
Similar threads
