Another Access 2010 Combo Box Problem

I have a combo box that changes its contents based on the selection of a value in another combo box using this code:

Me.sTopic.RowSource = "SELECT SubTopic FROM SubTopics WHERE Topic = " & Me.mTopic & " ORDER BY SubTopic"
Me.sTopic = Me.sTopic.ItemData(0)

Open in new window


In other words, when one selects a topic from the 'Topics' combo box, the 'SubTopics' combo box then gets a list of sub topics appropriate to the main topic.

However, I have a couple of problems. Firstly, when a new record is started the list of sub topics doesn't change.  The Topic changes back to the first topic in the topics table, but nothing happens to the subtopic box.

Secondly, the combo box doesn't store it's field in the overall record.  Whatever selection you make in the combo box, nothing gets stored.  I wonder if it's because the control source is set to 'unbound' but if I change it to 'subtopic' it gives me an error of invalid value whenever I change the 'topic' in the top box.

Help greatly appreciated!
williamzNetAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
For the first issue, try placing the code you have posted in the form's Current Event, which gets fired as the user navigates between records, opens the form or creates a new record.

For the second issue, yes, you do need to have something in the control source.   It should be the name of the field in the underlying table which will store the combo's value and the data type ot the underlying field should be the same as the datatype of SubTopic in your combo's rowsource.
0
Dale FyeOwner, Developing Solutions LLCCommented:
You would normally have two fields in the underlying record source that define the current selection in each of these combo boxes, one for the TopicID and another for the SubTopicID.  You could do it with a single field, but keeping the combo boxes in synch would be a bit more complicated.

Agree with mbizup that you need code in the Form_Current event to requery the second combo, but again, this will only work if the first combo is bound to a field in the forms record source.

Private Sub Form_Current

    Me.sTopic.RowSource = "SELECT SubTopic FROM SubTopics WHERE Topic = " & NZ(Me.mTopic,0) _
                                       & " ORDER BY SubTopic"
    Me.sTopic = Me.sTopic.ItemData(0)

End Sub

Note that I've added the NZ( ) function to the Rowsource description.  For a record that already contains a value for the mTopic, this will return the mTopic numeric value.  But if no topic has been selected, this would return a zero, which would make the subTopic combo box empty.

   
0
williamzNetAuthor Commented:
Thanks for the help mbizup!

The first part is now cured!!

However, when I set the control source to 'Subtopic' and row source type to Table/Query, I get this error:
'The value you entered isn't valid for this field'

I am not sure what the problem is, but I wonder if it is to do with the way I have structured the database.  I have a table of 'topics' and another table of 'subtopics'.  The subtopics table has a field for topic which is linked to the topics table.

Then in the table I am defining the form for, I have fields for topic and subtopic which are linked to the topic and subtopic tables respectively.  From an accident that happened before, I think that my overall table stored the topic and subtopic as a number - presumably their ids.

However, I still can't work out how save the subtypes.  As noted, I get an error when trying to use this combo box...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
Since you are storing the subtopic numeric ID, your rowsource should be:

Me.sTopic.RowSource = "SELECT SubtopicID, SubTopic FROM SubTopics WHERE Topic = " & Me.mTopic & " ORDER BY SubTopic"

Change the ID field name as needed, and in the Combos properties set:

Column Count = 2
Column Widths 0; 1


0
mbizupCommented:
Alternatively, if you want to store the subtopic and not it's ID, ensure that the data type of the Subtopic Field is TEXT in the table your form is bound to.

(Your code and your combo properties would be the ssame as what you originally had if you use this approach)
0
williamzNetAuthor Commented:
Hi Fyed,

Sorry - only just spotted your help.

I am afraid I don't understand what you are saying about the NZ function.  How does one define it and what does it do?

Also, I am not sure what is bound to what.

When setting up the tables, I set up a topics table that I enter topics into directly.

I also set up a subtopics table with 3 fields - id, topic and subtopic.  The subtopics are entered into the table, but the topics are a lookup field that connects to the topics in the topics table.

Finally, I set up the overall table.  This has several fields, including title (text), description (memo) and fields for topic and subtopic.  These are set as lookup fields and connect to the topic in topics and subtopic in subtopics respectively.

Have I made the binding that you refer to?

Thanks in advance,

Dave
0
williamzNetAuthor Commented:
Hi mbizup,

Just tried your suggestion, and the error message has disspaeared.

However, the form is still not storing the subtopic.  Do you have any suggestion as to what I am doing wrong?

I am happy to store the IDs, but there are no errors to go on.
0
williamzNetAuthor Commented:
Update:

Hmm...

It's sort of working after all.  When I enter a new record, if I look at the subtopic directly in the table it is being stored correctly.

However, if I try browsing through records with the form, not only does the correct subtopic display it also overwrites the subtopic with a new incorrect one...
0
mbizupCommented:
Just verifying -
Which of my suggestions did you try (refer to the post ID number above the comment)?

Is the Control Source property of the combo box set to "subtopic" ?


Are you able to post a sample database (with no personal or otherwise sensitive data)?
0
mbizupCommented:
Try changing your current event code to:

Me.sTopic.RowSource = "SELECT SubTopic FROM SubTopics WHERE Topic = " & Me.mTopic & " ORDER BY SubTopic"
If Me.NewRecord = TRUE Me.sTopic = Me.sTopic.ItemData(0) 

Open in new window


So that the combo value (the stored data)  is only changed for new records

0
mbizupCommented:
Sorry - theres a typu in that last post (missing the THEN in the If statement).

This should fix the issue you're having:

Me.sTopic.RowSource = "SELECT SubTopic FROM SubTopics WHERE Topic = " & Me.mTopic & " ORDER BY SubTopic"
If Me.NewRecord = TRUE THEN Me.sTopic = Me.sTopic.ItemData(0) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
miriam,  you forgot the THEN

if me.NewRecord = True THEN me.sTopic = me.stopic.itemdata(0)

But personally, I would recommend:

if me.NewRecord = True THEN me.sTopic = NULL



0
williamzNetAuthor Commented:
Thanks again guys, I have got it working with this code:

Private Sub Form_Current()
  Me.sTopic.RowSource = "SELECT ID, SubTopic FROM SubTopics WHERE Topic = " & Nz(Me.mTopic, 0) & " ORDER BY SubTopic"
  If Me.NewRecord = True Then Me.sTopic = Me.sTopic.ItemData(0)
End Sub

Private Sub mTopic_AfterUpdate()
  Me.sTopic.RowSource = "SELECT ID, SubTopic FROM SubTopics WHERE Topic = " & Nz(Me.mTopic, 0) & " ORDER BY SubTopic"

Open in new window


The Control Source is set to 'SubTopic'.

The Row Source is left blank.

I have set Column Count = 2 and Column Widths 0; 1 for the subtopic combo box.

I think that the problem was that I had used this code:

Me.sTopic = Me.sTopic.ItemData(0)

Open in new window


for both the browsing code and the update code - I hadn't realsied that this code was updating the database...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.