Solved Form recordsource won't update from unbound combobox. (1 Viewer)

JMongi

Member
Local time
Yesterday, 22:27
Joined
Jan 6, 2021
Messages
335
I've decided to use an unbound combobox to select between two queries as recordsources for my form. Here is the code in the AfterUpdate event of the combobox:

Code:
Private Sub Combo20_AfterUpdate()
    If Me.Combo20.Value = "Active List" Then
        Me.RecordSource = "QryProjSummaryActive"
    Else
        Me.RecordSource = "QryProjSummary"
    End If
    Me.Requery
End Sub

I realize the if/then really only works with having two options. I need to rework it for more than 2. But, the main issue is that it doesn't work! Ha! What am I missing?
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:27
Joined
Mar 14, 2017
Messages
4,339
Looks like it ought to work, I use the Recordsource method almost exclusively, and that's what I might have done as well.
What happens? Just no apparent change to the recordsource at all?
 

JMongi

Member
Local time
Yesterday, 22:27
Joined
Jan 6, 2021
Messages
335
Sorry, I did leave that ambiguous. No apparent change. I get a flicker like possibly it reloaded something but no change. I didn't have the requery line in there at first and added it to try and solve the issue. I tested both queries and their records are correct. I wasn't sure if the issue was the "Me" clauses since it's an unbound box.
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:27
Joined
Mar 14, 2017
Messages
4,339
The only other thing I can think of is that you have a misspelling or are mistaken in that it's not changing, honestly, there is nothing wrong with that code that I can see. Maybe try playing in the immediate window with it:
?Form_formname.recordsource [enter]
 

JMongi

Member
Local time
Yesterday, 22:27
Joined
Jan 6, 2021
Messages
335
LOL...definitely time for lunch...
"Active Lists" should be "Active Projects"
*sigh*
 

JMongi

Member
Local time
Yesterday, 22:27
Joined
Jan 6, 2021
Messages
335
To make this less of a waste of time (!) what is a nice succinct way to work with a longer list of options. That will be a thing in a different future form. I'd like to stick with prewritten queries that are chosen by the box as opposed to dynamically generated.
 

Minty

AWF VIP
Local time
Today, 03:27
Joined
Jul 26, 2013
Messages
8,173
Use select case statement;
SQL:
SELECT CASE Me.cboChoices
    CASE "First Option"   
        'DO stuff here'
    CASE "Option Two", "Option Three"
        'Do stuff here'
    CASE "Option Five"
        'Do stuff here'
    CASE ELSE
        'Do final stuff here'
END SELECT
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:27
Joined
Sep 21, 2011
Messages
7,898
When you change recordsource, there is no need for a Requery?

That just means you effectively do it twice. :)
 

Minty

AWF VIP
Local time
Today, 03:27
Joined
Jul 26, 2013
Messages
8,173
The other option is to have a table with the Option name and the name of the query in it, bring that into the combo as a hidden column and refer to it in your code, no need to add to the code at all then.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:27
Joined
Sep 12, 2006
Messages
14,413
after the me.requery you could do

msgbox me.recordsource to check the name of the query.
or even domcd.openquery me.recordsource to view the query.
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:27
Joined
Mar 14, 2017
Messages
4,339
To make this less of a waste of time (!) what is a nice succinct way to work with a longer list of options. That will be a thing in a different future form. I'd like to stick with prewritten queries that are chosen by the box as opposed to dynamically generated.
Glad to hear you got it worked out.

for the longer list, I'd create a table with at least two columns: 1) the friendly name of the query, user-facing 2) the real name of the query.

in the after update event: me.recordsource = me.comboboxname.column(1) or something. (hidden column)

Edit, i guess minty already suggested this - my responses are too slow today
 

Users who are viewing this thread

Top Bottom