Cascade combobox on continuous form

[Ivy]

Hi all,

I would like add a cascade combo box to my subfom, but it's in continuous form and I have always error about parameter.

It works on second combobox ("ValoriCarrieraIDcmb"), only if I put manually the number of ElencoCarrieraID, for example "8".

How can I filter it?

Thank you very much

 

[xavier.batlle]

Cascading combo boxes in a continuous can be difficult to do. See this discussion
https://www.access-programmers.co.u...boxes-in-datasheet-or-continuous-form.275155/

Semi Unique Combox Record Source on Continuous Form​

 

[MajP]

Besides the problem that @xavier.batlle is describing you have another problem.

The Forms collection only holds references to all open forms. It does not include forms opened as subforms. This means you can never reference a subform, or control on the subform by the subform name.

Forms!NameOfFormOpenedAsSubform.NameOfControl  (Does not work)

You have to reference as follows going from the main form through the subform control to the form in the subform control

Forms!MainFormName!SubFormControlName.Form!NameOfControlOnSubForm

In your case would have to be

SELECT
 ValoriCarrieraTbl.ValoriCarrieraID,
 ValoriCarrieraTbl.ValoriCarriera,
 ValoriCarrieraTbl.ElencoCarrieraID
FROM ValoriCarrieraTbl
WHERE
 ValoriCarrieraTbl.ElencoCarrieraID =[forms]![DipendenteM]![CarrieraM].[Form]![ElencoCarrieraIDcmb]

 

[MajP]

I personally would not use the method referenced above by @xavier.batlle. It is OK with datasheets but it is complicated and unreliable in continuous forms. You will often get flickering or fields that partially disappear depending on the cursor location.
This method always works and IMO just easier. No complicated sql strings to code code. It requires putting a textbox over top of the combobox and rerencing that field. However, see the subforms updated controlsource.

Code:

Private Sub txtValoriCarriera_KeyDown(KeyCode As Integer, Shift As Integer)
  Me.ValoriCarrieraIDcmb.SetFocus
End Sub

Private Sub ValoriCarrieraIDcmb_Enter()
  Me.ValoriCarrieraIDcmb.Requery
End Sub

Align the textbox so that it covers the combobox except for the down arrow. Send the combobox to the back so it is behind the textbox. FYI, I do get an error when selecting from the first combobox saying the field cannot hold the value, but then it does take it. That is unrelated, but could not find the cause.

 

[Ivy]

I personally would not use the method referenced above by @xavier.batlle. It is OK with datasheets but it is complicated and unreliable in continuous forms. You will often get flickering or fields that partially disappear depending on the cursor location.
This method always works and IMO just easier. No complicated sql strings to code code. It requires putting a textbox over top of the combobox and rerencing that field. However, see the subforms updated controlsource.

Code:

Private Sub txtValoriCarriera_KeyDown(KeyCode As Integer, Shift As Integer)
  Me.ValoriCarrieraIDcmb.SetFocus
End Sub

Private Sub ValoriCarrieraIDcmb_Enter()
  Me.ValoriCarrieraIDcmb.Requery
End Sub

Align the textbox so that it covers the combobox except for the down arrow. Send the combobox to the back so it is behind the textbox. FYI, I do get an error when selecting from the first combobox saying the field cannot hold the value, but then it does take it. That is unrelated, but could not find the cause.

Thank you very much @MajP!

Yes, it seems much simpler to me and above all I would have fewer problems in the future, in case, understanding because something doesn't work in the code.

I put your code in my "real" project (with real names, data etc) and it works fine.

Only, sorry, can I ask you how you managed to avoid the error when selecting for example "Elogio" (praise) or "Varie comunicazioni (miscellaneous communications), for those values I haven't a subgroup in the second field (not all of them do).

I always get an error on my db, but in your example no, how did you do it?

 

[tvanstiphout]

Another source of information is the Northwind 2 Developer Edition template. It has lots of documentation including this page specifically addressing cascading comboboxes. A video is here.

 

[MajP]

Can you translate the error message for me? I assume that Messages is some of your own code and a custom message. There is no access error message that has "OK" and "?" as choices. My guess is in your real DB that ValoriCarrieraID is a required foreign key and that is a custom message if you forget to pick one.

 

[Ivy]

Can you translate the error message for me? I assume that Messages is some of your own code and a custom message. There is no access error message that has "OK" and "?" as choices. My guess is in your real DB that ValoriCarrieraID is a required foreign key and that is a custom message if you forget to pick one.

Found the problem!!! after hours checking every little thing by comparing the two db.. it was the join property in the query of the form "CarrieraM". Now it works perfectly!!

(Traslation of the error message: impossible to find any record in the "TipoCarrieraValoriTbl" table containing "ValoriCarrieraID" fields corresponding to the key)

Thanks again MajP, you're great

 

[MajP]

it was the join property in the query of the form "CarrieraM". Now it works perfectly!!

That is my fault. I forgot to mention that I had to modify the forms recordsource to that query.