Cascading listbox error (1 Viewer)

Cantthinkofone

Registered User.
Local time
Today, 11:40
Joined
May 2, 2018
Messages
19
I figured I'd jump right in.
Tasked with a project in Access 2016: I have an intake form and for ease of use, have created cascading combobox to listbox fields on the form. This way if a user selects an office location in the combobox, the region, state and manager listboxes auto-populate with the related data.

The error is that the value selected in the combobox is saved to the table (control source) , but the cascade listbox values do not save.
I've been searching web, resource manuals, Microsoft support and various forums and I have not found a solution.

Any thoughts?

Thanks!
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
Use exactly the same method to save any data on the cascade combos as you did on the first combo
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:40
Joined
Aug 30, 2003
Messages
36,118
If the listbox is multiselect, it's value will always be Null. You would need code to get the values:

http://www.baldyweb.com/MultiselectAppend.htm

You could use similar code to concatenate the values to a textbox, but most of us would discourage saving multiple values in a single field.
 

Cantthinkofone

Registered User.
Local time
Today, 11:40
Joined
May 2, 2018
Messages
19
Thanks for the responses.
When I set up the cascade list box, the same as the first combobox, it does save. But then turns the list box into a combobox where the user then has the extra step of selecting the corresponding data. I like the simplicity of the list box which is automatically populated. Does that make sense? Trying to remove the human error part of data entry.

I'll post screenshots of what I'm trying to solve and in the meantime read the recommended link for code.

Thanks for your help
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:40
Joined
Feb 19, 2002
Messages
42,971
but the cascade listbox values do not save
They are not supposed to save. You would join back to the location table whenever you needed these values. Do you really want location 1 to have a different city for person A than for person B? That is what can happen when you duplicate data that should not be duplicated. Do a little reading on database normalization before continuing.

I've attached a sample database that shows how to use this type of data. Look at example 4. The "best" solution shows you that you use a query as the RecordSource for the form and the query joins to the lookup table. You then bind the combo to the lookup field and pick the text fields from the lookup table but you lock those fields to prevent accidental updating.
 

Attachments

  • FillFormFields.zip
    333.2 KB · Views: 64

Cantthinkofone

Registered User.
Local time
Today, 11:40
Joined
May 2, 2018
Messages
19
Thank you all for the suggestions.
The listboxes are set Multi Select = None. There should only be one option, it's a 1:1. I attached a doc with screenshot of the data entry form, it has listboxes that auto-populate based on a combobox selection.
Its the value in the listbox that currently does not save to the underlying table. I'm thinking code is written in the Listbox AfterUpdate() to convert the value into a string to save to table, however this is where my limited VBA fails me.
:banghead:
 

Attachments

  • Listbox values.docx
    31.8 KB · Views: 64

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:40
Joined
Aug 30, 2003
Messages
36,118
Is the control source of the listbox the field in the underlying table? Has the value been selected, or just displayed? If you've just set the source of the listbox, you'd still have to select the value.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:40
Joined
Aug 30, 2003
Messages
36,118
By the way, I think this will programmatically select the first item:

Me.ListboxName = Me.ListboxName.ItemData(1)
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
Thank you all for the suggestions.
The listboxes are set Multi Select = None. There should only be one option, it's a 1:1. I attached a doc with screenshot of the data entry form, it has listboxes that auto-populate based on a combobox selection.
Its the value in the listbox that currently does not save to the underlying table. I'm thinking code is written in the Listbox AfterUpdate() to convert the value into a string to save to table, however this is where my limited VBA fails me.
:banghead:

Having looked at your screenshot, apart from the two combos, these appear to be textboxes NOT listboxes.
If they are listboxes, why only show one record?
 

Cantthinkofone

Registered User.
Local time
Today, 11:40
Joined
May 2, 2018
Messages
19
Re: listboxes that look like textboxes. You are correct, the majority data entry are txtboxes. I cascaded the Region and Division fields as listbox to only show one value, as 1:1 relation to the cboOrderBranch. I couldn't get the function I wanted with a textbox

the Region Listbox Row Source =qry BranchRegionMgr. Control Source = Region. cboOrderBranch After Update requery listbox since the listbox is cascaded down from the cbo selection.

the Division Listbox Row Source = qry BranchRegionMgr Control Source = Division. cboOrderBranch After Update requery listbox since the listbox is cascaded down from the cbo selection.
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
They may not have worked as textboxes before but they aren't fully working for you now either.
This is what I suggest as an alternative:

Change your Region and Division controls to inbound textboxes and use them for display purposes only.
In your Order Brach combo after update event, add code to save the Region and Division values to the table AND populate the two textboxes with those values.
 

Cantthinkofone

Registered User.
Local time
Today, 11:40
Joined
May 2, 2018
Messages
19
"They may not have worked as textboxes before but they aren't fully working for you now either. "

Lol!, ok you got me there. Very true.
I will try your suggestion to code After update.

Thanks
 

Mark_

Longboard on the internet
Local time
Today, 11:40
Joined
Sep 12, 2017
Messages
2,111
@ OP,

DESIGN question; are these fields that need to be saved as static information or should they see current values dynamically?

Example of each:
If I look at an order form I want to see where that order was sent to. As such I would keep a copy of the customers shipping information in the order header so I can always tell where I went to.

If I am looking at customers who may wish to buy from me, I would show their shipping address dynamically so that I always show their "Current" information up until an order is actually going out for delivery. This way if the customer changes their shipping address all current and future orders go to the new address instead of the one they had when they placed the order.
 

Mark_

Longboard on the internet
Local time
Today, 11:40
Joined
Sep 12, 2017
Messages
2,111
If you use all of the fields you wish to look up in your combo box query, you can then have your bound fields on your form, disabled. After the user makes a selection from the combo box, assign values from the additional columns in your combo box to the bound text boxes. This will allow ACCESS to handle updating your record when the user saves.

In effect you are creating a normal form, just instead of having the user type in the information that is being looked up, you copy it from the selected entry of the combo box. ACCESS gets to do the work of updating the table and will then display what is already in the table without you needing to resort to workarounds.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:40
Joined
Feb 19, 2002
Messages
42,971
Please at look at the example I posted. You never want to copy this type of data so that it resides in multiple places. The sample shows three ways to get lookup data from another table. The correct way which does not duplicate the data and two inferior ways which do duplicate the data and will I promise that duplicating data like this will come back to haunt you in the future.
 

Mark_

Longboard on the internet
Local time
Today, 11:40
Joined
Sep 12, 2017
Messages
2,111
@ Pat, definitely agree. Very few times do you really want to save something like this. I'd normally use a "Deleted" type flag to show a record is not in use but still point back to it. For a beginner, doing it wrong and then learning WHY it is wrong is often the only way they learn though.
 

Cantthinkofone

Registered User.
Local time
Today, 11:40
Joined
May 2, 2018
Messages
19
Thanks to everyone on the thread. Recap: I'm trying to get a cascading listbox Region value to save to the underlying table when the cboLocation is chosen. I tried the recommended set value on AfterUpdate. I've pulled up this tutorial from pbaldy (see below) however, I still can't get the value to save to table. I have the code:

Private Sub cboLocation AfterUpdate ()
Me.ListRegion = Me.cboLocation.Column(2)

End Sub
"

If your situation is that rare one where saving the related info is necessary, you'd use the same combo. In the textbox, instead of the formula as the control source, you'd list the field in the Orders table where you wanted the phone number saved. In the after update event of the combo, you'd have code like this to copy the value over:
Me.TextboxName = Me.ComboName.Column(2)
 

Mark_

Longboard on the internet
Local time
Today, 11:40
Joined
Sep 12, 2017
Messages
2,111
Can you please list the source for your combobox?

Me.ComboName.Column(2) will reference the 3rd column of data in your source.

Are you receiving an error? If so what is the error message?
 

Users who are viewing this thread

Top Bottom