Constant expressions error

[Wullie]

I'm sure I'm missing something simple in this, but needs some help.

I have a form with 3 drop-down combo-boxes, which a subform is populated
with a list of questions (which has another subform which will populate the
answer, but not got that far yet) from the selections made from the
drop-downs.

I had a look around and found
http://www.microsoft.com/office/com...a49c&mid=1ea934e4-8065-49ed-b9f6-814fd63ea49c helpful to start me on my way.

I have made a few adjustments (to filter on 3 selections rather than 1) and
created the following code

---------------------

Private Sub QuestionCategoryType_AfterUpdate()

Dim QuestionSchoolType As String
Dim QuestionAreaType As String
Dim QuestionCategoryType As String

Const strcStub = "SELECT Question FROM QuestionResolution "
Const strcTail = " ORDER BY QuestionResolution.Question;"

If Not IsNull(Me.QuestionSchoolType) Then
QuestionSchoolType = "WHERE (SchoolTypeID = """ &
Me.QuestionSchoolType & """)"
End If
If Not IsNull(Me.QuestionAreaType) Then
QuestionAreaType = " AND (AreaID = """ & Me.QuestionAreaType & """)"
End If
If Not IsNull(Me.QuestionCategoryType) Then
QuestionCategoryType = " AND (CategoryID = """ &
Me.QuestionCategoryType & """)"
End If

Me.OpenQuestionsQuery.Form.RecordSource = strcStub & QuestionSchoolType
& QuestionAreaType & _
QuestionCategoryType & strcTail

End Sub

---------------------

When I change the 3rd drop-down, I get a 'Constant Expression required'
error, highlighted on strcStub in the section of the
Me.OpenQuestionsQuery.Form.RecordSource line.

Any advice as to why this is not getting picked up as a Constant, after
being declared as such further up, would be greatly appreciated.

Thanks

 

[Dirk Goldgar]

I'm sure I'm missing something simple in this, but needs some help.

I have a form with 3 drop-down combo-boxes, which a subform is populated
with a list of questions (which has another subform which will populate
the
answer, but not got that far yet) from the selections made from the
drop-downs.

I had a look around and found
http://www.microsoft.com/office/com...a49c&mid=1ea934e4-8065-49ed-b9f6-814fd63ea49c
helpful to start me on my way.

I have made a few adjustments (to filter on 3 selections rather than 1)
and
created the following code

---------------------

Private Sub QuestionCategoryType_AfterUpdate()

Dim QuestionSchoolType As String
Dim QuestionAreaType As String
Dim QuestionCategoryType As String

Const strcStub = "SELECT Question FROM QuestionResolution "
Const strcTail = " ORDER BY QuestionResolution.Question;"

If Not IsNull(Me.QuestionSchoolType) Then
QuestionSchoolType = "WHERE (SchoolTypeID = """ &
Me.QuestionSchoolType & """)"
End If
If Not IsNull(Me.QuestionAreaType) Then
QuestionAreaType = " AND (AreaID = """ & Me.QuestionAreaType &
""")"
End If
If Not IsNull(Me.QuestionCategoryType) Then
QuestionCategoryType = " AND (CategoryID = """ &
Me.QuestionCategoryType & """)"
End If

Me.OpenQuestionsQuery.Form.RecordSource = strcStub & QuestionSchoolType
& QuestionAreaType & _
QuestionCategoryType & strcTail

End Sub

---------------------

When I change the 3rd drop-down, I get a 'Constant Expression required'
error, highlighted on strcStub in the section of the
Me.OpenQuestionsQuery.Form.RecordSource line.

Any advice as to why this is not getting picked up as a Constant, after
being declared as such further up, would be greatly appreciated.

Thanks

I don't like the fact that you have variable names that are the same as
control names, but I don't think that's the problem (though you should
change them). It is clear, though, that your SQL will be malformed if the
QuestionSchoolType control is Null, because it's the code for
QuestionSchoolType that includes the WHERE keyword. I suggest you modify
your code like this:

'----- start of revised code -----
Private Sub QuestionCategoryType_AfterUpdate()

Dim strQuestionSchoolType As String
Dim strQuestionAreaType As String
Dim strQuestionCategoryType As String
Dim strWhere As String

Const strcStub = "SELECT Question FROM QuestionResolution "
Const strcTail = " ORDER BY QuestionResolution.Question;"

If Not IsNull(Me.QuestionSchoolType) Then
strQuestionSchoolType = _
" AND (SchoolTypeID = """ & Me.QuestionSchoolType & """)"
End If
If Not IsNull(Me.QuestionAreaType) Then
strQuestionAreaType = _
" AND (AreaID = """ & Me.QuestionAreaType & """)"
End If
If Not IsNull(Me.QuestionCategoryType) Then
strQuestionCategoryType = _
" AND (CategoryID = """ & Me.QuestionCategoryType & """)"
End If

strWhere = _
strQuestionSchoolType & _
strQuestionAreaType & _
strQuestionCategoryType

If Len(strWhere) > 0 Then
' Replace leading " AND " with " WHERE ".
strWhere = " WHERE " & Mid$(strWhere, 6)
End If

Me.OpenQuestionsQuery.Form.RecordSource = _
strcStub & strWhere & strcTail

End Sub
'----- end of revised code -----

 

[Wullie]

Thanks Dirk,

That code works (or at least the error has disappeared).

One furtther thing is that it does not populate the listbox in the subform.
Can you advise how I get the sql query to populate the specific field
(SelectQuestion) within the form?

Do I amend the Me.OpenQuestionsQuery.Form.RecordSource value or do I need
another step to populate the field?

Thanks

 

[Wullie]

I've now changed this to being all on one form rather than a subform within a
subform within a form and looking to populate the first control from the
selected drop-downs.

 

[Dirk Goldgar]

Thanks Dirk,

That code works (or at least the error has disappeared).

One furtther thing is that it does not populate the listbox in the
subform.
Can you advise how I get the sql query to populate the specific field
(SelectQuestion) within the form?

Do I amend the Me.OpenQuestionsQuery.Form.RecordSource value or do I need
another step to populate the field?

I'm afraid I'm not following you, as this is the first time you've mentioned
a list box, so far as I can see. I don't know where this list box stands in
relation to everything else. Also, I see from your followup message that
you've changed the setup. If you're still having problems, you'd better
post the details of your current setup, the relevant code, and the
difference between how it currently behaves and how you want it to behave.

 

[Wullie]

Hi Dirk,

Sorry for the chopping and changing and the confusion.

Here's what I have.

-----------

I am trying to set up a Knowledge database (not about access thankfully) and
at the moment have a form with 3 combo-boxes at the top of the page where a
person can narrow down the question (by school type, area type and category
type).

Once an option from these 3 has been selected, I want a list of questions
that fall into those categories to appear in a box below (first listbox).

After a question is selected from this, the relevant answer should then
appear in another box below this (currently a listbox, but maybe this should
be a text box).

Hope that makes more sense.

At the moment, I have the following code behind the form, but to be honest I
think I've ended up confusing myself and have no problem starting from
scratch if you can suggest a better solution.

Private Sub QuestionCategoryType_AfterUpdate()

Dim strQuestionSchoolType As String
Dim strQuestionAreaType As String
Dim strQuestionCategoryType As String
Dim strWhere As String

Const strcStub = "SELECT Question FROM QuestionResolution "
Const strcTail = " ORDER BY QuestionResolution.Question;"

If Not IsNull(Me.QuestionSchoolType) Then
strQuestionSchoolType = _
" AND (SchoolTypeID = """ & Me.QuestionSchoolType & """)"
End If
If Not IsNull(Me.QuestionAreaType) Then
strQuestionAreaType = _
" AND (AreaID = """ & Me.QuestionAreaType & """)"
End If
If Not IsNull(Me.QuestionCategoryType) Then
strQuestionCategoryType = _
" AND (CategoryID = """ & Me.QuestionCategoryType & """)"
End If

strWhere = _
strQuestionSchoolType & _
strQuestionAreaType & _
strQuestionCategoryType

If Len(strWhere) > 0 Then
' Replace leading " AND " with " WHERE ".
strWhere = " WHERE " & Mid$(strWhere, 6)
End If

' Me!SelectQuestion.RecordSource = strcStub & strWhere & strcTail
Me.SelectQuestion = strcStub & strWhere & strcTail

End Sub


Private Sub SelectQuestion_AfterUpdate()

Dim strAnswerSchoolType As String
Dim strAnswerAreaType As String
Dim strAnswerCategoryType As String
Dim strWhere As String

Const strcAnsStub = "SELECT Resolution FROM QuestionResolution "
Const strcAnsTail = " ORDER BY QuestionResolution.Resolution;"

If Not IsNull(Me.SelectQuestion) Then
strAnswerSchoolType = _
" WHERE (SchoolTypeID = """ & Me.SelectQuestion & """)"
End If

Me!AnswertoQuestion = strcAnsStub & strAnsSchoolType & strcAnsTail

End Sub