Error Creating New Record

[Mroper14]

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.

Im going to attach the database, on the login form, 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.

[Mroper14]

I will paypal someone 20 bucks if they help me finish this by tonight.

[Mroper14]

I will give you 20 bucks to help me resolve this problem by tonight. I can be contacted via email or AIM.

AIM - MRoper18
E-Mail - mikeroper@gmail.com

[geekgirlau]

Okay, I've had a quick look.

I think you're making this unnecessarily difficult for yourself. A couple of recommendations:

Use bound controls on your forms. All the visible controls on the Sales forms are unbound, which means you have to manually handle updating of the record rather than letting Access do it for you.
Change your ID fields to AutoNumber, rather than having to populate these the way that you are currently.By the way, I've amended the title of your post to make it specific to your question - you'll get a much better response rate this way.

[Mroper14]

Thank you so much. I have actually fixed this problem with a sorragate key. Only problem now is, say I want to update the quantity of an item onhand, is there a way I can change a value of something in the products table based on a known product ID. So if I know the primary key for a record in another table, how would I go about updating a field in that table for the record for which I know the primary key?

[geekgirlau]

If you are in a form, you can set the value to the relevant control. For example,

Me.ProductID = myvalue

Another method is to run an update query. The simplest method I find is to create an update query manually, then go into SQL view and copy the SQL string and insert it into your code. So you may end up with something like this:


Dim strSQL As String


strSQL = "UPDATE Products " & _
"SET QtyOnHand = " & NewQty & " " & _
"WHERE ProductId = 1"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True