Code help to filter subform

[parkerkay]

I have a form where a user defines a checklist. There is a ChecklistName combo box where the names of all previously defined checklists, and where you would type the name of the new one (LimitToList = No). I am trying to get the subform to Update with the selection of each checklist in that combo box. Right now, I can cycle through records with the record navigator...it works perfectly, each name matches the checklist. However, when I go to select a checklist from the drop down menu...it just changes the name of the showing checklist to whatever I picked (very annoying), and changes the data in the tables to reflect such changes. I've tinkering with this trying to figure it out on my own...but, I'm missing something.

I wrote a line of code (modeled after another's) to do this...as another form I have KIND OF does what I'm trying to do here on this form. Here is the code I am using in the AfterUpdate event

Dim connDB As ADODB.Connection
Dim qry As New Command
Dim sql As String

Set connDB = CurrentProject.Connection
qry.ActiveConnection = connDB

sql = "SELECT CategoryID, BehaviorID FROM tblChecklistDetails WHERE ChecklistNameID = " & Me.ChecklistNameID & ""

qry.CommandText = sql
qry.Execute

Me.sfrmChecklist.Requery

connDB.Close
Set qry = Nothing
Set connDB = Nothing

A checklist consists of a name, categories, and behaviors. The tables are as follows:

tblChecklistName
ChecklistNameID
ChecklistName

tblBehavior
BehaviorID
Behavior

tblCategory
CategoryID
Category

tblChecklist
ChecklistID
CategoryID
BehaviorID
ChecklistNameID

Let me know if this is not clear and you need more info!

Thanks for all your help!!

 

[CautionMP]

parkerkay,
Two things.

...it just changes the name of the showing checklist to whatever I picked...

It sound like the ComboBox is still bound to a field in the Form Recordset. If your just using the ComboBox as a filter you will probably want to leave the Control Source blank.

...and changes the data in the tables to reflect such changes

Sounds like you have a Master/Child relationship between the Form and SubForm. Check the Properties > Data tab and remove the link if it exists.

Hope this helps,
CMP