[AccessD] Lookup Table Maintenance

Thanks Jim. I think RI is imperative of course and trapping RI errors ahead
of generic errors is a good idea...
 so that the user plainly understands he/she cannot delete a lookup value in
use.
Finally, Lookup value should be indexed (without dupes) unless this is
maintained programatically.

I am also considering using a table naming convention so picking up the list
of lookup tables from the MSysObjects table. In this fashion, there is no
maintenance when new lookup tables are added.


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Wednesday, November 17, 2010 11:55 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>