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.