Form-Subform Relation

[thehorse]

How can I get my subform to update based on my form selection? That's my question, now let me give details:

I created a form with a single combobox called "LastName". I pulled this from a query called "CompileInfo" that contains 8 different data fields. There are 5 "LastName"s. I "Group By"'s the combo box so only a single occurrence of the "LastName" appears. I also set the default value to " " so nothing appears initially.

Next, I created a second form (to become a subform) as a datasheet containing all fields, including the "LastName" field. Once I get this to work, I'll hide the "LastName". In the wizard, I linked the two LastNames to update the subform.

Two issues driving my question: 1. When I run the form, before I make any selection, the subform filters to one lastname, say "Smith". When I change the "LastName" combobox, the subform does not respond (i.e., it still lists "Smith"). 2. The subform will not let me modify any data.

I tried adding a Me.Requery to several of the fields in the subform.LastName field, but with no prevail.

Hopefully, this makes sense. Thanks in advance for the help. TheHorse

 

[dcurtis]

Put the requery for the subform in the After Update of the combo box on the main form.

 

[thehorse]

I got part of it now. I previously tried to add the Me.Requery to the AfterUpdate of the Combobox as you suggest, but it already contained the following code:

Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ItemID] = '" & Me![Combo0] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

So, how did I get it? Notice the [ItemID]? Access added this code from the wizard, but I don't know why it chose the [ItemID] field. I changed it to [LastName] and voila!

That answers only part of it though. I still have two problems:

1. How do I start with the subform either showing all records or showing no records? When I open the form, the subform automatically filters to "Smith". I want to see either all the names or no names when I open the main form.

2. I still cannot edit the data in the subform.

One more question on this issue, while I'm at it: How can I set up an ad hoc query for my table? I want to have options to filter by any of my subform fields.

Again, thanks for the help.