Results 1 to 4 of 4
  1. #1
    micahbecca is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    2

    Loop through list of true values


    Hi everybody,
    I've got a head scratcher. I have a form with 50 combo boxes on it that users can select "Yes" to or leave blank. In vba I need to get a "list" of all the "Yes" values that I can assign a text value to to be looped through an SAP script. I considered using a record set but I'm having a difficult time getting the concept right. Any ideas? I could use some guidance.

    Thank you!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Before proceeding, are these just Yes/No values? If so, a checkbox is much more suitable.
    And, what kind of list? Table? Message box? Report? Once you have the recordset, I have no idea what to do with it.

    EDIT:
    A table is probably the best bet. A recordset residing only in memory is not only more difficult to produce, you can't store and query it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    micahbecca is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    2
    Thanks Micron. They are either "Yes" or blank...my brain prefers the text to the 0 or -1.

    The responses are located in a table. The boxes are all titled Box1XX (where the XX) is 01 to 50. I know I can do this by writing 50 IF blocks but logic tells me there must be a better way.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, the unchecked value for a checkbox is either 0, False or No. Take your pick. Beats having to select from a list, but since you didn't ask, I won't say it (oops! too late)
    Something like this, perhaps? NOTE - untested 'air code'. Use on table copy to test. It's supposed to add a record to a table with 1 field. If your table has more, change (0) to the zero based index of the field, or its name - !Fields("fldName")

    Code:
    Dim ctl As Control
    Dim rst as DAO.Recordset
    
    Set rs = Currentdb.OpenRecordset ("tblName", dbOpenTable)
    
    For each ctl in Me.Controls
     If ctl.Type = acCombobox Then
      If Not IsNull(ctl) Then 
        With rs
         .AddNew
         !Fields(0) = ctl
         .Update
        End With
       End If
      End If
    Next
    
    rs.Close
    Set rs = Nothing
    I'm assuming you can decide on the appropriate method of calling the procedure (which I didn't name) and/or what event to put it in. Also assuming you have or can create a reference to DAO. Hope you are preventing users from adding anything to your combo list. I didn't include an error handler. If you need the table to start blank, execute a delete query or sql statement in the code before opening the rs.
    Last edited by Micron; 05-19-2017 at 10:38 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 9
    Last Post: 05-08-2015, 02:36 PM
  2. Error During Loop For Updating True/False
    By Levonas in forum Programming
    Replies: 10
    Last Post: 03-06-2015, 12:21 PM
  3. Replies: 8
    Last Post: 10-06-2014, 03:02 PM
  4. Extracting TRUE values only
    By kloun04 in forum Queries
    Replies: 3
    Last Post: 06-20-2014, 11:23 AM
  5. Replies: 4
    Last Post: 02-05-2014, 12:17 PM

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