search number and open table

[Kirt84]

Hi,
I posted this problem on Fri. but I did not get a proper solution. Please
could someone help..........


This is quite a tricky problem and was wondering if there is a solution to
this. I have a field called ECMA which is a Product identification number. As
an example we’ll use the number 028834051. The problem is that a product can
have the same identification number but different pack sizes. Therefore, I
have created a unique Primary Key field called ‘Search’, which display the as
numbers as 028834051-01, 028834051-02, 028834051-03 etc.

Here is where it gets difficult – On my Form I want to be able to Search by
ECMA e.g. 028834051 and then a List/Table to display all the numbers (if any)
and their pack sizes – 028834051-01, 028834051-02, 028834051-03 etc. And if
possible the user can select the row and it will automatically drop the
details into the Form. Please help, suggestions would be greatly appreciated.

 

[KARL DEWEY]

I would recommend using a compound primary key or multiple field key. One
field for the ECMA and a second field for your size indicator. In table
design view highlight both field before clicking on the key icon.

 

[John Vinson]

This is quite a tricky problem and was wondering if there is a solution to
this. I have a field called ECMA which is a Product identification number. As
an example we’ll use the number 028834051. The problem is that a product can
have the same identification number but different pack sizes. Therefore, I
have created a unique Primary Key field called ‘Search’, which display the as
numbers as 028834051-01, 028834051-02, 028834051-03 etc.

That's your mistake: storing two disparate items of information (a
ProductID and a PackSize) in one field.

These should be two different fields. They can be a joint, two-field
Primary Key if you wish - select both fields in table design view and
click the Key icon.

You could use two combo boxes, based on the ProductID and PackSize
fields respectively; for the pack size combo, use a Query referencing
the form control containing the ProductID as a criterion:

SELECT PackSize
FROM Products
WHERE Products.ProductID = [Forms]![MyForm}![cboProductID]
ORDER BY PackSize;

to show only the packs for that particular product.

John W. Vinson[MVP]

 

[Kirt84]

The ID and pack size are already different fields. The 'ECMA' is the
duplicated field and the 'Search' is the Primary key which has an -01, -02,
-03 at the end to make the field unique.
--
Thank you for your help

This is quite a tricky problem and was wondering if there is a solution to
this. I have a field called ECMA which is a Product identification number. As
an example we’ll use the number 028834051. The problem is that a product can
have the same identification number but different pack sizes. Therefore, I
have created a unique Primary Key field called ‘Search’, which display the as
numbers as 028834051-01, 028834051-02, 028834051-03 etc.

That's your mistake: storing two disparate items of information (a
ProductID and a PackSize) in one field.

These should be two different fields. They can be a joint, two-field
Primary Key if you wish - select both fields in table design view and
click the Key icon.

You could use two combo boxes, based on the ProductID and PackSize
fields respectively; for the pack size combo, use a Query referencing
the form control containing the ProductID as a criterion:

SELECT PackSize
FROM Products
WHERE Products.ProductID = [Forms]![MyForm}![cboProductID]
ORDER BY PackSize;

to show only the packs for that particular product.

John W. Vinson[MVP]

 

[John Vinson]

The ID and pack size are already different fields. The 'ECMA' is the
duplicated field and the 'Search' is the Primary key which has an -01, -02,
-03 at the end to make the field unique.

A Primary Key can consist of one field... two fields... even ten
fields.

I would still very strongly suggest a) renaming the field, as
suggested and b) making it into two fields, EMCA and this additional
field. You can concatenate them for display and make the two of them a
joint, two-field primary key.

John W. Vinson[MVP]