Combo Box With Two Conditions

  • Thread starter Thread starter tclarke
  • Start date Start date
T

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.
 
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.
 
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?
 
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
 
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
 
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.
 
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?
 
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.
 
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.
 
Back
Top