Results 1 to 12 of 12
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Combo box row source based on 3 unbound text boxes

    Experts:

    I need some assistance with populating a combo box's row source based on calculated "month" values.

    Attached DB contains a single form "F00_Dates". Upon opening the form, I determine various dates (i.e., last month, current month, and next month).

    The form includes 3 unbound textboxes which (as of 10/08/2021) display "Sep 2021", "Oct 2021", and "Nov 2021".



    Also, I added an unbound combo box. The row source in the combo box thus should be: "Sep 2021", "Oct 2021", and "Nov 2021".

    Naturally, next month, the combo box's row source should be "Oct 2021", "Nov 2021", and "Dec 2021" though.

    How can I link the combo's row source to the 3 individual unbound text boxes?

    Thank you,
    EEH
    Attached Thumbnails Attached Thumbnails Month.JPG  
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just typing while thinking (not a good start) but how about
    Month(Date) == current month
    Month(Date) -1 == previous month
    Month(Date) + 1 = next Month

    You can also use MonthName and Year where needed.
    Just looked at your database.

    Is this what you mean??Click image for larger version. 

Name:	comboTomOct21.jpg 
Views:	24 
Size:	76.6 KB 
ID:	46377

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Orange:

    Thank you... I appreciate the feedback.

    Just clarification purposes... the values for the 3 months are already successfully derived and display individually.

    The challenge is to get those 3 values combined into the drop-down box's row source. Are you suggesting to add these 3 lines to my Sub Form_Load() function? How can I turn them into .... ?

    Code:
    Me.cbo_Month.Rowsource = Me.Month_Previous + Me.Month_Current + Me.Month_Next

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I used this:

    Me.cbo_Month.RowSourceType = "value list"
    Me.cbo_Month.RowSource = Me.Month_Previous & ";" & Me.Month_Current - Current & ";" & Me.Month_Next & ";"

    I found this now using Google


    Using a different Today value Click image for larger version. 

Name:	TomComboDifferentDate.jpg 
Views:	21 
Size:	75.8 KB 
ID:	46378

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Orange:

    Ah... that's works great! Thank you!

    Is it possible to automatically display the 2nd value (i.e., current month)?

    Currently, upon opening the form, ...
    1. The combo box is blank
    2. I then can choose any of the 3 values.

    Instead, I'd like to already display "Oct 2021" when opening the form. Naturally, next month it would show "Nov 2021"... ultimately, it would always be the 2nd value (1st value = previous month; 2nd value = current month; 3rd value = next month).

    Finally, I changed the combo's format to "mmm yyyyy". However, the values are shown as "9/1/2021, 10/1/2021, 11/1/2021". How can I force the format to show, e.g., "Sep/Oct/Nov 2021"?

    Thank you,
    EEH

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Maybe try:
    Code:
    Me.cbo_Month.RowSource = Format(Me.Month_Previous,"mmm yyyy") & ";" & Format(Me.Month_Current,,"mmm yyyy") & ";" & Format(Me.Month_Next,,"mmm yyyy") & ";"
    Me.cbo_Month.DefaultValue=Format(Date(),"mmm yyyy"))
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    thanks for chiming in... 'always appreciate your input. When using the following 3 lines, I'm getting "Run-time error 13. Type mismatch".

    Code:
        'Combo box
        Me.cbo_Month.RowSourceType = "Value List"
        Me.cbo_Month.RowSource = Format(Me.Month_Previous, "mmm yyyy") & ";" & Format(Me.Month_Current, , "mmm yyyy") & ";" & Format(Me.Month_Next, , "mmm yyyy") & ";"
        Me.cbo_Month.DefaultValue = Format(Date, "mmm yyyy")

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    I changed the combo to a listbox and then slightly tweaked the code (see below). It appears it's now working.

    Code:
        Me.lst_Month.RowSourceType = "Value List"
         
        Me.lst_Month.RowSource = Format(Me.Month_Previous, "mmm yyyy") & ";" & _
                                 Format(Me.Month_Current, "mmm yyyy") & ";" & _
                                 Format(Me.Month_Next, "mmm yyyy")
        
        If IsNull(Me.lst_Month) Then
          Me.lst_Month = Me.lst_Month.ItemData(1)
        End If

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Great that you have it working Tom, just curious which line was raising the error.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    For some reason, line "Me.cbo_Month.RowSource = Format(Me.Month_Previous, "mmm yyyy") & ";" & Format(Me.Month_Current, , "mmm yyyy") & ";" & Format(Me.Month_Next, , "mmm yyyy") & ";""
    appeared to cause the error. Once I added the carriage return, it worked fine. Odd, isn't it?

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    @Gicu
    The last two Formats had an extra , in them?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I use a sub for such things called in the OnEnter event of the combo.
    You can adjust the future/past months to display using the arguments.

    Code:
    Public Sub Dates4Combo(Cbo As ComboBox, Optional FutureMonths As Integer = 1, _
                           Optional MonthsToDisplay As Integer = 3, Optional DefaultCurrentMonth As Boolean = True)
    
    
        Dim dte As Date
        Dim i As Integer
    
    
        dte = DateAdd("m", FutureMonths, Date)
    
    
        Cbo.RowSource = ""
        Cbo.Value = Null
    
    
        For i = 1 To MonthsToDisplay
    
    
            Cbo.AddItem Format(dte, "mmm yyyy")
    
    
            dte = DateAdd("m", -1, dte)
    
    
        Next i
    
    
        If DefaultCurrentMonth = True Then
            Cbo.Value = Format(Date, "mmm yyyy")    ' default selected month to current month
        End If
    
    
    End Sub
    Code:
    Private Sub cboMonths_Enter()
        Dates4Combo Me.cboMonths
    End Sub
    Click image for larger version. 

Name:	Screenshot 2021-10-09 071238.jpg 
Views:	15 
Size:	7.3 KB 
ID:	46380
    Code:
    Private Sub cboMonths_Enter()
        Dates4Combo Me.cboMonths, 6, 13
    End Sub
    Click image for larger version. 

Name:	Screenshot 2021-10-09 071703.jpg 
Views:	15 
Size:	15.4 KB 
ID:	46381
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 3
    Last Post: 06-12-2019, 12:20 PM
  2. Replies: 5
    Last Post: 01-07-2019, 01:51 AM
  3. Form based on its combo box & text boxes
    By LonghronJ in forum Queries
    Replies: 7
    Last Post: 11-02-2016, 10:40 AM
  4. Replies: 9
    Last Post: 11-05-2014, 09:23 AM
  5. Replies: 9
    Last Post: 06-04-2014, 10:45 AM

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