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?
 
mbizupConnect With a Mentor Commented:
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
 
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 FyeCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
Dale FyeConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.