Solved I need some help with a find-item button

[June7]

Fine, save SupplierID but probably not all details about product (colour, etc) as you were trying before. You still have code to save ProductName and SKU and not just ProductID (or ProductDetailsID), again, there are no fields in either Batch table for this info so you are just saving into lookup table ProductDetailsT. And, if combobox were bound, would not need code to save selected ID.

My advice about RecordSources still applies.

I also suggest not using exact same field name in multiple tables. Instead of BatchNumberID in both tables, consider BatchNumberID and BatchNumberID_FK.

 

[LarryE]

View attachment 117336


I fixed them I think

I didn't mean for you to change the relationship type. I meant you have too many tables that are not necessary to track what you are wanting to track. From what I can understand based on your tables and fields in those tables, you create products for sale made from supplies you purchase from various suppliers.

1. You have multiple categories of products made from supplies you buy
2. You produce these products in batches
3. Each product batch could be created in multiple sizes, net weights and gross weights
4. Each size could be made and sold in multiple colors (colours)
5. Each color you sell has various wholesale and retail prices with Value Added Tax (VAT) and without VAT and some with extra Packaging cost.

As to items you purchase to create your products:

1. You purchase items from your suppliers to make your products
2. Each supplier sells multiple items to you probably with a Purchase Order (if you use them)
3. Each item has a cost to you, an SKU code, a lot number, a description and other information

As to your customers:

1. Each customer has name, address, contact information and discounts associated with them and may place multiple orders through time
2. Each order has an order date, quantity ordered and Order Code Number
3. Each fulfilled customer order has final delivery information consisting of the applicable price, shipping cost, delivery information, date paid and paid amount (an Invoice if you use them)

Does this sound like your business model?

 

[asteropi]

I didn't mean for you to change the relationship type. I meant you have too many tables that are not necessary to track what you are wanting to track. From what I can understand based on your tables and fields in those tables, you create products for sale made from supplies you purchase from various suppliers.

1. You have multiple categories of products made from supplies you buy
2. You produce these products in batches
3. Each product batch could be created in multiple sizes, net weights and gross weights
4. Each size could be made and sold in multiple colors (colours)
5. Each color you sell has various wholesale and retail prices with Value Added Tax (VAT) and without VAT and some with extra Packaging cost.

As to items you purchase to create your products:

1. You purchase items from your suppliers to make your products
2. Each supplier sells multiple items to you probably with a Purchase Order (if you use them)
3. Each item has a cost to you, an SKU code, a lot number, a description and other information

As to your customers:

1. Each customer has name, address, contact information and discounts associated with them and may place multiple orders through time
2. Each order has an order date, quantity ordered and Order Code Number
3. Each fulfilled customer order has final delivery information consisting of the applicable price, shipping cost, delivery information, date paid and paid amount (an Invoice if you use them)

Does this sound like your business model?

Exactly to the point

 

[asteropi]

Fine, save SupplierID but probably not all details about product (colour, etc) as you were trying before. You still have code to save ProductName and SKU and not just ProductID.

And my advice about RecordSources still applies.

I actually don't save all the details in the Batch tables. It's just for my reference in the form, but I only save ProductDetailsID and SupplyID

 

[Pat Hartman]

But when I put IngrB, the SupplierA changes to SupplierB and LotA to LotB

That's because the controls are unbound. When you are using a coninuous or DS form, you can show all the lookup data from the record selected by the combo by using a query with a join as the RecordSource for the subform. WARNING!!!!!! If you do this, you MUST set the control properties of all the lookup fields to LOCKED. If you don't, you run the risk of the user accidentally changing the underlying data which you do not want to happen.

 

[June7]

I actually don't save all the details in the Batch tables. It's just for my reference in the form, but I only save ProductDetailsID and SupplyID

Why do you still have this code:
ProductDetailsID = ProductNameCmb
ProductName = ProductNameCmb.Column(1)
SKU = ProductNameCmb.Column(2)

Don't see need for Me.Refresh

 

[asteropi]

Why do you still have this code:
ProductDetailsID = ProductNameCmb
ProductName = ProductNameCmb.Column(1)
SKU = ProductNameCmb.Column(2)

Don't see need for Me.Refresh

forgot to take it off. it's fixed now thank you

 

[Pat Hartman]

Do not use Me.Refresh when your intention is to force Access to save the current record. Be explicit. Updating the dirty record is a side effect of Refresh and Requery. There is an explicit command to ask Access to save. Use it when that is what you mean.