RowSource property on ComboBox on form

[red6000]

Hi I have a form with a combo box.

I would like the source to be 1 of 2 queries, but I can't work out how to do
it. I had put in the 'Rowsource' property field the code below, but it
doesn't work.

Any help appreciated (I'm guess something like merging my 2 queries???)

=If(Forms!QuestionData!QuestionArea = "WFI Queue", [QuestionAreaWFI],
[QuestionsAreaNonWFI])

The SQL for the 2 queries are below:

SELECT Questions.Category
FROM Questions
GROUP BY Questions.Category
HAVING (((Questions.Category) Not Like "*Authorise"));
==================
SELECT Questions.Category
FROM Questions
GROUP BY Questions.Category
HAVING (((Questions.Category) Like "*Authorise"));


Thanks.

 

[Ken Snell \(MVP\)]

Use the Enter event of the combo box to run VBA code that will do what you
seek:

Private Sub ComboBoxName_Enter()
Dim strSQL As Strin
Select Case Forms!QuestionData!QuestionArea
Case "WFI Queue"
Me.ComboBoxName.RowSource = "QuestionAreaWFI"
Case Else
Me.ComboBoxName.RowSource = "QuestionsAreaNonWFI"
End Select
Me.ComboBoxName.Requery
End Sub

 

[Steve Schapel]

Red,

Ken's advice here is good.

Just wanted to comment on the unusual queries you are using, as they
appear to be Totals queries but there is no aggregate function being
used. I would have expected more like this...
SELECT Category
FROM Questions
WHERE Category Not Like "*Authorise"
.... or, if the same category is repeated in the Questions table...
SELECT DISTINCT Category
FROM Questions
WHERE Category Not Like "*Authorise"

By the way, I think you could make one query like this, to use as the
RowSource of the combobox...
SELECT DISTINCT Category
FROM Questions
WHERE (Forms!QuestionData!QuestionArea="WFI Queue" And [Category] Not
Like "*Authorise")
OR (Forms!QuestionData!QuestionArea<>"WFI Queue" And [Category] Like
"*Authorise")
(well, maybe I've got them back to front - you'll know!)