Good day all,
Attached is a copy of my database (2007). I have tried to make notes in the tables as best I can.
It's been a while since I have worked with Access and I'm working through setting up a personal plant breeding and inventory tracking database. In an effort to keep similar data types together, I am wondering if I have made my structure more complicated than need be since I have been running into some issues.
My hope was to use table.TransactionID and table.TransactionDetailID to control all the additions and subtractions of inventory in my database.
tableTransactionTypeID contains a large number of different transaction types I had envisioned these two tables being able to handle.
Unfortunately, not every transaction (ex. harvesting a fruit from a plant [+1 fruit to inventory], converting that fruit into seed [-1 fruit, +X Seed]) will have a BillingID and BillingAddressID associated with it because it is an internal transaction. I do still want to keep the SenderID and ShippingID information so that I have a geographical record of where that transaction occurred. So in order to deal with that, I have pulled the billing information out into its own table. Also, given that any Client can be any one of the different parties in a transaction, all three IDs ship back to a common pair of tables (ClientID, ClientAddressID).
One of the big issues I seem to be having now is developing forms for inputting multiple tables worth of data without using subforms.
Based on what I have decided to include in my Transactions table, I know I will eventually need to use multiple forms to deal with each transaction type. Example: one for incoming invoices, one for harvesting fruit, one for repotting, etc.
To start, I have been trying work on a form to log incoming invoices. From a functional standpoint, I had hoped to be able to set up a set of cascading comboboxes to log all the clients involved in a transaction and their corresponding addresses. These combobox selections would then fill in all the PKs and FKs in the background, and then display the relevant data on the form. From an aesthetics point of view, I was hoping for this relevant data to take the form of an invoice where each combobox selection would display sender, billing, and shipping information. I only mention this because it was dictating my decision making when it came to form design.
As it stands right now I have two main problems:
1. I keep getting an error along the lines of "Cannot add record, join key of table "TransactionBillingID" is not in the dataset," whenever I try to tack on the Billing information to a new record.
2. I don't have a good way to display more than one type of ClientID on my form. For example, even if my combobox works, enters data into the PKs and FKs in the background, I haven't been able to display more than one group of data (FirstName, LastName, Email, etc.) at a time from either SenderID, BillingID, or ShippingID.
This feels like a lot to unpack and a rather tall order so I appreciate your help in advance.
Thank you for your time.
Attachment 40837