2 queries in one form

[TMK]

Please clarify.

Is it the case that you actually have two different "Codes"?

That is what this paragraph suggests.

1. "...the item code is a hardcode[d] id from the factory itself..."
2. "...the serial unique id case comes up only when stock is added to the shelve.."

Please confirm: Is there a manufacturer's code and also your own inventory code?

If so, I assume (hope, actually) that you have two different fields for each of these codes in the Product table.

And further, I assume that you are only struggling with your own, unique, serial ID which you create for each item as it is added to your inventory? The existence of a separate manufacturer's code is, therefore, a distraction, is it not? Or is it somehow involved in your attempt to create and manage unique serial numbers?

As others have suggested, there are multiple ways to handle this, from list boxes to continuous forms to coding solutions.
I am not yet clear on what needs to be done, exactly.

yes two different fields, ill share the sample file i made shortly.
and yes i am having a hard time with my own ID which is created to identify each record created when a new stock is added to inventory

 

[TMK]

heres the sample

 

[Ken Sheridan]

heres the sample

The Product_tbl table should be decomposed by moving the Product_ID, Product_Name and Product_Material columns to distinct rows in a referenced table, ProductType_tbl say, in which Product_ID will be the primary key. The Product_ID column in Product_tbl should be retained as a foreign key, and an enforced relationship created between ProductType_tbl and Product_tbl. If the unit price will always be the same for each ProctType then this column can also be moved to ProductType_tbl. If the price might differ between different sizes, however, then the Product_UnitPrice table should be retained in Product_tbl.

The Product_Actual_Name column should be removed from the Stockin_tbl table as it is functionally determined by Product_Actual_ID, and thus transitively dependant on the key, resulting in the table not being normalised to Third Normal Form (3NF), which requires all non-key columns to be determined solely by the whole of the primary key. At present the table is therefore open to the risk of update anomalies.

With the above amendments the addition of multiple products to stock could be undertaken by means of a multi-select list box, as described in my earlier reply. Note that the list box's RowSouce would be query which joins the ProductType_tbl and Product_tbl tables.

I've attached a copy of a little demo file which illustrates some of the basic methodologies of purchase and supply management. I've added an Add Items to Stock button to the opening form which illustrates the routine I described in my earlier reply.

 

[CJ_London]

i want jus the records only

Use a filter to exclude the records you don’t want or open the form in data entry mode

 

[mike60smart]

heres the sample

Your sample database has a few problems in table design.

1. Product Table
Product_Actual_ID PK set as Text - It is recommended that the PK be set as an Autonumber - LongInteger Datatype
Category field set as a Lookup - You should not use Lookup Fields in Tables

2. StockIn Table
StockIn field set as PK using Number DataType - It is recommended that the PK be set as an Autonumber - LongInteger Datatype
SupplierID - field set as a Lookup - You should not use Lookup Fields in Tables

Relationship between these two tables is wrong.
See the error generated when I try to set Referential Integrity between the tables.

 

[TMK]

The Product_tbl table should be decomposed by moving the Product_ID, Product_Name and Product_Material columns to distinct rows in a referenced table, ProductType_tbl say, in which Product_ID will be the primary key. The Product_ID column in Product_tbl should be retained as a foreign key, and an enforced relationship created between ProductType_tbl and Product_tbl. If the unit price will always be the same for each ProctType then this column can also be moved to ProductType_tbl. If the price might differ between different sizes, however, then the Product_UnitPrice table should be retained in Product_tbl.

The Product_Actual_Name column should be removed from the Stockin_tbl table as it is functionally determined by Product_Actual_ID, and thus transitively dependant on the key, resulting in the table not being normalised to Third Normal Form (3NF), which requires all non-key columns to be determined solely by the whole of the primary key. At present the table is therefore open to the risk of update anomalies.

With the above amendments the addition of multiple products to stock could be undertaken by means of a multi-select list box, as described in my earlier reply. Note that the list box's RowSouce would be query which joins the ProductType_tbl and Product_tbl tables.

I've attached a copy of a little demo file which illustrates some of the basic methodologies of purchase and supply management. I've added an Add Items to Stock button to the opening form which illustrates the routine I described in my earlier reply.

YESS all of those has been done and repeat data is been entered using other tables and theres no need to worry as the only way data is entered to this table is via a form which has all the necessary features to prevent duplications and enter repeated data using dropdowns, i jst didnt want to add the whole file to this, just the necessary data to help with the BULK STOCK IN ONLY

 

[TMK]

The Product_tbl table should be decomposed by moving the Product_ID, Product_Name and Product_Material columns to distinct rows in a referenced table, ProductType_tbl say, in which Product_ID will be the primary key. The Product_ID column in Product_tbl should be retained as a foreign key, and an enforced relationship created between ProductType_tbl and Product_tbl. If the unit price will always be the same for each ProctType then this column can also be moved to ProductType_tbl. If the price might differ between different sizes, however, then the Product_UnitPrice table should be retained in Product_tbl.

The Product_Actual_Name column should be removed from the Stockin_tbl table as it is functionally determined by Product_Actual_ID, and thus transitively dependant on the key, resulting in the table not being normalised to Third Normal Form (3NF), which requires all non-key columns to be determined solely by the whole of the primary key. At present the table is therefore open to the risk of update anomalies.

With the above amendments the addition of multiple products to stock could be undertaken by means of a multi-select list box, as described in my earlier reply. Note that the list box's RowSouce would be query which joins the ProductType_tbl and Product_tbl tables.

I've attached a copy of a little demo file which illustrates some of the basic methodologies of purchase and supply management. I've added an Add Items to Stock button to the opening form which illustrates the routine I described in my earlier reply.

thank you i will go thru and see if it suits the requirements!!!!

 

[TMK]

Use a filter to exclude the records you don’t want or open the form in data entry mode

will try that too, thanks for the tip

 

[TMK]

Your sample database has a few problems in table design.

1. Product Table
Product_Actual_ID PK set as Text - It is recommended that the PK be set as an Autonumber - LongInteger Datatype
Category field set as a Lookup - You should not use Lookup Fields in Tables

2. StockIn Table
StockIn field set as PK using Number DataType - It is recommended that the PK be set as an Autonumber - LongInteger Datatype
SupplierID - field set as a Lookup - You should not use Lookup Fields in Tables

Relationship between these two tables is wrong.
See the error generated when I try to set Referential Integrity between the tables.

yes thanks for tips, but like in my previous message, there is another 18 tables i hav not included here which is solely related to the category table, size tables and material tables, which is why u do get that error in the relationship, otherwise in my actual orginal file all of the relationships work completely fine.

 

[mike60smart]

All bad data must be removed before RI can be enforced.

True but the purpose of the Post was to indicate to the OP what the error was.

 

[mike60smart]

It isn't necessarily that the relationship is "wrong", it is that there is already data in the table that violates the relationship.

True, badly worded on my part. Probably best to say that you cannot set the relationship unless bad data is removed.