Combo Box With Two Conditions

[tclarke]

I'm trying to open a form from a Combo Box, based on the two
conditions of VenueDetailsID and Withdrawn. The following works fine
for one condition; VenueDetailsID:

Private Sub cboCentreStudentDetails_Click()

If Not IsNull(Me.cboCentreStudentDetails) Then
DoCmd.OpenForm "StudentFrm", acFormDS, , _
"VenueDetailsID=" & Me.cboCentreStudentDetails
Me.Visible = False
End If
End Sub

What I'd like to do is to open the form as above but not display
students who have withdrawn. I I have a Check Box in the StudentFrm
called "cboWithdrawn". I've tried adding a Filter to the DoCmd as
shown below but it has no effect and displays no errors.

Private Sub cboCentreStudentDetails_Click()

If Not IsNull(Me.cboCentreStudentDetails) Then
DoCmd.OpenForm "StudentFrm", acFormDS, "chbxWithdrawn = False", _
"VenueDetailsID=" & Me.cboCentreStudentDetails
Me.Visible = False
End If
End Sub

P.S. I've also tried putting "chbxWithdrawn = False" into the OpenArgs
section of the DoCmd and Me.Withdrawn = Me.OpenArgs in the OnOpen
event in StudentFrm but get a Runtime Error saying "You can't assign a
value to this object", Any suggestions would be much appreciated.

 

[tclarke]

OOPS! The Check Box in the StudentFrm called "chbxWithdrawn" not
"cboWithdrawn".

 

[Joan Wild]

If Not IsNull(Me.cboCentreStudentDetails) Then
DoCmd.OpenForm "StudentFrm", acFormDS, "chbxWithdrawn = False AND " _
"VenueDetailsID=" & Me.cboCentreStudentDetails
Me.Visible = False
End If

Note the removal of the comma.

 

[tclarke]

Hi Joan,

I presume that by removing the comma, you suggest using a 'Multiple
Filter' instead of a 'Filter' and a 'Where Condition'.

When I compiled it, the DoCmd code generated a Compile Error: Syntax
Error.

So I put the DoCmd code on one continuous line (without the
underscore) and it generated Compile Error: Expected End of Statement,
Highlighting "VenueDetailsID=".

I've tried numerous permutations but can't get it right.

Any further suggestions?

 

[Joan Wild]

Sorry that isn't what I meant. Put it all in the Where clause.
If Not IsNull(Me.cboCentreStudentDetails) Then
DoCmd.OpenForm "StudentFrm", acFormDS, , "chbxWithdrawn = False AND
VenueDetailsID = " & Me.cboCentreStudentDetails
Me.Visible = False
End If

 

[tclarke]

Hi Joan,

Couldn't get it to accept the syntax. Tried one 'Where' condition at a
time.
'VenueDetailsID="......' worked OK but 'chbxWithdrawn=False" resulted
in an Enter Parameter Value PopUp for 'chbxWithdrawn'.

I think it's down to restrictions on the number of conditions allowed
in a Where Clause. Tried creating a String, 'strWhere', to get around
it but no joy.

Thanks for your help

 

[Joan Wild]

No it isn't an issue on the number of conditions. What is the name of the
control bound to the 'Withdrawn' checkbox . That is what's causing your
error - it can't find chbxWithdrawn. Select the checkbox in design view and
go to View Properties..Other tab - the name is listed there.

 

[tclarke]

Select the checkbox in design view and went to View Properties..Other
tab. The bound cotrol is definately called chbxWithdrawn. I also
looked at Data..Triple State and tried it with bot Yes and No. It
still came up with the same error. Could there be any other reason for
this?

 

[Joan Wild]

Upon re-reading your original post
DoCmd.OpenForm "StudentFrm", acFormDS, , "Withdrawn = False AND
VenueDetailsID = " & Me.cboCentreStudentDetails

In other words you want to open StudentFrm where 'withdrawn' on StudentFrm
is false - so change it to the name of the field in StudentFrm's
recordsource that corresponds with Withdrawn.

 

[tclarke]

Hi Joan,
Changed the name of the control chbxWithdrawn to correspond with the
field Withdrawn from StudentTbl and altered the code accordingly.
(StudentTbl was the recordsource for StudentFrm).

Works like a dream! The only slight problem was that the code would
only work if it was on one continuous line.

Thanks for your patience, perseverence and expertise.