Results 1 to 4 of 4
  1. #1
    sbogucki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    2

    Using a List Box to Save Multiple Values to a Record, Access 2010

    All,

    I am struggling with a pretty simple issue. I have google-searched pretty thoroughly. While I have discovered solutions, they all contained VBA that was just beyond my ability to effectively manipulate.
    I have intermediate Access skill, but only novice VBA ability. I am using Access 2010, 32 Bit.

    I have a very simple database and I am trying to use an extended list box on a form to allow users to select multiple choices. And then click a command button that saves those choices to a record. I can get the list box to appear, and grant the capability to make multiple selections. But I am struggling to produce the VBA language necessary to translate those choices in a way that they can be saved to the record, and then subsequently saved.

    I tried alternatives including an ill-advised lookup field, but no luck. Here is some additional information:

    I’ve titled my listbox: lstEmployees
    The row source is the EmployeeName field from the tblEmployees


    The control source is the EmployeeName field in tblRecordsMaster which is also the table to which this form (frmEnterEmployee) is bound.

    The basic idea is to allow the user to select several employees. And then to have to those employee names saved to the EmployeeName field in the appropriate record in tblRecordsMaster, preferable separated by commas.

    Is this possible? And, if so, can you please help?

    Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Options:

    Multi-value field - I NEVER use MVF

    or

    VBA code that cycles through the listbox and writes data to table. This can be a delimited string (bad design, about as bad as MVF) or multiple records.


    Should not save names, should save ID.

    What is tblRecordsMaster for? Why is this table the source of names for the listbox as well as the destination for selected data?
    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.

  3. #3
    sbogucki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    2
    Quote Originally Posted by June7 View Post

    VBA code that cycles through the listbox and writes data to table. This can be a delimited string (bad design, about as bad as MVF) or multiple records.


    Should not save names, should save ID.

    What is tblRecordsMaster for? Why is this table the source of names for the listbox as well as the destination for selected data?
    This is the option that I attempting to implement without success.

    tblRecordsMaster is the primary repository of all the records. tblEmployees is a generic table with an ID (primary key), EmployeeName, and EmployeeBranch fields.

    The database contains table of several training courses. The purpose is to choose a single course, and then create records for all the employees that completed the course. An MVF isn't necessary. It will be totally sufficient for the code to loop through and make a new record for each employee selected. I just don't have the programming skill necessary to produce the VBA code that recognizes the multiple selections and writes each to the Records Master table.

    Again, I totally appreciate the help. I'm trying to find the solution in a way that is not only usable in a practical why, but understandable enough to me that it increases my ability. Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looping through a listbox is one way. Post attempted code for analysis. Example at http://stackoverflow.com/questions/2...a-list-box-vba
    Instead of building a string, you would write record to table.
    CurrentDb.Execute "INSERT INTO tblRecordsMaster(EmpID, TngID) VALUES(" & Me.listboxname & ", " & Me.tbxTngID & ")"

    Another is to have a Yes/No field in tblEmployees. Bind checkbox to this field and user can select records in form. Then code in button click event:

    CurrentDb.Execute "INSERT INTO tblRecordsMaster(EmpID, TngID) SELECT EmpID, " & Me.tbxTngID & " FROM tblEmployees"
    CurrentDb.Execute "UPDATE tblEmployees SET Selected=False"

    The real trick is making sure duplicate EmpID/TngID pairs are not allowed. Set those two fields as compound index (allow duplicates No) in tblRecordsMaster.
    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: 18
    Last Post: 07-19-2018, 05:05 PM
  2. Replies: 9
    Last Post: 05-07-2013, 08:37 PM
  3. Save a access 2010 db as 2007 db
    By duncan cameron in forum Access
    Replies: 2
    Last Post: 08-30-2012, 10:37 AM
  4. Replies: 0
    Last Post: 07-31-2012, 12:25 PM
  5. Save Access 2010 as .SQL
    By rthomaskelly in forum Access
    Replies: 1
    Last Post: 04-01-2012, 03:37 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