Results 1 to 7 of 7
  1. #1
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115

    Stripping values out of a field to create Combo box rows

    Hi. I am using a 3rd party system and writing dome extra database forms. It is sorting values such as colour choices or brands in a single field in a global "variable table". So each variable is a single row and the choices stored in a single field. For example:

    NONE|DARKS|GREY|WHITE|CREAM-NATURAL|TAUPE|BROWN|TAN|GREEN|YELLOW|ORANGE|BLUE|L IGHT-BLUE|LILAC-PURPLE|RED|PINK
    I need to use these values as a row source for a combo box in a form.

    I can retrieve the values (above) using the query:

    Code:
    SELECT Variable.sValueRange FROM Variable Where sName = 'colour';
    However it just populates the entire sequence into 1 row of the combo box.



    I was hoping someone might know how to work some magic to get the individual colours into separate rows?

    Many thanks

    tony

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    Import the data here by using this sub to parse it , then break it up into records for your combo....

    Code:
    Sub breakup()
    Dim vWord,vLine
    
    open "c:\folder\file.txt" for input as #1
    Line Input 1, vLine
    i= instr(vLine,"|")
    while I > 0
       VWord =left(vLine,I-1)
       VLine=mid(vLine,I+1)
    
        Docmd.runSql "insert into tColours (colour) values ('" & vWord & "')"
    i= instr(vLine,"|") 
    wend
    Docmd.runSql "insert into tColours (colour) values ('" & vLine & "')"
    close 1
    end sub
    now your colors are in the table as should be.

  3. #3
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    Quote Originally Posted by ranman256 View Post
    Import the data here by using this sub to parse it , then break it up into records for your combo....

    Code:
    Sub breakup()
    Dim vWord,vLine
    
    open "c:\folder\file.txt" for input as #1
    Line Input 1, vLine
    i= instr(vLine,"|")
    while I > 0
       VWord =left(vLine,I-1)
       VLine=mid(vLine,I+1)
    
        Docmd.runSql "insert into tColours (colour) values ('" & vWord & "')"
    i= instr(vLine,"|") 
    wend
    Docmd.runSql "insert into tColours (colour) values ('" & vLine & "')"
    close 1
    end sub
    now your colours are in the table as should be.
    Thank you again for your help. Unfortunately I am being a bit thick.

    I get the sub breaking up the line into the elements and I have that working.

    i am confused about the
    Code:
    Docmd.runSql
    lines. Is this to create a table that contains the elements converted into rows? Why does vLine need adding to it?

    I was wondering if I could just put them into a
    Code:
    DAO.Recordset
    and use that to populate the combo-box using:

    Code:
    Set Me.ColourCombo.Recordset = ColoursRecordset
    or even just pass in a comma separate list of values stored in a string?

    I have not managed to get either to work. I am wondering if my methods are not possible?

    Many thanks

    Tony

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Create a table (or use a current table and modify it) named "tblgVariables".
    It should have 3 fields:
    VariableID_PK Autonunber (PK) (I always add an autonumber in my table)
    ValueRange TEXT (data to be used in combo box)
    OptionName TEXT (option to be looked up - ex. Colour, Brand, etc)

    The Row Source property of the combo box for Colours would be
    Code:
    SELECT tblgVariables.ValueRange
    FROM tblgVariables
    WHERE (((tblgVariables.OptionName)="Colour"))
    ORDER BY tblgVariables.ValueRange;

    The Row Source property of the combo box for Brands would be
    Code:
    SELECT tblgVariables.ValueRange
    FROM tblgVariables
    WHERE (((tblgVariables.OptionName)="Brand"))
    ORDER BY tblgVariables.ValueRange;
    (change the field names to what you want. )
    Attached Files Attached Files

  5. #5
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    Many thanks

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    or you could just use the replace function to populate a valuelist - something like

    dim rst as dao.recordset
    set rst=currentdb.openrecordset("SELECT Variable.sValueRange FROM Variable Where sName = 'colour'")
    mycombo.rowsource=replace(rst.fields(0),"/",",")
    set rst=nothing

    and set the combo rowsource type to 'value list'

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    The .RunSQL adds the data to the table. No recordset is needed.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-16-2016, 03:10 PM
  2. Replies: 2
    Last Post: 06-01-2015, 01:21 PM
  3. Replies: 6
    Last Post: 02-14-2015, 05:40 PM
  4. Replies: 3
    Last Post: 03-24-2014, 08:17 AM
  5. create rows from field
    By cm-net in forum Queries
    Replies: 1
    Last Post: 01-24-2011, 02:05 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