Trying to Populate Comboboxes with Month & Year Values

[RunTime91]

Greetings All...



I am trying to populate a couple ComboBoxes with Month Year values e.g.,

January 2020
February 2020
March 2020
.
.
.
January 2021

I have the following which gets me close (or maybe not)

But it creates separate instances as opposed to creating a list I can use as a RowSource for ComboBoxes -

Also, when the code wraps back around to January it records it as January 2020 - At that point I need January 2021

Thanks so much as always

Declare @Counter INT = -6
While @Counter <= 6
Begin
SELECT CONCAT(DATENAME(MONTH,DATEADD(M,+@Counter,GETDATE())), ' ', YEAR(GETDATE())) AS Foo
SET @Counter = @Counter + 1;
END

[kd2017]

Here is an example of how you can loop over the dates:

Public Sub test()
    Dim counter As Integer
    
    For counter = 0 To 12
        Debug.Print Format(DateSerial(2020, 1 + counter, 1), "mmmm yyyy")
    Next counter
End Sub

[RunTime91]

Absolutely Brilliant!!

Thanks kd!

[RunTime91]

Ugh!!!

Okay I figured it would be easy enough to move the results of the loop from the Immediate Window and into a RecordSet

At which time I could simply set the RowSource of the combox to the Rs.

Well - here I am so that should tell you how that went...

So now my question becomes - how can I store the results of the loop so I can use those results as the RowSource for my ComboBox.

Below is only the latest variation of my effort to use a RecordSet - I've tried both setting the Rs before and after the loop - Not happening

Can this even be done - ??

The errors I'm getting back are either Foo is not a valid table name (if I use quotations) or that July 2020 not being a valid table name.

    Dim Boo As Integer
    Dim Counter As Integer
    Dim Db As Database
    Dim Rs As Recordset
    
    Set Db = CurrentDb
    
    Boo = Month(Date)
    
    For Counter = Boo - 7 To Boo - 1
        Foo = Format(DateSerial(2020, 1 + Counter, 1), "mmmm yyyy")
    Next Counter
       
      ' Set Rs = CurrentDb.OpenRecordset(Foo, dbOpenSnapshot)
       Set Rs = CurrentDb.OpenRecordset(Foo, dbOpenDynaSet)
    
    Debug.Print Rs

[ssanfu]

So, did you single step through the code?
Do you have

Option Compare Database
Option Explicit

as the first 2 lines in EVERY code module? You should.

OK....... "Foo" is not declared, so you get an error.
Then when you step through the For...Next loop, "Foo" is set equal to the month/year as a string.

You cannot set a text string to a record set. A record set is a query. It has "SELECT field1, field2 FROM MyDomain"...... But a text string is not a domain - a table/query.


You haven't really described what you are trying to do.

You can set the combo box row source to a text string. Maybe this demo will help

[RunTime91]

Okay - Still at this

I am abandoning the idea of a Recordset and switching my focus to trying to create a String.

Perhaps I need both a String & RecordSet??
Below is where I'm at - at the moment - Thank YOu for any help or guidance

    Dim Boo As Integer
    Dim Counter As Integer
    Dim Db As Database
    Dim Rs As Recordset
    Dim Str As String
    
    Set Db = CurrentDb
        
    Boo = Month(Date)
    
    For Counter = Boo - 7 To Boo - 1
        Str = Format(DateSerial(2020, 1 + Counter, 1), "mmmm yyyy")
    Str = Str & vbCrLf & Str
    Debug.Print Str
    Next Counter
       Me.Combo92.RowSource = Str

I hope it is fairly clear what I am trying to do - Build the string upon itself while adding a line break

Yeah - not even close to working - So the question is - Am I on the right path - Or on the wrong planet altogether?

Thanks for any help...

Rt91

[RunTime91]

Hey Steve ~ Didn't see your post ahead of mine - Checking the Demo Db right now

[RunTime91]

First off - Thank You, Steve!!!
Yes. I was missing my options - I started in a Db that had them - then switched to another that did not and neglected to add them - I will set them to default.
Looking at our code - I was 'kind of' on the right path - but still a couple light years away from the solution you provided...

Again, can't Thank You enough - Glad you came, Glad you saw - Really glad you stuck around

[ssanfu]

Now that was funny!


Not really sure where you were headed with the date, so I added the variable "BooYa" for the Year. Later you could have a text box on a form, enter the year, and see the same 6 months, only 1 year earlier.
Anyway, happy to help. Good luck with your project...