cascading combo boxes

[luciegiles]

Hi,

I have used the tutorial Cascading Combo/List Boxes to filter the combo box cboCareManager dependent on the entry to cboLocalityTeam - the common code between the two tables is LocalityCode. cboCareManager and cboLocalityTeam both sit within frmSub which in turn is a subform within frmMain. The AfterUpdate code is as follows:

Private Sub cboLocalityTeam_AfterUpdate()
With Me![cboCareManager]
    If IsNull(Me!cboLocalityTeam) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [CareManagerCode],[CareManagerText] " & _
                   "FROM tlkpCareManager " & _
                   "WHERE [LocalityCode]=" & Me!cboLocalityTeam
    End If
    Call .Requery
  End With
 
End Sub

Essentially this works but when I exit the form and return to the record the text no longer appears in the form - the code (CareManagerCode) is still in the underlyning table (tblContactDetails) but the associated text (CareManagerText) no longer appears and the list is empty; I have to reselect an entry in cboLocalityTeam for the data to reappear in cboCareManager.

How do I rectify this so that the data on the form is maintained?

[NeoPa]

Generally to return a ComboBox selection to where it was before a .Requery is called you simply assign the previously selected value to the control. This moves the selection to that value in the list.

More than that is hard to say as you have a fairly involved relationship between forms and subforms and the controls thereon which is loosely described at best. If you can specify clearly (precisely) what fits where in relation to the main form then we can more directly answer your question.

[luciegiles]

Generally to return a ComboBox selection to where it was before a .Requery is called you simply assign the previously selected value to the control. This moves the selection to that value in the list.

How do you assign the previously selected value to the control? can you give an example?

If you can specify clearly (precisely) what fits where in relation to the main form then we can more directly answer your question.

I'll do my best. frmMain has a subform frmSub. Record source for frmMain is tblPatientDetails. Record source for frmSub is tblContactDetails. Tables are linked by HospitalNumber (PK in tblPatientDetails) and have a 1 to many relationship (1 record in tblPatientDetails to many in tblContactDetails). frmSub has two combo boxes, cboLocalityTeam and cboCareMananger. These write to number fields [Locality] and [CareManager] in tblContactDetails. cboLocalityTeam gets its values from table tlkpLocality while cboCareManager gets its values from tlkpCareManager. The tables have the following layout:

tlkpLocality
[LocalityCode] – Number, PK 
[LocalityText] – Text, Name of locality
 
tlkpCareManager
[CareManagerCode] – Number, PK
[CareManagerText] – Text, Name of staff
[LocalityCode] – Number, FK to link staff to locality in which they work 

cboLocalityTeam has row source tlkpLocality with column count of 2 and bound column 1. cboCareManager is determined by AfterUpdate of cboLocalityTeam but also has column count 2 and bound column 1 in order to save [CareManagerCode] to tblContactDetails.

On adding a new record the cascade works fine and the correct Care Manager’s appear when a locality is selected. If I exit and then re-enter the record the cboLocalityTeam is still populated but cboCareManager is not. CareManagerCode is still stored in [CareManager] of tblContactDetails but cboCareManager is not populated in frmSub. If I re-enter the same value in cboLocality, cboCareManager re-populates.

Is it possible to have cboCareManager stay populated with the original value on each subform record?

[NeoPa]

@luciegiles
I'll try to look at this in more detail later, but for this question specifically :

Dim strValSave As String
 
With Me
  ...
  strValSave = .cboLocalityTeam
  Call .Requery
  ...
  .cboLocalityTeam = strValSave
  ...

It's important to remember that this will fail if the value is no longer available after the .Requery.

[NeoPa]

Good job Lucie. I'm impressed.

It seems to me that your problem is always that the values available in cboCareManager (as opposed to the value stored in the record) are not correctly set unless and until you make a selection from the other ComboBox (cboLocalityTeam).

This could be handled by placing similar code in the OnCurrent event procedure I suspect, however, I would suggest instead that there is no situation where this updated SQL is not appropriate, so you would be better served designing it directly into the ComboBox RowSource instead. For this you will need the actual name of the SubForm control on your main form (The control that contains [frmSub] - See Referring to Items on a Sub-Form). For the sake of illustration I'll refer to it here as [sfmSubForm]. You may need to change the reference to match your own control name.

SELECT [CareManagerCode],
       [CareManagerText]
FROM   tlkpCareManager
WHERE  [LocalityCode]=Forms!frmMain!sfmSubForm!cboLocalityTeam

[NeoPa]

This means of course, that your AfterUpdate event procedure can be simplified to :

Private Sub cboLocalityTeam_AfterUpdate()
Call Me.cboCareManager.Requery
End Sub

A little more straightforward, but good experience anyway looking into all that I expect.

[luciegiles]

Hi Neo,

I've taken the second option and placed the following SQL in the RowSource of cboCareManager.

SELECT [CareManagerCode], 
       [CareManagerText] 
FROM   tlkpCareManager 
WHERE  [LocalityCode]=Forms!frmMain!frmSub!cboLocalityTeam 

Have updated the AfterUpdate event procedure for cboLocalityTeam also as you say above.

Now however there are no options in the list for cboCareManger i.e. a blank lookup.

Any ideas where I've gone wrong?

[NeoPa]

I would guess that it's the one place where your code is different from what I suggested Lucie. I can't be sure as I can't see your database, but the frmSub is almost certainly the name of your form rather than the SubForm control. The naming standard seems to fit. You need to refer to the SubForm control there not the form.

Refer to the link in post #5 for the explanation.

[luciegiles]

Hi Neo,

I've very nearly got this where I want it - one further difficulty though. As I mentioned above frmMain has a subform frmSub. Record source for frmMain is tblPatientDetails. Record source for frmSub is tblContactDetails. Tables are linked by HospitalNumber (PK in tblPatientDetails) and have a 1 to many relationship (1 record in tblPatientDetails to many in tblContactDetails). The user can use navigation buttons within frmSub to move between records (existing within tblContactDetails). The problem is when moving from one record to the next the options available in cboCareManager stay as per cboLocalityTeam in the first record. Selecting a different option in cboLocalityTeam in the second record gives the correct options in cboCareManager for that record but the options then reflect cboLocalityTeam as per the second record in the first record also. Is there anyway to have the form (frmSub) reflect the options for cboCareManager as per cboLocalityTeam on the current record/form without changing the existing one?

[NeoPa]

Lucie,

You really need to read that explanation I keep pointing you to. Without it you will fail to grasp the problem. I'm almost certain now that frmSub is not a SubForm control (notice control) but is actually a form you happen to be using within a SubForm control on your main form. SubForms don't contain ComboBox controls or any other types of control. Controls fit on forms (or reports). I don't know how I can explain this more clearly.

[luciegiles]

Neo,

I've read the article and understand that the form and the Subform control are not the same thing or necessarily called the same thing - what the article doesn't explain is how the to find the name of the SubForm control. When in frmMain what I believe is the SubForm control (the container of frmSub) is also called frmSub but maybe I am looking at the wrong thing? I have treid renaming this to frmSubControl and re-doing the SQL but the same problem persists.

[NeoPa]

1. Open the form in Design mode.
2. Click on where the SubForm shows on your form.
3. Ensure the Properties Pane is open (Alt-Enter).
4. Look at the Title Bar of the Properties Pane and confirm that it says "SubForm/SubReport: blah, blah, blah".
5. Look down the properties from the top (in the All section - tabs at the top) Name should be the name of the SubForm control itself.
6. Source Object should be the name of the form object it contains.

If you can get those values for me and post the current code then I'll see if I can make some sense of it :)

[luciegiles]

Thanks Neo,

I had it right; both were called frmSub. So as to differentiate I have changed the SubForm control to frmSubControl (Name) and the form remains frmSub (Source Object).

AfterUpdate of cboLocalityTeam is as follows.

Private Sub cboLocalityTeam_AfterUpdate()
 
Call Me.cboCareManager.Requery
 
End Sub

Control source of cboLocalityTeam is Locality (in tblContactDetails). Row source is tlkpLocality (structure below) and bound column is [LocalityCode]

tlkpLocality 
[LocalityCode] – Number, PK  
[LocalityText] – Text, Name of locality 

Control source of cboCareManager is CareManager (in tblContactDetails). Row source is below and bound column is [CareManagerCode].

SELECT tlkpCareManager.CareManagerCode, tlkpCareManager.CareManagerText
FROM tlkpCareManager
WHERE (((tlkpCareManager.LocalityCode)=[Forms]![frmMain]![frmSubControl]![cboLocalityTeam]));

One other thing - frmSub is tabbed; having just looked at some of the other posts you've highlighted I'm not sure if this is the problem. Page1 is the tab cboLocalityTeam and cboCareManager are on. The data doesn't change when switching between tabs, only when selecting a different Locality within a new record.

Really appreciate your help with this.

L

[NeoPa]

@luciegiles
Nothing important then Lucie :D

Let me see if I can find out about Tabs and find a solution for you. Maybe when I've done that I can start to use them myself sometimes. It worked like that when I started learning about forms ;)

[NeoPa]

Well, it seems that all controls on a tab control are equally accessible by code. That is to say that, from my testing at least, there is no special handling required for a control on a tab compared with any that are on the form directly. It is my understanding that this (what you have posted) should work fine for you.
@luciegiles
What exactly is not working as expected? I'm getting a little confused here. Isn't the behaviour you describe exactly what you've been trying to achieve anyway? I suppose that really depends on what you mean by the data. I thought the ComboBox list was supposed to change.

[luciegiles]

Hi Neo,

Sorry for delay; been caught up with something else the last few days.

What I want is for the options in the combo boxes to always reflect what is stored in the underlying table tblContactDetails.

At present one client in tblPatientDetails can have many records in tblcontactDetails; these are navigated via navigation buttons (Next and Previous) on frmSub. When you move from one record to the next and change the option in cboLocality the options in cboCareManager in Record 1 change to the selection associated with the selection of cboLocality in Record 2.

EG:

Client 1 has Record 1 and Record 2.

Select Locality 1 in cboLocality in Record 1 and get options A, B and C in cboCareManager. Navigate forward to Record 2, select Locality 2 in cboLocality and get options D, E and F. Navigate back to Record 1 and options A, B and C have disappeared (the cbo appears blank) to be replaced by options D, E and F. The data in the underlying table for record 1 (tblContactDetails) still reflects what was selected originally but is not reflected in the frmSub.

[NeoPa]

@luciegiles
That's not a problem Lucie, except in that it makes remembering what's going on a little more complicated for me. I think I will need to have another look at this when my brain's a little fresher. I'll save the link for tomorrow.

BTW I did some digging after the Page problem came up and found that I'd actually done a short article (Using Tabbed Pages on a Form) on accessing controls on Tabs myself some while before. It must have been in a previous lifetime (Although now I check it again I realise I didn't do much of the work itself. just tidied up something someone else had posted and made it into a linkable article) :D

[NeoPa]

@NeoPa
Well, maybe not tomorrow (or today that should be now), but I still intend to dig into this again when I get an opportunity.

[NeoPa]

I think I see what may be going wrong here Lucie. You have told it to reflect the changes (with the posted code) when cboLocalityTeam is updated, yet it seems to me that what you are saying is that logically, the contents of cboCareManager in the same record of your subform should also change whenever the value in cboLocalityTeam changes, which would include when you move between records.

If that is so then you need to include the code to do the job in both the cboLocalityTeam_AfterUpdate event procedure and the Form_OnCurrent event procedure. This is best done by separating the payload code (that which does the job) out into a separate procedure then calling that procedure from both locations. Otherwise changes required later can get out of step and also the volume of code is nearly doubled. Try something like :

Private Sub cboLocalityTeam_AfterUpdate()
    Call SetUpCareManager
End Sub
 
Private Sub Form OnCurrent()
    Call SetUpCareManager
End Sub
 
Private Sub SetUpCareManager()
  With Me![cboCareManager]
    If IsNull(Me!cboLocalityTeam) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [CareManagerCode],[CareManagerText] " & _
                   "FROM tlkpCareManager " & _
                   "WHERE [LocalityCode]=" & Me!cboLocalityTeam
    End If
    Call .Requery
  End With
End Sub

[luciegiles]

Thanks Neo, and sorry for the delayed response (again), I have been waylayed by something else. I'll give this a go and let you know how I get on.

thanks again

[NeoPa]

Go for it Lucie.

And don't worry too much about delays posting. There are all sorts of reasons for that and we have to learn to deal with people on an as and when basis.