Results 1 to 1 of 1
  1. #1
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012

    Handling Large Lookup tables, Identical forms, Example of User Defined Type (UDT)

    Click image for larger version. 

Name:	11-8Lookup.png 
Views:	58 
Size:	129.4 KB 
ID:	51006

    Sample DB could be considered useful training for 2 purposes, (1) a method to deal with large lookup tables, and (2) an example of the use and configuration of User Defined Type (UDT) objects.
    Novice developers are advised to try and use the DB with as little modification as possible, especially modification to the VBA code.
    Intermediate developers might find the introduction to UDTs of interest if not already acquainted.
    Expert developers, note that I did my best to balance the tradeoff of complexity vs functionality!

    It takes a lot of words to explain the coding scheme and user interaction, and even more notes are in the VBA comments. Actually using the DB GUI is pretty simple and intuitive, I hope.

    First off, it should be noted that Access limits the number of rows in a list box to not exceed 65535. If your lookup table exceeds that number of rows, the program will run with warnings. It will be your responsibility to ensure proper and expected results, as rigorous testing has not been done by author in such cases.

    The sample database will help when selecting rows from large lookup tables. The lookup forms have a combobox and 2 listboxes. The combobox filters the rows for the left listbox, then manual selections from the left listbox populate the right listbox. The last 2 VBA procedures in the lookup forms show examples of how to use the final rows selected in the right listbox.

    The lookup forms will work WITHOUT CHANGING THE FORM VBA CODE for any properly defined lookup table. Properly defined lookup table means the table(or its query) has a primary key and a field to display in the listboxes. This is a very ordinary requirement for lookup tables in general, nothing special.

    tblConfiguration and frmConfiguration are used to enter and store your lookup table particulars. One record for each lookup table.
    When a lookup form is opened, it is informed via OpenArgs which tblConfiguration record to read and use as its lookup table.

    If more than one lookup form need be opened at once, exact copies of the lookup forms (except for the form name) can be opened, again using OpenArgs to specify the tblConfiguration record for the additional lookup form(s). The sample DB uses this technique. Forms are frmLookup1, frmLookup2 and frmLookup3, and are identical.

    If using multiple lookup forms, never allow 2 or more lookup forms to use the same configuration simultaneously. Data will not be harmed, but data display conflicts will arise between the forms and listboxes.
    The method employed for the form reusability is to include a private User Defined Type (UDT) structure in the lookup forms. The UDT holds the configuration data for the lifetime of the form.

    The sample DB has 3 lookup tables (and 3 queries based on the tables), and 3 (identical) lookup forms, allowing 3 lookup forms to be open at once for different lookup tables. It can be easily modified to use more of fewer functional lookup structures.

    If only one lookup form is anticipated to be open at one time, and you have several lookup tables, then one lookup form will suffice. Just use OpenArgs to tell it what tblConfiguration record to use. Be sure not to open 2 instances of the lookup form simultaneously.

    I was not able to include VERY large tables, as DB would exceed forum upload limits.
    Last edited by davegri; 11-08-2023 at 04:32 PM. Reason: added image, clarif

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-10-2021, 01:28 PM
  2. Compile error User-defined type not defined
    By Ashfaque in forum Modules
    Replies: 8
    Last Post: 03-03-2021, 03:37 AM
  3. Replies: 4
    Last Post: 10-16-2017, 09:09 AM
  4. DAO ERROR - User Defined type not defined
    By adamtate94 in forum Programming
    Replies: 2
    Last Post: 09-08-2016, 05:42 AM
  5. Replies: 3
    Last Post: 11-12-2013, 04:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums