search number and open table

[Kirt84]

Hello,

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.

 

[Kirt84]

Hi

Sorry but i'm not quite sure how to do this, please could you explain.

--
Thank you for your help

Once an ECMA is selected, display the corresponding numbers from a query that
searches for:

PKNumber Like [Forms]![MainForm]![ECMA] & "*"

Hello,

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.

 

[Kirt84]

The 'Search' field I have created is the Primary key. The 'ECMA' is the field
where all the numbers are duplicated. E.g 'Search' would have a number
034216186-01, 034216186-02, 034216186-03 etc and the ECMA would be 034216186,
034216186, 034216186 respectively. I have designed the Form so that the user
has to do a Find on the ECMA field. Then all the other fields like Pack size,
Country etc get automatically populated based on that ECMA.

What I want to be able to do is search by the ECMA and where the number is
duplicated it will open a table with all the different types of Product that
exist under that ECMA number and show the numbers in the Search column as
well. If it makes it easier I can use a combo box to perform the Search by
ECMA. The Source of the Form is the Product Details Table.

--
Thank you for your help

Start off with a list of unique product IDs and a list of possible
combinations (product ID and pack size). When a product ID selection is made
in your form (e.g. in a combo box called cboProductID), use the value to look
for product and pack combinations. This can be done with the wildcard search
parameter: [Forms]![MyFormName]![cboProductID] & "*". Otherwise, if you
built your two lists in one table (e.g. the first field is Product ID and the
second field is Product ID & Pack Size), you can simply query the second
field based on matches to the first field.

It's kind of hard to explain unless you give me more information about where
you're at in the process and how you've structured the data. Do you have a
form set up with a combo box for your products or do you simply have a text
box that users will use to enter a search parameter? Do you have a query
based on that control and another control that is fed by the query?

Hi

Sorry but i'm not quite sure how to do this, please could you explain.

Once an ECMA is selected, display the corresponding numbers from a query that
searches for:

[quoted text clipped - 15 lines]

possible the user can select the row and it will automatically drop the
details into the Form. Please help, suggestions would be greatly appreciated.

 

[Kirt84]

Thanks for this!!

I was just wondering if it is possible to perform the Search and a Subform
or Query to open based on that ECMA.
--
Thank you for your help

Can you structure your data like this:

ECMA ECMASearch
034216186 034216186-01
034216186 034216186-02
034216186 034216186-03

BTW, I don't think it's a good idea to name a field simply Search.

Basically, put these fields in one table. I'll assume that there is another
table where ECMA is the PK with more data (Product Details Table?). Make a
form based on this table and create a combo box that lists all ECMA (so that
it is listed only once and you can have other data). While in design mode
for the form, drag and drop the first table (ECMA and ECMASearch) into the
body of the form. Access will help you create a subform. Link the forms
using ECMA, not ECMASearch.

There are other ways to do this and I'm not sure that this is exactly what
you want. I know you've done some work already, but this might give you some
ideas. If you simply want a search box that'll find anything, it'll take a
bit more work and a lot more explaining. For starters, you have to
understand my first response and probably learn a little bit of coding. I'll
be happy to help you but I need a lot more information to be able to give you
specific solutions.

The 'Search' field I have created is the Primary key. The 'ECMA' is the field
where all the numbers are duplicated. E.g 'Search' would have a number
034216186-01, 034216186-02, 034216186-03 etc and the ECMA would be 034216186,
034216186, 034216186 respectively. I have designed the Form so that the user
has to do a Find on the ECMA field. Then all the other fields like Pack size,
Country etc get automatically populated based on that ECMA.

What I want to be able to do is search by the ECMA and where the number is
duplicated it will open a table with all the different types of Product that
exist under that ECMA number and show the numbers in the Search column as
well. If it makes it easier I can use a combo box to perform the Search by
ECMA. The Source of the Form is the Product Details Table.

Start off with a list of unique product IDs and a list of possible
combinations (product ID and pack size). When a product ID selection is made

[quoted text clipped - 20 lines]

possible the user can select the row and it will automatically drop the
details into the Form. Please help, suggestions would be greatly appreciated.