Find a record in a Form -Access 2003

[cbianchi]

Hello
I have a form based on a table which has 100 records and 18 fields the first
of which is a "ID" field. Because each record represents one code(second
field) and all its own alternatives codes,I should be able to retrieve the
complete record, starting from any value listed in the table. I have tried by
a combo using the wizard ; it works but the combo or includes just one of
the fields so in many cases the searched value is in another field or, if I
add other fields,becomes useless because it is quite to read the table itself.
Is there the possibility in a text box or similar to type the known value
and get the complete record?

thanks for your attention

 

[John W. Vinson]

Hello
I have a form based on a table which has 100 records and 18 fields the first
of which is a "ID" field. Because each record represents one code(second
field) and all its own alternatives codes,I should be able to retrieve the
complete record, starting from any value listed in the table. I have tried by
a combo using the wizard ; it works but the combo or includes just one of
the fields so in many cases the searched value is in another field or, if I
add other fields,becomes useless because it is quite to read the table itself.
Is there the possibility in a text box or similar to type the known value
and get the complete record?

thanks for your attention

If you need to look in multiple fields to find the code... your table
structure may need reconsidering. What are the fields in your table? What are
these "alternative codes", where are they stored, and why?

You might want to consider a one to many setup with two tables: the second
table would contain the ID (non-unique) and the alternative code. If a given
record had a dozen alternative codes, you'ld have a dozen records - looking up
any one would give you the ID.


John W. Vinson [MVP]

 

[cbianchi]

Hello John
the table structure is :
-field ID: autonumber (indexed as primary Key)
-field "Original Code": text (typically 22-1032-310)
-field "Alt_Code_1": text (typically 22-1072-310)
-field"Alt_Code_2": text (typically 22-1092-310)
same till to Alt_Code 8.
-Field "Lenght": numeric
-Field"Width": numeric
-Field "Quantity": numeric

The form is than just including all the fields of the above mentioned table.
I receive by phone requests to check the availability of one code that it
could be the "original" or one of the "alternatives". That the reason why I
should be able to type the requested code and find in the form the complete
recorset which shows the requested code,the original and all the infos stored
in the record.
Thank you very much for your attention and be patient because I am just
facing Access so It could be I don't understand easily what you mean.

cbianchi

 

[John W. Vinson]

Hello John
the table structure is :
-field ID: autonumber (indexed as primary Key)
-field "Original Code": text (typically 22-1032-310)
-field "Alt_Code_1": text (typically 22-1072-310)
-field"Alt_Code_2": text (typically 22-1092-310)
same till to Alt_Code 8.
-Field "Lenght": numeric
-Field"Width": numeric
-Field "Quantity": numeric

The form is than just including all the fields of the above mentioned table.

And you will NEVER, ever, under any concievable combination of circumstances,
need an Alt_Code_9? If you do, you'll need to redesign your table, redesign
your form, redesign all your queries, rewrite your code... ouch.

I receive by phone requests to check the availability of one code that it
could be the "original" or one of the "alternatives". That the reason why I
should be able to type the requested code and find in the form the complete
recorset which shows the requested code,the original and all the infos stored
in the record.
Thank you very much for your attention and be patient because I am just
facing Access so It could be I don't understand easily what you mean.

A better design would be:

Products
ProductID <Autonumber, Primary Key>
OriginalCode <Text, with a unique index; you could if you wish omit the
autonumber and use OriginalCode as the primary key provided it's unique within
the table>
Length
Width
Quantity

AltCodes
ProductID <Long Integer link to Products; or you could use a text
OriginalCode if that's the PK>
AltCode <Text>


If product 22-1032-310 has two alternative codes, table AltCodes would have
two records:

OriginalCode AltCode
22-1032-310 22-1072-310
22-1032-310 22-1092-310

You can add two, or eight, or nineteen records to this table, and have only
one field to search for alt codes. You could even include a record with the
original code twice (so you only have one place to look).


John W. Vinson [MVP]

 

[cbianchi]

Hi John

I have understood your way to redesign the actual one single table spliting
it into to separate tables. What surprised me is that in the AltCodes table I
will have two field where inside the "Original code field" I will have
several times repeated the same code. In fact if the original code has min. 3
alternatives to max 8 alternatives I will have conseguentely from 3 to 8
records with tha same original code.Because I am reading an Access manual
where it is written that the beginners like me make often the mistake to
duplicate too much the data I would never have considered this approach.
Now that I have clear how to design tha tables it remains unclear how to
make the form and the search. Should I make a form based on both tables or
make a form with a subform for the AltCodes ? What about the search?
Rememeber that I will be asked to search for a code for which i don't know if
it is the original or one of the alternatives and I would simply type
somewhere tha required code having back the original plus all its
alternatives.
Thank you very much for your help and I want to take this occasion to wish
you an Happy New Year.
Cesare Bianchi

 

[John W. Vinson]

Hi John

I have understood your way to redesign the actual one single table spliting
it into to separate tables. What surprised me is that in the AltCodes table I
will have two field where inside the "Original code field" I will have
several times repeated the same code. In fact if the original code has min. 3
alternatives to max 8 alternatives I will have conseguentely from 3 to 8
records with tha same original code.Because I am reading an Access manual
where it is written that the beginners like me make often the mistake to
duplicate too much the data I would never have considered this approach.

The repeated field here is what's called a "foreign key" - and it's perfectly
appropriate, even essential, to repeat it. There are eight repeats because
there are eight distinct "facts" (relationships between OriginalCode and
AlternativeCode) stored in your table.

Now that I have clear how to design tha tables it remains unclear how to
make the form and the search. Should I make a form based on both tables or
make a form with a subform for the AltCodes ? What about the search?
Rememeber that I will be asked to search for a code for which i don't know if
it is the original or one of the alternatives and I would simply type
somewhere tha required code having back the original plus all its
alternatives.

For data entry... by all means, a Form (based on the main table) with a
Subform (based on the alternative codes table).

For searching, if you have fewer than 65536 distinct values, I'd suggest
simply basing a combo box on the alternative codes table. Make the alternative
code the first visible column, and the OriginalCode the bound column. Use the
combo box option "use this combo to find a record". This will allow the user
to simply type the first few characters of the code, and it will jump to that
record; when they select it, the form will open to the record for that
original code.

Thank you very much for your help and I want to take this occasion to wish
you an Happy New Year.

Thanks, and hope yours is happy too!

John W. Vinson [MVP]

 

[cbianchi]

Hello John

I did what you have suggested and it works perfectly.
Many thanks for your courtesy and the support.

c. bianchi