What's wrong with my simple Access DB? Can't enter data into a form

[djmurphy58]

I am a beginner user and am having trouble with a simple DB I'm trying to create. I want to track delivery of stone to a jobsite. A separate supplier (Vulcan Materials) supplies three different types of stone. The stone gets delivered to the jobsite by a separate trucking company (CTI). Both companies charge a unit price per ton, and both submit separate bills.

My problem is I have created a form with 4 fields, but only 1 of them allows me to enter data. (All 4 are unlocked).

So here are my tables:

tblStone:
StoneID (primary key, data type is number)
StonePrice
StoneType
CostCode

tblCTIPayforms
CTIPayformID (PK, data type is autonum)
CTIPayform

tblCTIInvoices
CTIInvoiceID (PK, data type is autonum)
CTIInvoice

tblVulcanInvoices
VulcanInvoiceID (PK, data type is autonum)
VulcanInvoice

tblDelivery
StoneID (PK, data type is number)
OrderID (PK, data type is autonum)
DelivDate
VulcanTicket
DelivPrice
Weight
CTIInvoiceID
VulcanInvoiceID
CTIPayformID

Now, I suppose this won't do any good to anyone if you don't know how my tables are linked. So if anyone wants to help me, give me your email address, and I'd be happy to send you my file.

Thank you very much,
Dennis

 

[KenReay]

Hi

No promises, but I will look, kenneth.reayREMOVETHISBIT@talk21.com

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[quest4]

Dennis, have you check the relationship links to see if the Referntial Integrity is checked, as well as the two casade check boxes. Makes a major differennce, if all three are not checked.

 

[djmurphy58]

Quest4-
Thanks for the reply. Yes, all three boxes are checked for each of my relationships. Knowing me, and my minimal experience with DB's, I probably have something screwed up with my logic.

Thanks,
Dennis

 

[willir]

Dennis

You have supplied the table format but not the form.

How is the form created?

Sometimes not be able to enter data suggests one of the criteria for the data entry into the table are not being met.

Your original question suggests you are trying to use a form to track delivery. Perhaps...

StoneID
OrderID
Vulcanticket

either Invoice or weight

Where the tblDelivery uses OrderID + StoneID as the primary key.

If I am sort of right so far,
Has the order been created? (I suspect you already have your stone table setup with data)

Are you basing the form on a query or a table?

Lastly, do you have referential integrity enabled on your relastionships -- good thing to do, especially when trying to create deliveries.

One more thing...
Look in the delivery table to make sure the data is okay -- specifically, null OrderID's.

Richard

 

[djmurphy58]

Richard-
The form I am trying to create contains the following fields:
DelivDate (from tblDelivery)
Weight (from tblDelivery)
VulcanTicket (from tblDelivery)
StoneType (from tblStone)

After I create the form, the only field to which I am able to add data is StoneType.....which leads me to believe something is wrong with my tblDelivery.

Yeah, you're pretty much right. I want to track the truckloads of stone that get delivered to the jobsite. When the truckloads come to the site, the truck driver hands me a receipt from the supplier (Vulcan). That receipt contains the following info: delivery date, weight (tons), stone type, and a unique 6-digit ID number. After receiving all the truckloads in a given day, I want to be able to go into the office, and enter those 4 pieces of info into a database form. Then, when both the supplier (Vulcan) and the trucker (CTI) submit bills for the stone, I need to be able to print out a report showing what stone has been delivered when, and how much of each type. Then for each truckload of stone that the 2 companies have billed for I want to be able to input the companys' invoice number. That way, if I ever need to, I can print out exactly which truckloads of stone have been paid for under which invoice.....

Does that make a little more sense....?

Yes, I do have all three checkboxes checked on all of my relationships.

The data in the tblDelivery is ok......no nulls.

I am creating this DB after I have already received 150 truckloads of stone. Before the DB I had all the data stored in an excel spreadhsheet. So I have imported all the data from that spreadsheet.

Thanks in advance,
Dennis

 

[willir]

djmurphy58

I believe the problem is that you have not met the requirements for the tblDelivery.

tblDelivery
[red]StoneID[/red] (PK, data type is number)
[red]OrderID[/red] (PK, data type is autonum)
DelivDate
VulcanTicket
DelivPrice
Weight
CTIInvoiceID
VulcanInvoiceID
CTIPayformID

StoneID and OrderID for tblDelivery are not yet defined.
You do have StoneID from tblStone -- not the same thing.

Recreate your form using the form wizard.
Reference the tblDelivery for the source.

You can then change to a combo box for
StoneID and reference tblStone


Also, I am confused -- you do NOT have and tblOrder but you use OrderID as an autonumber with PK = StoenID + OrderID

If you are using autonumber for the order, you can use just the OrderID as the PK.

Or would a better solution would be to create a proper order table and reference it from your delivery table.

Either way, you have to ensure you meet the data entry requirements for the table before you can enter data.

Richard