Solved I need some help with a find-item button

[asteropi]

Hey all,
Once again I turn to you for assistance. I have a problem I do not understand

I have a form to record batches of products made.
The indgredients I get from a drop down menu. Menu has the supply name-colour-manufacturer-lotnumber
When I get the ingredient, it seems it gets the name from the supplies table and saves it in the batch table, but for the manufacturer and the lot number it creates new entries in those tables.
I'm quite baffled because the info comes from different tables but the commands are exactly the same (it's supposed to only retrieve and save them in the Batch table.
What did I do wrong?

 

[June7]

Why are you saving the related data? Just save the SupplyID.

What is the form RecordSource?

Perhaps you should provide db for analysis. Follow instructions at bottom of my post.

 

[The_Doc_Man]

You show us that you have Option Compare Database but you don't have Option Explicit in the same area. What bothers me is that this code snippet relies on VBA behavior but it is not clear that it is the behavior you want.

You have lines that read like this:

SupplyName = SupplyNameCmb.Column(1)
...
Size = SupplyNameCmb.Column(3)
etc.

If I understand Access/VBA behavior, if you have controls with those names, the lines should probably read more like

Me,SupplyName = SupplyNameCmb.Column(1)
...
Me.Size = SupplyNameCmb.Column(3)
etc.

Without the Me. prefix (if they are in the main form), those could just as easily be treated as dynamically created variables as long as you don't have Option Explicit declared.

But then, you have a second problem. You should ALWAYS check for reserved words and for property names. SIZE, I guarantee, is a property name that appears in form context and should NEVER be a field or control name. Pretty sure that CODE occurs on some objects as well. How much that relates to your problem, I'm not sure - but if Access is confused over what variables you mean, I won't even try to predict what it will do.

 

[asteropi]

Why are you saving the related data? Just save the SupplyID.

What is the form RecordSource?

Perhaps you should provide db for analysis. Follow instructions at bottom of my post.

When making a batch it is important to record from which manufacturer you got the ingredient as well as which lot you used. So I need to have them on the form

 

[Pat Hartman]

When making a batch it is important to record from which manufacturer you got the ingredient as well as which lot you used. So I need to have them on the form

But to do that, you only need to store the FK to the item. You do NOT copy all the data about the item and store it in multiple places.

 

[June7]

Including lookup table(s) in form RecordSource is usually unnecessary and in this case causing confusion. Your code is saving values to lookup table fields (LotNumber, SupplierCompany, etc.), not BatchDetailsT (which does not have fields for this related info anyway). To display related lookup details, include fields in combobox RowSource and have expressions in UNBOUND textboxes
=SupplyNameCmb.Column(1).

Also, there is no reason to have parent table BatchNumberT included in the subform RecordSource, nor the child table BatchDetailsT in main form RecordSource. Bind each form to appropriate table and bind SupplyNameCmb to SupplyID field and eliminate code saving to fields.

 

[asteropi]

Including lookup table(s) in form RecordSource is usually unnecessary and in this case causing confusion. Your code is saving values to lookup table fields (LotNumber, SupplierCompany, etc.), not BatchDetailsT (which does not have fields for this related info anyway). To display related lookup details, include fields in combobox RowSource and have expressions in UNBOUND textboxes
=SupplyNameCmb.Column(1).

Also, there is no reason to have parent table BatchNumberT included in the subform RecordSource, nor the child table BatchDetailsT in main form RecordSource. Bind each form to appropriate table and bind SupplyNameCmb to SupplyID field and eliminate code saving to fields.

I did that but the VBA then gets stuck at the Me.Refresh command lol lol.
I don't know why

 

[asteropi]

I did that but the VBA then gets stuck at the Me.Refresh command lol lol.
I don't know why

I took off all the fields from those tables that I had in the query and now the VBA suppowedly works

I made unbound boxes and it takes the info
However, let's say I use IngrA and IngrB
WheI put IngrA it takes SupplierA and LotA
But when I put IngrB, the SupplierA changes to SupplierB and LotA to LotB

what am I doing wrong?

 

[mike60smart]

I took off all the fields from those tables that I had in the query and now the VBA suppowedly works

I made unbound boxes and it takes the info
However, let's say I use IngrA and IngrB
WheI put IngrA it takes SupplierA and LotA
But when I put IngrB, the SupplierA changes to SupplierB and LotA to LotB

what am I doing wrong?

Can you upload your latest version?

 

[LarryE]

You have no table relationship design at all created, so I attempted to create them based on your PK and FK fields. This is your table and relationship design at the present time:

You might need to simplify it a little before continuing on with your forms and reports. I also noticed you have lots of duplicate data in the ProductDetailsT. Further, the ProductsT has no relationship to any other table. It does have a ProductDetailID FK but I can't tell if you intended for that to be the same as ProductDeatailsID or not.

 

[asteropi]

Can you upload your latest version?

 

[asteropi]

You have no table relationship design at all created, so I attempted to create them based on your PK and FK fields. This is your table and relationship design at the present time:
View attachment 117335
You might need to simplify it a little before continuing on with your forms and reports. I also noticed you have lots of duplicate data in the ProductDetailsT. Further, the ProductsT has no relationship to any other table. It does have a ProductDetailID FK but I can't tell if you intended for that to be the same as ProductDeatailsID or not.

I do not know et to work with relationshiops
And indeed it should be ProductDetailsID in both tables

 

[asteropi]

You have no table relationship design at all created, so I attempted to create them based on your PK and FK fields. This is your table and relationship design at the present time:

You might need to simplify it a little before continuing on with your forms and reports. I also noticed you have lots of duplicate data in the ProductDetailsT. Further, the ProductsT has no relationship to any other table. It does have a ProductDetailID FK but I can't tell if you intended for that to be the same as ProductDeatailsID or not.

I fixed them I think

 

[mike60smart]

View attachment 117336


I fixed them I think

The following process's look OK:-
1. PurchasesT, PurchasedetailsT, Suppliers
2. CustomersT, OrdersT, OrderdetailsT

Not really understanding your process using all of the other tables.

Can you explain?

 

[asteropi]

The following process's look OK:-
1. PurchasesT, PurchasedetailsT, Suppliers
2. CustomersT, OrdersT, OrderdetailsT

Not really understanding your process using all of the other tables.

Can you explain?

I order supplies which have lot numbers, make the products which have batch numbers and sell to customers
When I make the batches I have to record which lots I used from the product, so if I get a bad one and the manufacturer recalls it I will know where I used it. So I need to history record everything

So Purchases -> Supplies (Lots) -> Products (Batches/Details) -> Orders (Details/Customers)
Customers-> Contacts to know when they contacted me to request something

I only made the ProductT to record wholesale prices. I really don't use it anywhere else. The bulk of the info is in ProductDetailsT
For example I have a product in 3 colours white, purple, lilac. All these have the same code except for the last digit. The also have the same price. But, there is no reason to show everything to my wholesale customers, they care about the product price, not the specific colour. So I made the ProductT with the initial SKU code and left out the last part.

 

[asteropi]

Never mind all. I fixed it myself.

 

[June7]

I downloaded the second posted db. Do not see changes I advised.

 

[Gasman]

Never mind all. I fixed it myself.

Well please tell us how you did it.
These forums are hear to help others, not just yourself, and your solution might help others? You never know.

Try giving a little for a change, instead of allways taking.

 

[asteropi]

Well please tell us how you did it.
These forums are hear to help others, not just yourself, and your solution might hel others? You never know.

Try giving a little for a change, instead of allways taking.

I realized I needed to record the lot and supplier in the BatchT for historical records. So I made 2 fields there and had the command button add my info in that table instead

Now I need to figure out how it will not input all the lots of one ingredient in the report

I downloaded the second posted db. Do not see changes I advised.

This is the correct one

 

[asteropi]

Including lookup table(s) in form RecordSource is usually unnecessary and in this case causing confusion. Your code is saving values to lookup table fields (LotNumber, SupplierCompany, etc.), not BatchDetailsT (which does not have fields for this related info anyway). To display related lookup details, include fields in combobox RowSource and have expressions in UNBOUND textboxes
=SupplyNameCmb.Column(1).

Also, there is no reason to have parent table BatchNumberT included in the subform RecordSource, nor the child table BatchDetailsT in main form RecordSource. Bind each form to appropriate table and bind SupplyNameCmb to SupplyID field and eliminate code saving to fields.

if it looks it up and for whatever reason I change suppliers, then the old batches will also channge suppliers, which I can't have. I need it to record the info in the batch table