Bound cascade combo?

[cdoyle]

Hi,
I search the forum, and also read the cascade FAQ, but didn't see this answer.

I want to have some cascading combo boxes, but all the examples I see have the cbo's as unbound. I would like to have mine bound, so when the make a selection from each cbo, it's saved in the table?

Is this possible?

 

[ajetrumpet]

Yes, it's possible.

Just create the combos, and add a control source to each of them, even though they already have rowsources.

So, in the end, each combo box will have a rowsource and a controlsource. The CS is for binding, while the RS is for pulling data in and populating the box. You can have both.


The cascading FAQ does not show this, but my sample database FAQ does. It is located here.

 

[cdoyle]

OK, got the bounding problem to work, but now here is my next problem.

I want to use these on a continuous form, but now I just realized that when you update record 1, it updates all the records in the form.

I searched the form, and found this to be a problem but in one post Pat said he found a solution, and linked to a sample db. But the link is dead!

Anyone have the sample db that he posted, or know where to get it?

Once I get this all working, what I want to do, is have a command button, that allows you to open a popup form depending on the selection in the second dropdown.

 

[boblarson]

OK, got the bounding problem to work, but now here is my next problem.

I want to use these on a continuous form, but now I just realized that when you update record 1, it updates all the records in the form.

Then you don't have the binding set properly because if the combo is bound to a field it will NOT change all of them.

 

[cdoyle]

Hmm, I'll have to take another look. Now that you said that, it makes sense.

Edit: Just took a look and the first field wasn't bound anymore!! Maybe I didnt' save the form, the last time I closed it but I sure thought it was bound.

 

[boblarson]

Hmm, I'll have to take another look. Now that you said that, it makes sense.

Edit: Just took a look and the first field wasn't bound anymore!! Maybe I didnt' save the form, the last time I closed it but I sure thought it was bound.

Just something to look out for, if you think you saved but it didn't. If you have used the DoCmd.SetWarnings False anywhere in your code and either didn't use the DoCmd.SetWarnings True right afterwards, or if you were testing and it errored out before it could get to the code to reset them, then you might be without warnings which will, if you just close the form, it will discard any changes made as if you said no to the dialog asking if you wanted to save changes. You would have to click FILE > SAVE or the save button directly to save.

So, if you have accidentally turned your warnings off, you can reset them by sticking this:

Public Function ResetWarnings()
   DoCmd.SetWarnings True
End Function

into a STANDARD module and then putting your cursor in the function and then clicking the RUN button.

Don't know if that happened to you, but just thought I'd mention it if you did. And, if it did, then remember to put an error handler on all code that uses the DoCmd.SetWarnings False so that you can have DoCmd.SetWarnings True as the first line in the error handler so you get reset if something fails in between the time you turn off the warnings and the time you turn them back on.

 

[cdoyle]

Thanks for the tips on the warning, I'll check that out right now. I've been working on this db all week, and it probably did warn me, but I was just so tired of messing with it, I didn't realize what I did.

back to the combos,
I'm not sure what I'm doing wrong here, maybe it doesn't work the way I would like it too.

As soon as I bound the first combo, the second combo doesn't update anymore. The bound fields are number fields.

In the first combo, I've change the lookup to this

SELECT DISTINCT tbl_Criteria_dropdown.Criteria_dropdown_ID, tbl_Criteria_dropdown.Criteria_dropdown
FROM tbl_Criteria_dropdown
ORDER BY tbl_Criteria_dropdown.Criteria_dropdown;

I have the dropdown bound to column #1 (the number ID) and this column hidden in the dropdown.

I then have in my afterupdate event of the first combo this,

   .RowSource = "SELECT DISTINCT tbl_criteria_sub_lookup.Criteria_Sub_Options, tbl_criteria_sub_lookup.ID FROM tbl_criteria_sub_lookup WHERE tbl_criteria_sub_lookup.Criteria_dropdown_ID = '" & [cbo_criteria] & "'"

Is there something I'm missing to make it work with a bound number field?

 

[boblarson]

You don't need to change the rowsource in the after update event. Just leave it and use:
Me.YourSecondComboName.Requery

 

[cdoyle]

Where should I put the requery?

here is my complete afterupdate event

Private Sub cbo_criteria_AfterUpdate()
  
  With Me.cbo_subcriteria
    
    .Enabled = True

    'This code is very popular and uses the double quote (") as a delimiter in the SQL statement.
    'It also functions in the same manner as the code in Form 1 and Form 2...

     .RowSource = "SELECT DISTINCT tbl_criteria_sub_lookup.Criteria_Sub_Options, tbl_criteria_sub_lookup.ID FROM tbl_criteria_sub_lookup WHERE tbl_criteria_sub_lookup.Criteria_dropdown_ID = '" & [cbo_criteria] & "'"
    
    .Value = Null

  End With


End Sub

 

[boblarson]

Just replace the .RowSource and get rid of the .Value part too.

 

[cdoyle]

OK, I gave that a try but when I update field 1, it gives me an error

Compile Error,
Expected Function or variable

Here is my code, I think I did it right?

Private Sub cbo_criteria_AfterUpdate()
  
  With Me.cbo_subcriteria
    
    .Enabled = True

    'This code is very popular and uses the double quote (") as a delimiter in the SQL statement.
    'It also functions in the same manner as the code in Form 1 and Form 2...

     Me.cbo_subcriteria.Requery = "SELECT DISTINCT tbl_criteria_sub_lookup.Criteria_Sub_Options, tbl_criteria_sub_lookup.ID FROM tbl_criteria_sub_lookup WHERE tbl_criteria_sub_lookup.Criteria_dropdown_ID = '" & [cbo_criteria] & "'"
    

  End With
   

End Sub

 

[boblarson]

Since you are already using me.cbo_subcriteria with a WITH, you would just use:

.Requery


Otherwise, what you are asking it to do is essentially:

Me.cbo_subcriteria.Me.cbo_subcriteria.Requery

which is totally off.

 

[cdoyle]

I tried changing it to,

.Requery = "SELECT DISTINCT tbl_criteria_sub_lookup.Criteria_Sub_Options, tbl_criteria_sub_lookup.ID FROM tbl_criteria_sub_lookup WHERE tbl_criteria_sub_lookup.Criteria_dropdown_ID = '" & [cbo_criteria] & "'"

and I get the same error.

 

[boblarson]

Actually, it is just .Requery Nothing else next to it. Get rid of the 'Select Distinct...'

 

[cdoyle]

Actually, it is just .Requery Nothing else next to it. Get rid of the 'Select Distinct...'

Do you mean get rid of the entire Select Distint statement?

where I would have this

Me.cbo_subcriteria.Requery = "tbl_criteria_sub_lookup.Criteria_Sub_Options, tbl_criteria_sub_lookup.ID FROM tbl_criteria_sub_lookup WHERE tbl_criteria_sub_lookup.Criteria_dropdown_ID = '" & [cbo_criteria] & "'"

This doesn't work.

Or did you mean to get rid of the me.cbo_subcritera ahd have just .requery
So it would look like this

.Requery ="tbl_criteria_sub_lookup.Criteria_Sub_Options, tbl_criteria_sub_lookup.ID FROM tbl_criteria_sub_lookup WHERE tbl_criteria_sub_lookup.Criteria_dropdown_ID = '" & [cbo_criteria] & "'"

Neither seem to work, but I think I misunderstood what you mean to do.

 

[boblarson]

Okay, change it to this (this is what I meant):

  With Me.cbo_subcriteria
    
       .Enabled = True

       .Requery
    

  End With

 

[cdoyle]

that got rid of the error, but when I make a choice from the first combo (cbo_criteria), nothing happens in the second one (cbo_subcriteria). It just stays blank.

 

[boblarson]

It should stay blank until you make a choice from it, but make sure your rowsource is correct and references the first as criteria so that it will populate with choices (if that is the blank you are talking about) If you still can't get it to work, how about posting the db so we can resolve this quicker.

 

[boblarson]

Also, if it would work better for you. Take a look at my Cascading Combo sample on my website:

http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

 

[cdoyle]

Thanks for the link to yours, I got it to work right off the bat!

Thank you!