Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: Writing new records from a multiselect listbox

    I could sure use some help...I have some cursory knowledge of vb but I'm not a programmer....I'm building a form that collects data from 2 tables (policytable & Membership Table) and 2 orphan fields (Date created & Date Target). Using this 3 criteria data, I'd like to add a new record to 'Test Tracking' table for each member selected in the multiselect membership listbox.

    For example:
    The 1st criteria provides a listbox that you select the policy name which also pulls the policy ID from the 'Policy Table' table.
    The 2nd criteria asks you who you want to receive the policy from a (multiselect) listbox of members
    The 3rd criteria is to capture todays date in 1 orphan field and a date 2 weeks forward in the other orphan field.

    Once I have these criteria, I'd like to add a new record to the 'Test Tracking' table for each member selected. Anything anyone can do to help me would be so much appreciated.

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    I don't know of a way to do it without knowing how to code in VBA. The way I would do it is to add a button on the form that when the user clicks it it would create the records. You need to add a procedure to the button's On_Click event and some code that is similar to:

       Dim varPerson as Variant
       Dim strPerson as String
        For Each varPerson In lstPeople.ItemsSelected
             strPerson = lstPeople.ItemData(varPerson)
             strSQL = "INSERT INTO [Test Tracking] ..."
             Docmd. RunSQL strSQL
        Next varPerson
    This code will not work for you. It assumes your listbox is called lstPeople and I am not sure what the SQL should be to insert the records because I don't know all of the field names that you have created. The key is the ItemsSelected collection. This collection stores all of the people the user selected. The For Each loop loops through each selection and appends a new record to the table Test Tracking.

    As a side note... You should try to avoid having spaces in your table names. They cause some annoying issues when you are trying to reference the tables in code. That is why I had to add the [] around the table name. You probably would want to use a naming convention like tblTestTracking. The first three letters tell programmers what type of object (Table) you are referencing.
    Last edited by DCKunkle; 09-25-09 at 18:01.

  3. #3
    Join Date
    Sep 2009
    Thank you DCKunkle for your reply. I'm excited to insert and modify. This gives me the code parameters to start with. I have some experience with vba but it doesn't run deep (yet). I'll plug it in and see what I see. I appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts