Hello everyone, I am having some serious trouble with my Access Database.

I am trying to create a database that can manage a store. I currently have a few forms, Login which "logs" an employee in. Then the begin sale form, which starts a transaction. Then finally I have the sales line item form. This form is supposed to have many many records with the transaction ID and the product ID as a combined key. However, the problem I am having is that everytime I try to go to the next record it gives me an error about not being able to go to a new record.

In the database an acceptable id is "20001" and the password is "password". Then click the begin sale form, and enter the customer id "40001". Products are 30001 - 30025. The problem occurs when you click the "next item" button. Thanks in advance for the help.

I have attached a link where the file can be downloaded.

Please guys I'm desperate!!!!

AIM: MRoper18

I can paypal you as soon as this one problem is fixed.... I'm sure its an easy problem!

Your Sales Line Form is bound to the SaleLineItem table, which contains no data.

I have never been a fan of .. and "never" use compound (or composite) Primary Keys in and Access database.
I also have "never" seen a legitimate need for them ....
All they do is create the types of problems that you are currently having.

I suggest that you get rid of these composite keys and you will find your life much easier ....

RDH

The only thing is that each transaction is going to have multiple sales line items. There is no way to uniquely identify it without some fake primary key. Any recommendations on how to get around this?

As for the form referring to an empty table... the purpose of the form is to input data to the table.

I have attached the ERD so everyone can see the layout of the tables.

Why is it "fake" ???
All that is needed here is a way to "uniquely" identify each record in the table.

This is known as a "surrogate primary key" .....
To keep a combination of other fields unique ... create a composite (compound) "index" of the fields in question.

RDH

A few things that I noticed:

Your form is bound, but the controls on the form are not. You should bind the controls to their corresponding fields in the SaleLineItem table.

Then in your nextItem_Click() subroutine, delete the following commands:

SUBBox = " "
USPBox = " "
QBox = " "
PIDBox = " "

They are unnecessary (if the controls are bound), as the boxes clear upon going to a new record.

Also, on the opening of the form, the following commands are run:

TIDBox = TranID
TransactionID = TranID

These commands put the form into a "Dirty" state as it is open. This is not good practice, as the values are lost if the user hits the esc button. These commands should be moved from the form's "Open" event to the "Dirty" event., and actually, the TransactionID = TranID line would be unnecessary with the controls bound to the table.

thank you for the responses. The only problem with binding the controls is that wouldn't it then be really hard to update the fields as I was saying? Furthermore, won't this not solve the problem?

I started tinkering with your forms and decided to construct what I think is a preferable design. This utilizes a form with a subform to do what I think that you are trying to do.

After logging on using your logon form, open frmSalesMain. Once you select a customer, you can tab down to the subform and enter multiple line-items.

The form still has a bit of work to do, but I think that you look over the design and try to see how it is put together, you might be able to see how to proceed.