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 (CareManagerCod e) is still in the underlyning table (tblContactDeta ils) but the associated text (CareManagerTex t) 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 tblPatientDetai ls. Record source for frmSub is tblContactDetai ls. Tables are linked by HospitalNumber (PK in tblPatientDetai ls) and have a 1 to many relationship (1 record in tblPatientDetai ls to many in tblContactDetai ls). frmSub has two combo boxes, cboLocalityTeam and cboCareMananger . These write to number fields [Locality] and [CareManager] in tblContactDetai ls. 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 tblContactDetai ls.

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 tblContactDetai ls 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]

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

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 tblPatientDetai ls. Record source for frmSub is tblContactDetai ls. Tables are linked by HospitalNumber (PK in tblPatientDetai ls) and have a 1 to many relationship (1 record in tblPatientDetai ls to many in tblContactDetai ls). The user can use navigation buttons within frmSub to move between records (existing within tblContactDetai ls). 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 tblContactDetai ls). 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 tblContactDetai ls). 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]

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.

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.

The data doesn't change when switching between tabs, only when selecting a different Locality within a new record.

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.