Compile Error Syntax Error in Search Form VBA Code (1 Viewer)

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
I have a fairly large search form. The search button is working except for one field which is a combo box. I'm just not sure how to code it. It was suggested by someone on this forum that I also code it the same way that I did for my text boxes. That did not work. I also tried the following:


If IsNull(Me.CommitteeContact) Then
Me.FilterOn = False
Else
Me.Filter = "CommitteeContact = " & Me.CommitteeContact "'"
Me.FilterOn = True
End If


That is giving me the error "Compile Error: Syntax Error"


Can anyone help me with the correct code for a combo box on a search form?


Thank you so much in advance. I'm very much a novice at this.:confused:
 

JHB

Have been here a while
Local time
Today, 16:01
Joined
Jun 17, 2012
Messages
7,732
You are missing a '
Code:
Me.Filter = "CommitteeContact = [B][COLOR=Red]'[/COLOR][/B]" & Me.CommitteeContact "'"
 

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
Thanks, but unfortunately, that still did not work.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 28, 2001
Messages
26,999
Instead of using

Code:
If IsNull(Me.CommitteeContact) Then

try

Code:
If NZ(Me.CommitteeContact, "") = "" Then

It is possible that your control is not null but has a zero-length string, which would cause issues with quoting because the two quotes would appear side-by-side (due to having literally NOTHING between them). That has special meaning to quoting syntax and that could be your culprit.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:01
Joined
Sep 12, 2006
Messages
15,613
Me.Filter = "CommitteeContact = " & Me.CommitteeContact "'"

Can you clarify what this means

What does the red field represent in your form?
What does the blue field represent in your form?

are they numbers or names?
 

JHB

Have been here a while
Local time
Today, 16:01
Joined
Jun 17, 2012
Messages
7,732
You are missing a '
Code:
Me.Filter = "CommitteeContact = [B][COLOR=Red]'[/COLOR][/B]" & Me.CommitteeContact "'"
And a &, (sorry that I haven't seen that in first post.

Code:
Me.Filter = "CommitteeContact = [B][COLOR=Red]'[/COLOR][/B]" & Me.CommitteeContact [B][COLOR=Red]&[/COLOR][/B] "'"
 

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
It got rid of the error but it still did not give me the correct results. It gave me everything, not just the results for the selected Committee Contact.
 

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
Committee Contact is a name. (First Name Last Name)
 

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
It ran but it did not give me the correct results. It gave me all records, not just the correct records for the Committee Contact selected.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Jan 23, 2006
Messages
15,364
Can you post a copy of the database (zip format)?

Remove anything private and/or not related to this specific issue.
 

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
Sorry, I tried to upload it but I get an error message that says security token is missing. I don't know how to fix this.
 

Cronk

Registered User.
Local time
Tomorrow, 02:01
Joined
Jul 4, 2013
Messages
2,770
Having set the filter with
Code:
Filter ="..."
do you turn the filter on?
Code:
FilterOn = True
 

Cronk

Registered User.
Local time
Tomorrow, 02:01
Joined
Jul 4, 2013
Messages
2,770
From your original post
The search button is working except for one field which is a combo box.
Presumably CommitteeContract is the name of your combo.


What is the row source of this combo? Is the bound column in the combo, an index field?


You could include the following immediately after your "Filter=..." line to check exactly what the filter is
Code:
debug.print Me.filter
 

dmace1

Registered User.
Local time
Today, 08:01
Joined
May 3, 2019
Messages
15
It wouldn't let me post my zip file or my accdb file because a security image was missing.

I'm posting this here. Maybe this will help.

This is my code for searching on the whole search form.

Option Compare Database
Option Explicit
Private Sub Command72_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.CaseTitle) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseTitle] Like ""*" & Me.CaseTitle & "*"") "
End If

If Not IsNull(Me.CaseSummary) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseSummary] Like ""*" & Me.CaseSummary & "*"") "
End If

If Not IsNull(Me.CaseType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseType] Like ""*" & Me.CaseType & "*"") "
End If

If Not IsNull(Me.InitiatorType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([InitiatorType] Like ""*" & Me.InitiatorType & "*"") "
End If

If Not IsNull(Me.InitiatorName) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([InitiatorName] Like ""*" & Me.InitiatorName & "*"") "
End If

If Not IsNull(Me.OutsideOrganizationType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([OutsideOrganizationType] Like ""*" & Me.OutsideOrganizationType & "*"") "
End If

If Nz(Me.CommitteeContact, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "CommitteeContact = '" & Me.CommitteeContact & "'"
Me.FilterOn = True
Debug.Print Me.Filter
End If

If Not IsNull(Me.OutsideOrganizationName) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([OutsideOrganizationName] Like ""*" & Me.OutsideOrganizationName & "*"") "
End If

If Not IsNull(Me.Resolution) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([Resolution] Like ""*" & Me.Resolution & "*"") "
End If
If Not IsNull(Me.AdditionalRemarksRegardingResolution) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([AdditionalRemarksRegardingResolution] Like ""*" & Me.AdditionalRemarksRegardingResolution & "*"") "
End If

If Not IsNull(Me.BroughttoCommitteeMtg) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([BroughttoCommitteeMtg] Like ""*" & Me.BroughttoCommitteeMtg & "*"") "
End If

DoCmd.OpenReport "UseOfNameCommitteeSpecialReport", acViewPreview, , strWhere

End Sub

Please let me know if you have any additional thoughts on this. I am very grateful for the help.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:01
Joined
Sep 12, 2006
Messages
15,613
ignoring possible syntax errors, I just can't see how you can filter on this expression

Me.Filter = "CommitteeContact = '" & Me.CommitteeContact "'"
 

Cronk

Registered User.
Local time
Tomorrow, 02:01
Joined
Jul 4, 2013
Messages
2,770
What are you trying to achieve? The filter will be applied to the form whereas the condition generated by your strWhere variable is being applied to a report.


In relation to the filter being applied to the form, you did not provide the combo rowsource. Also could you post the output of the debug.print when the code is run.
 

Users who are viewing this thread

Top Bottom