Stock Tracking Form and DB

[jamesmiller460]

Hi all, 

I've attached a DB/Form im compiling to do a simple intake form for stock holding, Consignment Number needs to be unique, when inputting the TM_Carton_ID on the subform i want it to look up against the DB and bring back if its Pure or mixed, but i cant seem to get it to happen for multiple lines. 

See my attached DB, any help on this would be useful, feel free to add any additions too, once this is live the TM_Carton_ID will be scanned in via a device and we need to tell the user if that carton is a pure carton or a mixed carton. 

any help would be great on this thanks.

DB is below.
 https://drive.google.com/file/d/1UgD7rwVlKIe_0EHKlDQ2TWqhkbwyLzBx/view?usp=sharing 

[#2]

Can you provide a definition of "pure carton" and "mixed carton"?

Are all of the tables in this downloadable accdb, or is it a subset?

[#3]

In this link, on the table "tble_Carton_Type" the inputted carton should look up to that table and the corresponding value out of the "type" column should be brought back.

[#4]

Okay, but does "Mixed" mean that there is more than one SKU in a carton? And does "Pure" mean there is only one SKU in a carton? And are the SKUS also listed in each carton individually? If so, the field that is used to indicate "Pure" or "Mixed" is redundant because you can count the different SKUs in a carton. So, I'm trying to understand the business context, not just the fields in the tables.

[#5]

Yeah exactly, pure = only 1 sku and mixed = More than one, yes the field is there purely as a reference 🙂

Scanning stock in to putaway in the warehouse, Pure cartons will go to a different location than Mixed so need to flag what the type is each time a Carton ID is entered 🙂

[#6]

@jamesmiller460 

Got it.

In that case, this query will list all of the "Pure" and "Mixed" cartons.

SELECT tbl_Pack_List.TM_ID, Count(tbl_Pack_List.SKU) AS CountofSkusInPack, IIf(Count([SKU])>1,"Mixed","Pure") AS PackType
FROM tbl_Pack_List
GROUP BY tbl_Pack_List.TM_ID
ORDER BY Count(tbl_Pack_List.SKU) DESC;

The table named "tbl_Carton_Type" is not needed at all.

[#7]

@George_Hepworth :

Hi mate, like i say on the form itself, once entering the TM ID i need it too look up against the table and let me know if its a PURE or Mixed Carton... and i need to get it to display on the screen , via a text box to just prompt the user what type of carton it is. 

[#8]

open the main form and see if this is what you need. 

[#9]

Getting there,

So Consignment number and Container ID need to be either from drop down or free typed, then on the sub form im wanting this to be scanned in (via a device) and then each time a new line gets scanned in the SKUs in Carton and Type appear there too (this bit is exactly what I'm after).

[#10]

@jamesmiller460 , you open out tbl_vehicles form. i am not sure what the form is about (for checking of packing list received?)