Results 1 to 6 of 6
  1. #1
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7

    Can I use a macro to make selections in a list field based on the value of another field?

    I have a database that includes two dropdown menu fields. These fields can have multiple selections, but are limited to the list. One field is filled out for each record, but the other field was just added. I have 500 records, and would like to know if I can run a macro to fill in the new field based on the values checked in the first field. For example, if this is the first field:

    Product Lines
    X Hotel
    O Multihousing
    O Door Closers
    O E-Plex
    X Levers and Handles


    O Simplex
    O Keyscan
    O RCI

    The macro should see that "Hotel" and "Levers and Handles" are both checked, and check the boxes in the list in field 2 that correspond with those product lines. Is this possible, or will I have to go through and check the boxes for each record myself?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this is why you should NEVER use check fields like this.
    The wrong way for a table design (checkboxs):
    bob smith, hotel, multihouse,door closers, etc.

    because EVERYTIME you change the values, all checkboxes must be altered on tables and forms etc.

    the CORRECT table design is to use text values (not checkboxes). Then to add /change, just put a new entry in the look list:
    123 =bob smith, in the tPerson table

    tChosenTable:
    123, hotel
    123, multihouse,
    123, door closers,

    no changing of forms/reports/tables. Only 1 value is added to 1 table.

  3. #3
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7
    These values don't change. The product lines include definite product types that will not be altered, so is there any way to relate the two?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds like a multi-value lookup field, not checkboxes, but I think ranman's advice is still valid. Most of us won't use multi-value fields; we use a related table as ranman describes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7
    I don't have reports, tables, etc. It's just a very simple database that lists contacts and what they sell.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I never use Multi-value field, calculated fields or Look up fields in tables.


    Having said that, yes, you can use VBA code (but not a macro) to update the MVFs. It will take a good understanding of Multi-value fields.

    Start with these links:
    https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
    https://bytes.com/topic/access/answe...ivalued-fields
    http://www.utteraccess.com/forum/lof.../t1944468.html


    It would be much simpler with a properly normalized structure.

    Good luck with your project.

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

Similar Threads

  1. Replies: 5
    Last Post: 12-07-2016, 09:28 PM
  2. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  3. Replies: 4
    Last Post: 08-25-2013, 07:43 AM
  4. Replies: 8
    Last Post: 03-19-2012, 08:50 PM
  5. Make new field based on previous field's answer
    By VictoriaAlbert in forum Access
    Replies: 1
    Last Post: 04-11-2011, 09:54 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