Another Access 2010 Combo Box Problem

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.

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.

   

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...

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

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)

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

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.

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...

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)?

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) 

Select allOpen in new window

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

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"

Select allOpen 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)

Select allOpen in new window

for both the browsing code and the update code - I hadn't realsied that this code was updating the database...