Solved Combobox in a child form

[silversun]

Hi All,
I hope you all are very well.

I have a data entry form created by MS Access 2016.

I want to have a combobox that lists different items based on selction of cmb_items (like a castcaded combobox in a countinues subform). I name it cmb_packages and it gets its values from T_packages, column(1). When user selects any of the items in cmb_packages I need to have a textbox to show a short message that is again in the same table T_packages.Column(3) labeled as "msg".
I was able to setup castcaded comboboxes with the VBA's but I was stucked in this last part to show the message.
Please see my attached db and help me if you can.
Have a nice day
Masoud

 

[June7]

Expression in textbox ControlSource: =[cmb_packages].[column](3)

 

[mike60smart]

Hi All,
I hope you all are very well.

I have a data entry form created by MS Access 2016.

I want to have a combobox that lists different items based on selction of cmb_items (like a castcaded combobox in a countinues subform). I name it cmb_packages and it gets its values from T_packages, column(1). When user selects any of the items in cmb_packages I need to have a textbox to show a short message that is again in the same table T_packages.Column(3) labeled as "msg".
I was able to setup castcaded comboboxes with the VBA's but I was stucked in this last part to show the message.
Please see my attached db and help me if you can.
Have a nice day
Masoud

Hi Masoud

See the modified attached db

 

[mike60smart]

combo 3 raises an error. There seems to be a typo in a control name.

Hi Pat
Don't know how I managed that but here with the corrected version

 

[mike60smart]

I did not get error.

Did you see post #2?

Hi June

My updated Db was based on your suggesting in Post 2

It did have an error which Pat pointed out and now corrected.

 

[silversun]

Hi everyone. Sorry for beeing late.
I was busy with some other stuff.
I downloaded both db's you've attached but unfortunately none of them worked.
I used "=[cmb_packages].[column](3)" in my textbox's control source and nothing was showing up in the textbox when I selected any item from packages combobox.
Any idea?
I appreciate your time

 

[June7]

I put that expression in textbox of your original posted db and it works just fine.

 

[mike60smart]

Hi
This version does work

The Column Count on the Combobox needed to be changed from 3 to 4

 

[silversun]

Hi
This version does work

The Column Count on the Combobox needed to be changed from 3 to 4

I used this version and as soon as I select Apple in items combobox it gives me error and shows the VBA code. Once I clear the error message and select other parameters, Units and Packagin, I still don't see the message I was expecting to be appeared in textbox.
I've already changed the column number to 4.
I couldn't solve this issue.
Can you please try it one more time?
Please see the attachments.
Thanks

 

[June7]

Error is with line
Me.cbopackages.RowSource = pSource

There is no control named cbopackages - drop the s at end.

Message will not display in textbox until selection in cboPackage.

 

[silversun]

Error is with line
Me.cbopackages.RowSource = pSource

There is no control named cbopackages - drop the s at end.

Message will not display in textbox until selection in cboPackage.

I did remove the "s" and error was gone. The message is still not showing.

I am going to respond tomorrow if you send me the solution.
Thanks for your time

Its bed time
Good night

 

[June7]

The cmb_Item combobox code is setting cboPackage with a RowSource that does not include msg field. Fix the code.

pSource = "SELECT package_ID, package, package_tier, msg " & _
          "FROM T_packages " & _
          "WHERE package_tier = " & Me.cmb_items.Column(2)

Really should be more consistent with prefixes used in object naming.

 

[mike60smart]

This is easier

 

[silversun]

Really should be more consistent with prefixes used in object naming

I used cmb_ for my comboboxes where as friends in here use cbo. I will try to use cbo from now on.

 

[silversun]

The cmb_Item combobox code is setting cboPackage with a RowSource that does not include msg field. Fix the code.

pSource = "SELECT package_ID, package, package_tier, msg " & _
          "FROM T_packages " & _
          "WHERE package_tier = " & Me.cmb_items.Column(2)

Really should be more consistent with prefixes used in object naming.

I used your solution and it works now.
Thank you

 

[silversun]

This is easier

Thi solution works fine and shows the messages but the problem is why I don't see correct units in my combobox cmb_units? When I select a liquid (water) I'm expecting the voluum units to be listed in my combobox. Please see the attachment.

 

[mike60smart]

Thi solution works fine and shows the messages but the problem is why I don't see correct units in my combobox cmb_units? When I select a liquid (water) I'm expecting the voluum units to be listed in my combobox. Please see the attachment.

Can you upload your latest version

 

[silversun]

Can you upload your latest version

Here is the version that shows units improperly when I select water. I need to see voluume units when water is selected.
DB is attached with your solution.

 

[silversun]

I didn't look at the database so I'm not sure what that means. If it means you want to see the quantity when the units is not Each (or whatever you use), you would change the RowSource query of the combo to concatenate the two fields.

The cmc_units must only show the units we use for voluumes (when water is selected in cmb_items) rather than the mass units.
In that version (the one that mike60smart posted) it would show the mass units for any selection in cmb_items.
My issue was fixed with the solution from June7 and now it is working.
I am going to mark this thread as Solved once mike60smart responded to the last post.
Thank you all for the help.
Masoud

 

[June7]

cmb_units filters for unit_tier = 1. If you want this combobox list to be conditional on item_id, then need to change the RowSource to reference cmb_items for criteria and code to Requery cmb_units.

SELECT T_units.[unit_ID], T_units.[unit], T_units.[unit_tier] FROM T_units WHERE t_units.unit_tier = [cmb_items].Column(2);

Private Sub cmb_units_GotFocus()
Me.cmb_units.Requery
End Sub

Cascading/Conditional combobox is a common topic. Review https://stackoverflow.com/questions...ous-form-the-second-combo-doesnt-show-its-val

For some reason your form is not cooperating with the Requery command. So instead:

SELECT T_units.[unit_ID], T_units.[unit], T_units.[unit_tier] FROM T_units;

Private Sub cmb_units_GotFocus()
Me.cmb_units.RowSource = "SELECT [unit_ID], [unit], [unit_tier] FROM T_units WHERE unit_tier = " & Me.cmb_items.Column(2) & "; "
End Sub