Very odd problem

[Henley12]

I have a form with selection to create a report. Everything works fine except the sorting part. I have a series of 8 combo boxes to choose the sorting order of the report. The users will probably never use all eight, but I gave them the option. Anyway, as you open the first box and choose a sort field, I have the second box selecting the sort fields minus what is in the first box, and so on down the line. The first time through, this works perfectly. The problem comes when I clear the selections and make new ones. Once I choose the first field, then move onto the second box, the field chosen in the first one remains in the list, but some other choice is gone. It absolutely baffles my mind. To test this, create a table with 8 entries. Create a form with 8 combo boxes and do a SELECT fieldname FROM tablename on the first box, then on subsequent boxes, add a WHERE fieldname <> box1, and so on until on the 8th box, you would have seven WHERE clauses. That part should work fine. Create a button to clear all entries, then begin choosing them again. This may be unique to Access 2007, but hopefully some of you will get the same problem I am and see what I am doing wrong. Sorry for the lengthy post.

 

[Fifty2One]

Did you put a requery for the next combobox after your rowsource statement to reset the subsequent combo boxes?

 

[DCrake]

Not being funny but I don't think anyone will want to create 8 tables and 8 combos and program the cascades just to test what could possible be an error on your part. It may be more worthwhile if you posted your mdb to check it.

David

 

[Henley12]

Here is a test database I whipped up in 5 minutes.

 

[Henley12]

Any ideas on this one?

 

[boblarson]

Yes, I have an idea. You need to put a requery to requery all of them when selecting from one. So you need a function to requery the combos and do it the after update event of EACH combo. Also, I modified your clear button to be more efficient:

Option Compare Database

Private Sub Combo0_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo2_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo3_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo4_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo5_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo6_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo7_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Combo8_AfterUpdate()
    RequeryCombos
End Sub

Private Sub Command9_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Then
            ctl.Value = Null
        End If
    Next
End Sub

Function RequeryCombos()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Then
            ctl.Requery
        End If
    Next
End Function

 

[Henley12]

Perfect!!! Thank you very much, Bob.

 

[boblarson]

Perfect!!! Thank you very much, Bob.

¡No Problema! Glad I could help.

 

[Henley12]

I have just one more question. Suppose you have more comboboxes on the form than just the sort fields. How do you keep from clearing those with the code you specified, Bob?

 

[boblarson]

I have just one more question. Suppose you have more comboboxes on the form than just the sort fields. How do you keep from clearing those with the code you specified, Bob?

Put something in the tag property to identify them. So, if you go select each combo in design view you can set their TAG property (in the dialog under the OTHER tab) to say - "reset" (without the quotes)

And then you can modify the function to reset to be like this:

Function RequeryCombos()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Then
           If ctl.Tag = "reset" Then
              ctl.Requery
           End If
        End If
    Next
End Function

 

[Henley12]

That doesn't appear to work. This is Access 2007, would that have something to do with it?

 

[boblarson]

That doesn't appear to work. This is Access 2007, would that have something to do with it?

Nope, it works in 2007. Can you post a screenshot showing exactly where you put the tag and what you put in it?

 

[Henley12]

I am familiar with the tag property. My screenshot is too big for the forum. I am positive it is in the right place, but it just doesn't appear to be working as you say it should. I put the word "reset" in the tag property (without the quotes), then I modified my code to read exactly as you had.

 

[Henley12]

I also notice in VBA when I am typing the ctl.Tag, the Tag property is not in the list.

 

[boblarson]

I also notice in VBA when I am typing the ctl.Tag, the Tag property is not in the list.

Yes, that is normal.

 

[boblarson]

I am familiar with the tag property. My screenshot is too big for the forum. I am positive it is in the right place, but it just doesn't appear to be working as you say it should. I put the word "reset" in the tag property (without the quotes), then I modified my code to read exactly as you had.

So, post the exact code you are using now. And, you might want to set a breakpoint and follow the code through to see what it is doing and see if it is actually working.

 

[Henley12]

Ok, Bob. I am officially an idiot. I did indeed have the tag property set correctly on each combobox......however, I had the dang code to check for the tag property in the requerycombos code instead of the clear button code. Amazing how things work properly when you actually put them where they are supposed to be. Sorry for the headache.

 

[boblarson]

Ok, Bob. I am officially an idiot. I did indeed have the tag property set correctly on each combobox......however, I had the dang code to check for the tag property in the requerycombos code instead of the clear button code. Amazing how things work properly when you actually put them where they are supposed to be. Sorry for the headache.

No problem - I've been there and done the same type of thing before, so I totally can understand. It was starting to make me wonder what was going on though as I knew it should work.