Form Reference Error (1 Viewer)

So as a new developer, I'm running into some pretty mundane issues here and there.

Specifically- I've designed (most of) a database that I've attempted to have up and running for a small business.

A few days ago, I tested some of the features, and successfully printed from the Invoice form. It took data from the tblInvoices table and tblCustomers table exactly as it was intended to, but I never got around to making sure it could update to tables properly, or that a new form template would create a new record in tblInvoiceslike I want it to (for ease of access to invoicing for non-developers).

Since then, something went wrong, and now the form doesn't properly link to the other tables (some fields from tblCustomer are not edittable; and when changing any of the form control selections, I get an error to the effect of a join key not being in the recordset [?].

The biggest problem is of course that I haven't made any particularly large major changes since getting the printed invoice I wanted, and am not sure what I did to bring about these changes.

Attached is my database- I was hoping to get a little bit of help before I go ahead and put myself through the trouble of re-doing all of the relationships and deleting/remaking the form.

If that extreme sledgehammer method is indeed my only way of handling the problem- could someone still give me their best guess as to what could have possibly went wrong so that I may prevent it in the future?

Your tables are not normalized.

I recommend you should work through this tutorial to get a better understanding of relational data base design.

jdraw said:

Your tables are not normalized.

I recommend you should work through this tutorial to get a better understanding of relational data base design.

Click to expand...

Based off of what I'm seeing the big problem is my Invoice table specifically. The issue however, is that I'm not entirely sure how else to represent the data in a less redundant way.

The problem specifically: I'm attempting to organize workflow in a small business in a more efficient way by abandoning a paper system. I saw Access as the means in which to do this, since I can essentially customize it to be whatever I want to suit the needs of the business in question.

More importantly- I need the invoices to be viewable from every workstation (not only bookkeeping) to prevent the need for the trivial (and extremely error prone) method of having to communicate nuances of an order that has been worked on by more than one person (as multiple people + one job = risk for errors caused by miscommunication or lack thereof).

I want multiple people to have the means to fill out an invoice form, and have the information from the form applied to their respective tables easily (being able to create new customers would be nice if possible- but that's more of a QoL thing than anything).

I was hoping to accomplish this by creating an invoice table that would number each invoice and allow them to be searchable if they need to be referenced again later for any reason.

The business only truthfully has a handful of people on payroll, so things like an entire table dedicated to job titles or emergency contacts seemed extraneous.

From what I see though: basing information in the Invoice table on calculations involving data from other tables (prices can't really be primary keys as I see it as they wouldn't be unique or really indexable) is a faux pas. That leaves me in a bit of a bind for total pricing, and calculating discounts and taxes. Are there other more conventional ways to accomplish this?

jdraw said:

Your tables are not normalized.

I recommend you should work through this tutorial to get a better understanding of relational data base design.

Click to expand...

Let me clarify: The table I most readily see the normalization problem with is the Invoice table.

How can I more clearly represent the data I intend for it to have in such a way that the individual values each have their own invoice number/ID?

Zack90305 said:

..
How can I more clearly represent the data I intend for it to have in such a way that the individual values each have their own invoice number/ID?

Click to expand...

Show some examples of data where you think there will be problems, then we will easily be able to show you some solutions.

JHB said:

Show some examples of data where you think there will be problems, then we will easily be able to show you some solutions.

Click to expand...

At this point I'm starting fresh by just creating a new form and working my way up from there.

I guess my initial concern is if my tables in the attached database are a good base for the form I want to create.

I was told before to try to reduce the amount of redundancy in my database by throwing only the customer table's IDs and the Employee table IDs into my invoice table, as the rest of the information would still be accessible to suit my needs.

vvv

I want my invoices to be able to display a customer's name, company, and contact info; which employee took the order in the first place, when they took the order, when it's due, whether or not the order is active, and whether or not the order is completely paid off; products, their prices, discounts, and tax amounts (all of these calculations [taxes, discounts, totals] are dependent on the values that are typed in for price and quantity), etc; and I need my invoice's transactions to interface with with a transaction table for calculations to the deposit and amount due field (something along the lines of: total amount - transaction number [the deposit] = amount due and the form causing a boolean that checks if the invoice has a zero balance [or less] and returning "paid")

^^^

Most of these already exist as fields or in tables. But I'm having issues assembling it, and creating a form for users to interface with this information.

Is it a better practice for all of the information to interface to be in one (and only one) table? If so, how should I assemble this table?

The issue with the form was largely resolved when I made another one. I no longer have any reference errors- but I made the layout somewhat similar to the last one.

The problem was solved, but if I could only know the reason why- or why it happened in the first place... :/