Populating a form from combo box

[Carlos1815]

I have a form with a combo box that has a row source of a table
containing topic titles. On this form I have a sub form where a user
can add data to a topic. What I'd like to have is to have the user
choose a topic from the combo box, and have the subform populated with
the data from the topic (a separate table for each topic) chosen in
the cbo box. This way, I can avoid having 18 separate subforms, just
one form with a subform that allows the user to enter data into one of
18 chosen topics (tables).

Is there a way to do this? I've looked, and most of the advice given
was populating a form from a combo box using data from the same table
used by the cbo box, not a different table.

Thanks!

Carlos

 

[Klatuu]

Why do you have a separate table for each topic?
What you really should have is one table with a field that identifies the
topic.
Then all you would need to do is include a reference to the combo box in the
subform's record source.
Make the record source a query on the table and in the Topic Field, use the
criteria
Forms!MyFormName!MyComboBoxName

What you are doing will only make things harder for you.

 

[Carlos1815]

Why do you have a separate table for each topic?
What you really should have is one table with a field that identifies the
topic.
Then all you would need to do is include a reference to the combo box in the
subform's record source.
Make the record source a query on the table and in the Topic Field, use the
criteria
Forms!MyFormName!MyComboBoxName

What you are doing will only make things harder for you.
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

I understand, this is my first project and I'm really starting to see
where my mistakes are and how they affect the project. However, I'm
kind of at a point of no return with this project, so I just need to
make what I have work as best as I can. The reason why I have a
separate table for each topic is that the designers who will use this
project want the ability to shuffle the pages around which requires a
resort of the pageID's since I didn't want to actually move the data
around; this would be exceedingly more difficult with multiple topics
having multiple pages that need to be sorted on one table. At least,
that sounds well beyond my pay grade...

For my original question, I used this code:

Private Sub Topic_Name_AfterUpdate()

If Not IsNull(Me.Topic_Name) Then
Select Case Me.Topic_Name
Case 1
Me.subfrmTopic1.Form.RecordSource = "Topic1"
Case 2
Me.subfrmTopic1.Form.RecordSource = "Topic2"
Case 3 ... (etc. for each topic)

End Select
End If
End Sub

and this works just as I described I wanted to work.

Carlos

 

[Klatuu]

I understand your delima. The shuffling of pages could be done within one
table. It is a bit of advanced codeing to do so, but what you have as your
solution will work just fine. I would like to suggest you use indention in
your code to make it easier to read. Here is how I would write it:

Private Sub Topic_Name_AfterUpdate()

If Not IsNull(Me.Topic_Name) Then
Select Case Me.Topic_Name
Case 1
Me.subfrmTopic1.Form.RecordSource = "Topic1"
Case 2
Me.subfrmTopic1.Form.RecordSource = "Topic2"
Case 3 ... (etc. for each topic)
End Select
End If

End Sub