Sort/Search Across Forms

[RLN]

Re: Access 2003

I have about 9 forms that are lookup tables for various maintenance of
different codes. Each form has 2-3 fields and each form is based on a
different table, and each table has about 600-800 codes in them (give or take
a hundred or so).

I would like to have sort/search criteria on each form so the user can
locate the codes and their descriptions quicker. Sometimes they'll know the
code only, other times they'll know the description only. Realizing too,
that a lot of code will be in essence 'duplicate' across the forms, is there
a way to write (or call) one sort or search module and use it across all 9 of
these forms, like passing it a table object name? Maybe it would be easier
to have just one form and dynamically place controls on it if the
corresponding table has 2 columns or 3 columns. (?)
I'm thinking out loud here, but am unsure how to code it. (I'm used to
doing it only by unique forms and tables as data sources.)

Suggestions? thnx......

 

[KARL DEWEY]

What about using a union query as row source for a combo?

 

[Klatuu]

Why are there 9 different tables of codes?
It could be legitmate, but I suspect the design could be improved.
Can you describe the differences between these tables?

 

[RLN]

Can you describe the differences between these tables?

The 1st table has Business Unit Numbers and a description field.
The 2nd table has Affiliate codes and a description for those affiliates.
The 3rd table has Account numbers and a unique description for these account
numbers. .....etc...

I have a main input form that has combo boxes to choose a business unit,
account number, affiliate code, etc. Each of these combo boxes has SQL code
behind it to pull specific values from each of those individual tables based
on a region or date, etc...

Hope this helps to clarify.

RLN

 

[Klatuu]

Okay, that is not unreasonable.

Of course, you might consider combining them into one table with a CodeType
field and use queries to return only codes specific to one of the 9 types.

Rereading your original post, I am not sure if you mean you want this
capability on every form, or if you want one form where you can locate and
edit codes from any of the 9 tables.

 

[RLN]

....I am not sure if you mean you want this capability on every form, or if
you want one form where you can locate and edit codes from any of the 9
tables. <<

Right now each of these maintenance tables has their own form. For phase I
of delivering this app I could leave it that way for now, but maybe
consolidate them as a phase II part of requirements.
Regarding ongoing maintenance, I'm all for simplification down the road, so
if you think one form would be the better idea, long term, I'd vote the same.
(Maybe have a combo box on that single form that would let the user choose
which item they want to do maint on ..business unit, account, region,
segment, etc..)

Your thoughts?

 

[Klatuu]

That is how I would do it. I would have one table with a field that
designated the code type. The I would use cascading combos to first select
the type, then the code.

 

[RLN]

I can see your idea here as it it would cut down on the actual number of
tables in the database.

However, the question that raises is this--would having different types of
codes in a single table violate true relational database rules? I thought
having business units, product codes, segments, regions, et all all in the
same table are not advisable because they are all unrelated pieces of data.

I agree your suggestion is a good one. I'm just trying to balance your
suggestion with good database design practices.

 

[Klatuu]

In a purist's world, it may be argued they are not related pieces of data;
however, I would make two arguments. First, they are all codes used in the
database. There could actually be some redundancy if two different code
types used the same code, but even if they were in different tables, the
value would still be replicated. Second, there are times when some
denormalization is reasonable for performance issues. Although I would not
clasify this as incorrect design and therefore not denormalization, if
someone were to make that argument, I would submit that this is a reasonable
case. It is not an uncommon practice. I first learned the technique over 20
years ago when there were severe limitations on the number of files you could
have open at one time.

 

[RLN]

Thanks for your feedback here, I appreciate it. I can see where having one
table with multiple code types would indeed eliminate a lot of code for extra
forms, etc. And yes, I do recall the limitations of days gone by 20+ years
ago when the number of files opened was limited.

I apologize for not checking back here in the discussion forum to reply in a
timely manner. I'm sorry. Having said that, is there a way here in the MSDN
discussion areas when someone replies to my thread I get an email on it? I'm
in my email daily and having that as a reminder would prevent me from being
tardy with my replies, for that is certainly not my intention.

Thanks.

 

[Klatuu]

You can edit your profile and set up where and how you want to be notified.