Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value list for combo box set in code (AXP)

    Hello All,
    I need to set the values for a combo box row source using a calculation. Basically, I have a date and need to use that date to list a possibility of seven dates in the drop down list. For instance I will use the date as the first value, then date+1 as the second, date+2 as the third and so on up to seven possible days. How can I do this in VBA code?

    Thanks,
    Mark

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value list for combo box set in code (AXP)

    Hi Mark,
    You can write a loop in code like this: <pre>Private Sub Form_Open (Cancel as Integer)
    Dim strRowSource as String
    Dim intCounter as Integer
    Dim dteMyDate as Date
    dteMyDate = Format(Now(),"Short Date")

    For intCounter = 1 to 7
    strRowSource = strRowSource & ";" & DateAdd("d",1,dteMyDate)
    Next
    'The Right function gets rid of the opening ";"
    cboDate.RowSource = Right(strRowSource,Len(strRowSource) - 1)
    End Sub</pre>

    Hope this helps!

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Value list for combo box set in code (AXP)

    <pre>Private Sub Form_Open (Cancel as Integer)
    Dim strRowSource as String
    Dim intCounter as Integer
    Dim dteMyDate as Date
    dteMyDate = Format(Now(),"Short Date")
    For intCounter = 1 to 7
    strRowSource = strRowSource & ";" & DateAdd("d",1,dteMyDate)
    Next 'The Right function gets rid of the opening ";"
    cboDate.RowSource = Right(strRowSource,Len(strRowSource) - 1)
    End Sub</pre>


    Mark,

    What you showed is quite correct, I've just got a few of points you might want to consider.

    I'd get in the habit of using Date() rather than Now() when you just want a date. In many cases it doesn't make a difference (as above), but if you were actually using the results in a comparison, then the Time component of Now() might cause a problem for you.

    Another way to get rid of that pesky leading ";" is to declare your variable as variant (which initializes as Null), giving you this:
    varRowSource = varRowSource + ";" & DateAdd("d",1,dteMyDate)
    The use of "+" means that the varRowSource + ";" will be Null if varRowSource is Null.

    And finally, when you want to get rid of certain leading characters, you might find it easier to use the Mid function, as in:
    cboDate.RowSource = Mid(strRowSource,2)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Value list for combo box set in code (AXP)

    In addition to the methods already described, comboboxes in AXP have an AddItem method like combox in VB and the rest of Office so you can do something like this:

    <pre> With cbo
    .AddItem "Date1"
    .AddItem "Date2"
    .AddItem "Date3"
    End With</pre>


    Where Date1, etc., are strings containing the formated dates you want to use. So all you have to do is create code to generate the subsequent date values and use AddItem to add them to your list. No need to concatenate a string of values.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value list for combo box set in code (AXP)

    Thanks Mark,

    I guess we all fall into a routine of doing things a certain way. I'm always looking for new ways to improve my technique. Your tips have been most helpful!

    I'll work on integrating this into my coding practice.

    Thanks <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value list for combo box set in code (AXP)

    Thanks Everybody,
    Charlotte wins, it is the easiest to make work! However, I have wanted to do this for a long time and the other way is great information as well.

    Thanks,.
    Mark

Posting Permissions

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