Results 1 to 5 of 5
  1. #1
    1961ravi is offline Novice
    Windows 10 Office 365
    Join Date
    May 2023
    Posts
    2

    Getting default entry into a multi select list control in a form

    For example
    Lets say i have a table that I want to populate using a form
    The table Name and fields are:
    StudentSubjectT - Table
    Fields :
    StudentID - Autonumber data type
    StudentName - Short Text data Type


    Subjects - Short Text with a look up display control that allows multiple selection.

    I have another table by name SubjectsT and a single field Subjects of short text type.

    I have designed a form Student_Subjects_EntryF when it opens it will open on a new record.

    I want the form to open with Subjects control box filled in with the last selected subjects for the last student record entered as shown below


    I understand that I would need a VBA code on Form Current event to achieve this.

    Can some one help me with this VBA code.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    The table structure situation you describe should be Many-to-Many. You need a junction table to relate the subjects to the students.
    You should avoid multi-value fields in your tables.
    Here's an example of how it should work:

    Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

  3. #3
    1961ravi is offline Novice
    Windows 10 Office 365
    Join Date
    May 2023
    Posts
    2
    Hi Davegri
    Thanks for your response. I would like to get a solution using multi-select combo
    there should be a way of selecting through VBA code on the form current event based on last entry in that control instead of manually selecting with the mouse.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    1961ravi, your image did not show in your first post. You cannot paste images at this forum, you must upload them using the ribbon icon shown at the top of the message while composing.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    A multi-select combobox is possible when bound to multi-value field. MVF holds links to records of hidden table. If you want to assign default values to a new record, this will require VBA manipulating recordsets to create entries in MVF.

    You could use form AfterUpdate event to assign record ID to a global variable or TempVar or an UNBOUND textbox. Reference that ID in VBA to open recordset for source data and populate MVF of new record. The new record would have to first be committed to table. Whether you do this before or after user enters other data is your decision. Maybe this discussion will help https://stackoverflow.com/questions/...e%22%29.value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 10
    Last Post: 10-26-2019, 12:15 PM
  2. Multi Select List Box on a Search Form
    By Vetgeorge in forum Forms
    Replies: 1
    Last Post: 09-28-2013, 08:11 PM
  3. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  4. Replies: 1
    Last Post: 10-22-2010, 10:11 AM
  5. Replies: 1
    Last Post: 02-25-2009, 07:29 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