Command Button to requery combobox

[ascendantP]

Hi All,

I have a form that also has a subform. Table tblIdeaScaleME is the parent table that populates data on the form. Table tblDecisions is the child table that is linked to the subform for user entry purposes. Both tables are linked by a primary key, Idea Number. What I would like is to filter out choices in the combobox that has data already in the Moderator Decision field from the child table. For example, if table tblIdeaScaleME has 4 ideas and table tblDecisions has a decision for idea 3 in the Moderator Decision field, then I would like a command button to contain the functionality to requery the combobox to only contain the choices of ideas 1, 2, and 4 since idea 3 already contains information.

This is currently my code for the command button.

Dim sSQL As String
sSQL = "SELECT tblIdeaScaleME.[Idea Number], tblIdeaScaleME.Title, tblIdeaScaleME.Campaign" & _
" FROM tblDecisions INNER JOIN tblIdeaScaleME ON tblDecisions.[Idea Number] = tblIdeaScaleME.[Idea Number]" & _
" WHERE (((tblIdeaScaleME.Campaign) = [Forms]![frmManagementEvaluation]![cboSearchSponsor]) AND ((tblDecisions.[Moderator Decision])=""))" & _
" ORDER BY tblIdeaScaleME.[Date] ASC"
cboIdeaNum.RowSource = sSQL
Me.cboIdeaNum = Me.cboIdeaNum.ItemData(0)

I should note that this is a cascading combobox so cboSearchSponsor is the first combobox that requeries cboIdeaNum to populate information. Right now, when I execute this, there are no choices in my combobox. Hopefully someone point me in the right direction.

Thanks

 

[Extra Mile Data]

Here is the logic that I would use behind the command button:

Step 1
Get a list of values already used in the Moderator Decision field of the records in the subform. I'd suggest using a recordsetclone and looping through the clone records to build a comma separated list. For example, if the first record had 3 and the next had 1, you would end up with a list like this:
strList = "3, 1"

Step 2
Include the list in the WHERE clause of the combo box RowSource:

 "AND tblIdeaScaleME.[Idea Number] Not In(" & strList & ")"

Keep in mind that if your idea numbers are text and not really numbers, you would need to put single quotes around each value as you build the strList variable value.

Carl Connett
Live help and code snippets at extramiledata.com

 

[ascendantP]

If this is the question then requery the combo box in the after update event.

Thanks for the note but that's not specifically the problem. It does requery the combobox but does not requery with the correct data. See my post above for the specifics of my problem.