Solved Problem with Rowsource in Combobox of Continuous Form. (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 19:48
Joined
Apr 1, 2019
Messages
731
Friends, So I have a combobox 'ContactPurpose' on a bound continuous subform. I wish to populate the combobox with all records except when the user enters a new record, in which case I wish to restrict their selection to only 'Active' records. So, i have 2 select queries as below on the Form_Current event. One rowsource displays all records & the other displays the 'active' records only. My problem is that upon entering a new record, the first entered record does not display it's contents until the user 'loses focus' on that record.

I hope I've made sense & someone can shed some light on my issue. Cheers.

Code:
Private Sub Form_Current()

Me.ContactPurpose.RowSourceType = "table/query"

If Me.NewRecord Then ' that is check that the rowsource record is active
    
    Me.ContactPurpose.RowSource = "SELECT ContactPurposeID, ContactPurpose, RelationshipTypeID " & _
    "FROM tluContactPurpose " & _
    "WHERE ((RelationshipTypeID)=[txtgroup]) AND (([Active(Y/N)])=Yes) " & _
   "ORDER BY ContactPurpose" '
    
                   'Me.ContactPurpose.Requery
          
    Else ' show every previously selected record
    Me.ContactPurpose.RowSource = "SELECT ContactPurposeID, ContactPurpose,RelationshipTypeID " & _
    "FROM tluContactPurpose " & _
    "WHERE ((RelationshipTypeID)=[txtgroup]) " & _
    "ORDER BY ContactPurpose"
    
     'Me.ContactPurpose.Requery
          
    End If
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:48
Joined
Feb 28, 2001
Messages
27,383
You only have the requery in one leg (the ELSE leg) of your IF/THEN/ELSE fork yet you "diddle" with the .RowSource on both sides of the fork. I would expect awkward and apparently unsynchronized behavior in that situation.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
there is only 1 combobox on continuous form, so it will propagate to the rest of the record on the form.
what you need to do is add some dummy textbox and "overlay" it on top of the combobox, the source of the unbound textbox is the value of
ContactPurpose.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 19:48
Joined
Apr 1, 2019
Messages
731
Gents, thanks for the prompt replies. I figured what @arnelgp deduced. @arnelgp do you know of a demo database that displays this technique? Appreciate your help as always.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
upload a db with tluContactPurpose on it then I will show you how or
just open the table, select all records, then copy/ paste to this screen so i see the content.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
..anyway here is a demo. open DemoForm.
"on top" of ContactPurposeID combobox is a dummy textbox (txtDummy). see its controlsource.
 

Attachments

  • comboOnContinuous.accdb
    800 KB · Views: 41

Babycat

Member
Local time
Today, 13:48
Joined
Mar 31, 2020
Messages
275
Just to fix Arnelgp's demo overlay issue. Bring txtDummy to forefront, set its background to normal (not transparent).
 

Attachments

  • 1.JPG
    1.JPG
    32.9 KB · Views: 26

Babycat

Member
Local time
Today, 13:48
Joined
Mar 31, 2020
Messages
275
..anyway here is a demo. open DemoForm.
"on top" of ContactPurposeID combobox is a dummy textbox (txtDummy). see its controlsource.
Hi Arnelgp

What if ContactPurpose combox's record source depends on selected value of Group?
i.e:
Group = A then ContactPurpose dropdown list = A1, A2, A3
Group = B then ContactPurpose dropdown list = B1, B2, B3, B4

Any idea please?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
BabyCat, you try again this demo.
 

Attachments

  • comboOnContinuous.accdb
    868 KB · Views: 36

Babycat

Member
Local time
Today, 13:48
Joined
Mar 31, 2020
Messages
275
Hi Arnelgp
It works great but for new record only.
If user changes Group of exising record, Subgroup does not filter out its rowsource.

1710670123086.png
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
that was the original request on post #1.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 19:48
Joined
Apr 1, 2019
Messages
731
Gents, thanks for your input. Will have a go over the next couple of days & let you know how i get on.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
babyCat, this demo is for you.
 

Attachments

  • babyCombo.accdb
    868 KB · Views: 49

CJ_London

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2013
Messages
16,704
If user changes Group of exising record, Subgroup does not filter out its rowsource.

this sounds like what is called cascading combo's - see this link for an example


you need to use the combo enter/exit or got/lost focus events to modify the combo rowsource e.g.

Private Sub ContactPurpose_Enter()
If Me.NewRecord Then ' that is check that the rowsource record is active

Me.ContactPurpose.RowSource = "SELECT ContactPurposeID, ContactPurpose, RelationshipTypeID " & _
"FROM tluContactPurpose " & _
"WHERE ((RelationshipTypeID)=[txtgroup]) AND (([Active(Y/N)])=Yes) " & _
"ORDER BY ContactPurpose" '
end if

End Sub

Private Sub ContactPurpose_Exit()

If Me.NewRecord Then' show every previously selected record
Me.ContactPurpose.RowSource = "SELECT ContactPurposeID, ContactPurpose,RelationshipTypeID " & _
"FROM tluContactPurpose " & _
"WHERE ((RelationshipTypeID)=[txtgroup]) " & _
"ORDER BY ContactPurpose"

end if

end sub
 

Babycat

Member
Local time
Today, 13:48
Joined
Mar 31, 2020
Messages
275
Thank Arnelgp and CJ_London

The demo of Arnelgp works like a champ. That is what i have been looking for druing last 2 weeks. I stucked there, in fact I have to build a popup form instead of using cacading combo's.

CJ_London's demo is even better, I will study carefully. Very happy now, thank dudes!
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 19:48
Joined
Apr 1, 2019
Messages
731
@arnelgp , I downloaded your demo & yes, I can work with this. Exactly what I meant. A clever work around. Thanks a lot.
 

Ivy

New member
Local time
Today, 08:48
Joined
Apr 25, 2024
Messages
18
babyCat, this demo is for you.

Hi arnelgp,

I have a little adapted your cascade combobox sample to my project, basically I just removed the Active Y/N condition and it works like a charm, you are great!!

However I wanted to ask you for two pieces of information, if it can obviously be done:
1) Does every Relationship necessarily have a corresponding ContactPurpose? because I have some data that doesn't have a subgroup, so I would like to leave the second field blank on purpose, if selected.
2) If in the future I want to do a search to find out, for example, how many B1s are in the database, will it be feasible?

Thank you very much and thanks again for the sample
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:48
Joined
May 7, 2009
Messages
19,248
sorry, which form? and what Group does not have a subgroup?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:48
Joined
May 21, 2018
Messages
8,643
CarrieraTbl CarrieraTbl

IDcarrieraIDdipendenteDataInizioCarrDataFineCarrIDtipocarrieraselezionaIDtipocarrieravalori
7​
1868​
10​
9​
1868​
18​
50​
13​
3467​
19​
14​
14​
4364​
8​
35​
15​
4364​
8​
29​
18​
1868​
8​
19​
In your table you are storing both
the group: IDtipocarrieraseleziona
and the selection from the group; IDtipocarrieravalori

Normally you would not do this because it is not normalized. The relationship to the Group is through the selection. You would only store IDtipocarrieravalori. You know the group from

TipoCarrieraValoriTbl TipoCarrieraValoriTbl

IDtipocarrieravaloriTipoCarrieraValoriIDtipocarrieraselezione
16​
Assenza per espletamento visite, terapie, diagnosi
8​
19​
Aspettativa per donne vittime di violenza di genere art.24 D.Lgs. 80/2015
8​
20​
Aspettativa per volontariato emergenze e Protezione Civile
8​
22​
Aspettativa per permanenza in stato straniero (adozione/affidamento) art.27 c.2 D.Lgs. 151/2001
8​
23​
Aspettativa per richiamo alle armi/CRI
8​
25​
Aspettativa per conferimento incarico dirigenziale ex art.15 septies D.Lgs 502/92
8​
26​
Aspettativa per incarico di ricercatore universitario art. 24, c.9bis L. 240/2010
8​
You can still do cascading combos even if you do not store the group. The group combo would be unbound. See the Northwind2 example in the other thread that does that.

In your case you want a Generic group without subgroup. If it was me I would duplicate the group value as a generic selection in the subgroup.

For example this group has no subgroups.
TipoCarrieraSelezionaTbl TipoCarrieraSelezionaTbl

IDtipocarrieraselezioneTipocarrieraseleziona
10​
Transione di genere
In my subgroup table I would do this
TipoCarrieraValoriTbl TipoCarrieraValoriTbl

IDtipocarrieravaloriTipoCarrieraValoriIDtipocarrieraselezione
70​
Transione di genere (general)
10​
Then I select group 10 (unbound) Transione di genere in the first pull down, and then subgroup 70Transione di genere (general) . I only store 70. My table then gets modified
So this table
CarrieraTbl CarrieraTbl

IDcarrieraIDdipendenteDataInizioCarrDataFineCarrIDtipocarrieraselezionaIDtipocarrieravalori
7​
1868​
10​
9​
1868​
18​
50​
13​
3467​
19​
14​
becomes normalized to this
CarrieraTbl CarrieraTbl

IDcarrieraIDdipendenteDataInizioCarrDataFineCarrIDtipocarrieravalori
7​
1868​
70​
9​
1868​
50​
13​
3467​
14​
It works fine the way you are doing, but there are several reasons I would not do it that way.
Example would be if you every moved a subform selection to another group.
 

Users who are viewing this thread

Top Bottom