[AccessD] Lookup Table Maintenance

Mark,

<<Does anyone know of a generalized form with VBA code that allows any and
all
basic lookup tables to be maintained ?>>

  Easy enough to create, but it depends a bit on how you setup the lookups.
Some do a separate table for each type of lookup.  Others will break the
rules a bit and do one lookup table for all the lookups and use a series of
queries to ensure that they only get one type.

 For the first, I would have a table that lists all the lookup tables in the
system and base your combo on that along with a flag whether the lookup type
is user modifiable or not.  Then let the user choose the table they want to
maintain and then set a subforms recordset property to the name of the
table.  Boom your there.  RI is handled automatically (assuming you set that
up).

 For the second case, you just need to base the combo on a type table and
only grab the lookups values that apply to the type chosen:

tblLookupTypes
LookupTypeID - Autonumber - PK
Descr - Text
UserModifiable - Yes/No

tblLookupValues - 
LookupID - Autonumber - PK
LookupTypeID - Long - FK to tblLookupTypes
LookupValue - Text

Or in your case:

tblLookupValues - 
LookupID - Autonumber - PK
LookupTypeID - Long - FK to tblLookupTypes
LookupCode - Text
LookupValue - Text
   

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Tuesday, November 16, 2010 8:06 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Lookup Table Maintenance

Does anyone know of a generalized form with VBA code that allows any and all
basic lookup tables to be maintained ?
Ideally there would be a picklist of all of the lookup tables, and then a
form would open to add, update, or delete the various lookups.
It would apply only to basic referential data having a key and then an
associated data value. Ex: CarBodyTypeCd, CarBodyTypeDesc
CONV CONVERTIBLE
SEDN SEDAN
COUP COUPE
Finally, it would trap any referential integrity violations as well.
 
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com