Records

[Griphon]

Hello June7,



Okay, I am turning myself into a raging lunatic. Let me start at the bottom and slowly work through this project. Please can you see attached and help me to identify if my relationships are correct.

Thanks in advance.
GriphonDatabase.zip

[June7]

Sorry, I goofed. Supplier should not be in both tblPurchases and tblProducts. Remove from tblPurchases because supplier is associated with the PO through the product.

I don't understand the need for tblPONum.

[Griphon]

Database.zip

Sorry, I goofed. Supplier should not be in both tblPurchases and tblProducts. Remove from tblPurchases because supplier is associated with the PO through the product.

I don't understand the need for tblPONum.

Hello June7,

tblPONum is for the list of already generated Purchase Order Numbers that I receive from a third party app (Quickbase)

Thanks, Please check?
Griphon

[June7]

I still don't understand why you have tblPONum. Why not just import those PO numbers directly into tblPurchases?

tblPurchases has an autonumber ID field set as primary key but you have PONum field set as the linking field. Either ID should be the link or PONum should be the primary key.

Same for ID and PONum in tblPONum.

[Griphon]

I still don't understand why you have tblPONum. Why not just import those PO numbers directly into tblPurchases?

tblPurchases has an autonumber ID field set as primary key but you have PONum field set as the linking field. Either ID should be the link or PONum should be the primary key.

Same for ID and PONum in tblPONum.

Hello June7,

I need to enter the PO, then capture the stock. Check it against the delivery note and then update the stock quantity. I created a couple of forms. The only thing now is how do I create the form to select PONum, Supplier and enter stock using exsiting items from the master stock table and updating the qty.

Thanks
Database.zip

[June7]

Ideally, would not 'update' stock table. Stock on hand would be calculated when needed. Review http://allenbrowne.com/AppInventory.html

I still don't understand the need for tblPONum.

Cannot have related primary and foreign key fields both be autonumber. tblPONum and tblPurchases are not properly linked, nor are tblPurchases and tblPurchaseDetails.

Use form/subform arrangements for entering purchase details and sales details. http://office.microsoft.com/en-us/ac...010098674.aspx

Main form bound to tblPurchases, subform bound to tblPurchaseDetails with combobox to select products

Main form bound to tblSales, subform bound to tblSalesDetails with combobox to select products

[Griphon]

Ideally, would not 'update' stock table. Stock on hand would be calculated when needed. Review http://allenbrowne.com/AppInventory.html

I still don't understand the need for tblPONum.

Cannot have related primary and foreign key fields both be autonumber. tblPONum and tblPurchases are not properly linked, nor are tblPurchases and tblPurchaseDetails.

Use form/subform arrangements for entering purchase details and sales details. http://office.microsoft.com/en-us/ac...010098674.aspx

Main form bound to tblPurchases, subform bound to tblPurchaseDetails with combobox to select products

Main form bound to tblSales, subform bound to tblSalesDetails with combobox to select products

Hello June7,

Would it be wise/possible to link product code, rather than ID. So I would be able to lookup a product using a barcode? You will see with the attached db, the products on the purchase is not working correctly.

Please help.Database25112013.zip

Thanks

[June7]

You haven't fixed the PK/FK linking issues described in previous post.

The barcode can be used to lookup a product even if it is not primary key but it certainly could be the primary key and makes sense to me that it be the PK.

[Griphon]

You haven't fixed the PK/FK linking issues described in previous post.

The barcode can be used to lookup a product even if it is not primary key but it certainly could be the primary key and makes sense to me that it be the PK.

Hello June7,

Please take a look and advise, I am really battling to understand this concept. I have read through a couple of examples but I cannot seem to see where I am going wrong. If possible make the relevant relationship changes and post. PLEASE.

See attached

ThanksDatabase25112013.zip

[Griphon]

Hello,

I cannot understand why tblProducts is reflecting PONum data without any links or relation? I also cannot understand why I cannot use my combo box to select or scan a barcode on purchases, see attached screenshot and db.

ThanksDatabase25112013N.zip

[June7]

Still need to fix table relationships. Cannot proceed until this is done.

A table's primary key should be the field that is saved as foreign key in child table.

Set PONum in tblPurchases and tblPONum as primary key.

Set SalesNo in tblSales as primary key.

The ID fields in those tables are not used in relationship, setting them as primary serves no purpose.

You have Barcode set as primary key in tblProducts but the links to tblSalesDetails and tblPurchaseDetails is on ProductID.

Associated primary and foreign key fields must be the same data type.

The Barcode field is set as text. If you want this to be primary key then the link fields in other tables must also be text.

[Griphon]

Still need to fix table relationships. Cannot proceed until this is done.

A table's primary key should be the field that is saved as foreign key in child table.

Set PONum in tblPurchases and tblPONum as primary key.

Set SalesNo in tblSales as primary key.

The ID fields in those tables are not used in relationship, setting them as primary serves no purpose.

You have Barcode set as primary key in tblProducts but the links to tblSalesDetails and tblPurchaseDetails is on ProductID.

Associated primary and foreign key fields must be the same data type.

The Barcode field is set as text. If you want this to be primary key then the link fields in other tables must also be text.

Hello June7,

I have made the relevant changes, but when creating the sub-form in frmpurchases (sub frmpurchasedetails) the block is left blank?

Please take a look now and let me know, I think I have managed to fix the relationships but now have this other issue.

Thanks in advance

Database26112013N.zip

[orange]

?? I looked at your database. I don't see any frmpurchasedetails???

I am attaching a jpg of your tables and relationships as they are at the moment.

[Griphon]

26112013MDB.zip

?? I looked at your database. I don't see any frmpurchasedetails???

I am attaching a jpg of your tables and relationships as they are at the moment.

Hello Orange,

Sorry about that, it was (tblPurchaseDetails subform), please could you take a look as to why my products on tblPurchaseDetails subform appears blank.... not picking up the products?

Please see attached.

Thanks in advance.....

26112013.zip

[orange]

Why are ProductID and Barcode both in tblPurchaseDetails?
What is different in ProductID, ProductCode and Barcode in tblProducts?

You have Purchases and Sales, but there are no Amount fields??

Can you tell us in 4 or 5 sentences exactly WHAT your business is? Keep it simple so we can understand what your model is trying to support.