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

[HillTJ]

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.

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]

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]

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]

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]

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]

..anyway here is a demo. open DemoForm.
"on top" of ContactPurposeID combobox is a dummy textbox (txtDummy). see its controlsource.

 

[Babycat]

Just to fix Arnelgp's demo overlay issue. Bring txtDummy to forefront, set its background to normal (not transparent).

 

[Babycat]

..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]

BabyCat, you try again this demo.

 

[Babycat]

Hi Arnelgp
It works great but for new record only.
If user changes Group of exising record, Subgroup does not filter out its rowsource.

 

[arnelgp]

that was the original request on post #1.

 

[HillTJ]

Gents, thanks for your input. Will have a go over the next couple of days & let you know how i get on.

 

[arnelgp]

babyCat, this demo is for you.

 

[CJ_London]

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

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

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]

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]

@arnelgp , I downloaded your demo & yes, I can work with this. Exactly what I meant. A clever work around. Thanks a lot.

 

[Ivy]

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

 

[arnelgp]

sorry, which form? and what Group does not have a subgroup?

 

[MajP]

CarrieraTbl CarrieraTbl

IDcarriera	IDdipendente	DataInizioCarr	DataFineCarr	IDtipocarrieraseleziona	IDtipocarrieravalori
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

IDtipocarrieravalori	TipoCarrieraValori	IDtipocarrieraselezione
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

IDtipocarrieraselezione	Tipocarrieraseleziona
10​	Transione di genere

In my subgroup table I would do this
TipoCarrieraValoriTbl TipoCarrieraValoriTbl

IDtipocarrieravalori	TipoCarrieraValori	IDtipocarrieraselezione
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

IDcarriera	IDdipendente	DataInizioCarr	DataFineCarr	IDtipocarrieraseleziona	IDtipocarrieravalori
7​	1868​	​	​	10​	​
9​	1868​	​	​	18​	50​
13​	3467​	​	​	19​	14​

becomes normalized to this
CarrieraTbl CarrieraTbl

IDcarriera	IDdipendente	DataInizioCarr	DataFineCarr	IDtipocarrieravalori
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.