Code help to filter a subform

[parkerk]

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

 

[Call_Me_Sam]

Hi, would this have anything to do with the control/recordsource of the combo box you're using? you mention it changes the name of the checklist to whatever you select?

Do you have a scaled down version you could zip and attach in order to get a better idea of how things link.

 

[parkerk]

OK.

I have attached the relevant aspects of my database. Open frmChecklistSetup...My goal is to get that combo box, when a checklist is selected, to populate the subform with the selected checklist (as in an edit). When I want to add a new record, I think I will add a text box for the new checklist's name (from the tinkering I've done, setting LimitToList=No doesn't allow you to just type the name you want...I think a bit of coding needs to be done).

I appreciate you taking the time to look at things.
Let me know if you need any more info.

Thanks!!

(I just realized when I slimmed my database down, I forgot to add the existing relationships...Each table's primary key links to another table with a foreign key of the same name. So BehaviorID, CategoryID, ChecklistNameID are all 1-to-many to tblChecklist - - hope that wasn't too confusing)

 

[Call_Me_Sam]

OK.

I have attached the relevant aspects of my database. Open frmChecklistSetup...My goal is to get that combo box, when a checklist is selected, to populate the subform with the selected checklist (as in an edit). When I want to add a new record, I think I will add a text box for the new checklist's name (from the tinkering I've done, setting LimitToList=No doesn't allow you to just type the name you want...I think a bit of coding needs to be done).

I appreciate you taking the time to look at things.
Let me know if you need any more info.

Thanks!!

(I just realized when I slimmed my database down, I forgot to add the existing relationships...Each table's primary key links to another table with a foreign key of the same name. So BehaviorID, CategoryID, ChecklistNameID are all 1-to-many to tblChecklist - - hope that wasn't too confusing)

Just having a quick look but your combo box has an entry in the properties box for both ControlSource AND the RecordSource. Removing the ControlSource entry i found you were able to select a record from the Checklist items, which in turn changed the records in the subform. I think because the combo box is named after a field which is used in the link Master/Child of the subform/form then there is a reaction as you change the combo box.

Hopefully will correct me if i'm wrong, but i wouldn't have bound the form to a table, if you're going to provide a combo box to allow choice. The combo can be bound seperately.

Hope this gets you somewhere, sorry it's taken me til now to reply but i had to leave the office early.

oh i couldn't find that extra piece of code you wrote in the AfterUpdate box, did you remove it because you were going to put in a seperate text box?

 

[parkerk]

Sam-

Thanks for the tip. I used your suggestion on the combo box and it behaved perfectly. In order to add a new record, I added a text box to grab the name. So...I laid that textbox on top of the combo box and it LOOKS like it's just a combo box that you can type into. Hopefully, the users don't find figure out a way for that not to work. From where I'm sitting, they'll never know...

Thanks again!!

 

[Call_Me_Sam]

Sam-

Thanks for the tip. I used your suggestion on the combo box and it behaved perfectly.

Quite alright.

In order to add a new record, I added a text box to grab the name. So...I laid that textbox on top of the combo box and it LOOKS like it's just a combo box that you can type into. Hopefully, the users don't find figure out a way for that not to work. From where I'm sitting, they'll never know...

Thanks again!!

LOL i have heard of this trick before but is the extra text box there to add a new record, why not simply makr it up clearly enough so the user knows what controls are for what function? But if it works for you then many say don't fix what isn't broken. Just glad i could help.