>Thanks for the ideas. I would like the users to be able to add to the code lists. What's the best way to do this assuming I stick with the one "allcodes" table? Should I keep another table to store the last unique i.d. for each type of code to then increment by 1?? Or just use the main "allcodes" table to sort by codetype+code then increment that code by 1?? I'm beginning to fry my brain on this project ;^).
>
>And Paul, one of the reasons I migrated from the 25 individual tables to a single code table was that I was having trouble creating my data environment for my forms (VFP 3.0). I was getting messages telling me I couldn't add any more tables to the environment (I think I had about 20+ tables per form...to work with my page frames full of data).
>
>Many thanks!
>Mandy
Mandy, I use an ID table with a record for each table in all of my apps. Each record holds an ID value which is incremented when a new record is added to that table. For my "LookUp" table, I use this routine, and the user NEVER sees this field. If I have 3 fields, UniqueID, CodeType (this divides the table into the required categories) and CodeDesc (the actual 'English' description), then I use the UniqueID to store values in my main tables, the CodeType as the limiting parameter in my array-SQL (or cursors, if you go with Tom's ideas) and, of course, CodeDesc is alphabetized for the users to look at.
Most of the time my users add/edit through a "LookUp Editing Screen" where they pick the category (CodeType) and get a list to add/edit/delete. When they're done, I regenerate the arrays. Takes a second or so. If you want this to be a popup linked to your combos, that's possible too. Just be sure you requery() your cursor or regenerate your array.
HTH
Barbara