Applying Filter causes odd runtime error (1 Viewer)

George-Bowyer

Registered User.
Local time
Today, 08:24
Joined
Dec 21, 2012
Messages
178
I have a db where Users can Mark contacts for later reference by clicking a chkMark checkbox on the frmContacts (and in various other ways).

When the checkbox is ticked it creates a new unique dual key record in tblMarkedPeople with fldPeopleID and fldUserID. When the checkbox is unchecked, that record in tblMarkedRecord is deleted.

I have a "continuous forms" form based on qryPeople, where I want to highlight records that are marked.

I have done this by putting the following code in Detail_Paint:

Dim intUser1 As Integer

intUser1 = Form_NavigationForm.cmbUser


If Not DCount("*", "tblmarkedpeople", "fldPeopleID = " & PeopleID & " AND fldUserID = " & intUser1) = 0 Then

txtContactName.ForeColor = 12500670
Address.ForeColor = 12500670

Else

txtContactName.ForeColor = 404040
Address.ForeColor = 404040

End If

(I cannot remember why I went for "If Not DCount = 0" rather than "If Dcount = 1", but I suppose I probably thought I had a reason at the time...)

This does what I want. The marked records are greyed out.

However, when I apply a filter to the form, I get this:

Run-time error '3705':

Syntax error (missing operator) in query expression 'fldPeopleID = and fldUserID = 1'

Even more weirdly, I have to click the [End] button numerous times (sometimes more than 20) to end the debug. (I am pretty sure that the number of clicks depends on the number of records returned by the filter, but it's not as simple as 1 click per record or anything like that.

I regret to have to say that I apply the filter with a Macro in the after update action of txtSearch (which is in the form header).

Macro Error.jpg

I filched this macro from somewhere before I knew how to write any VBA and have never got around to coding it instead. I have no idea whether this would make any difference to this particular error, which seems to make the Detail_Paint code "lose sight" of [PeopleID].

Can anyone please explain why the filter causes this problem and how to fix or get around it?

Many thanks,

George
 

tvanstiphout

Active member
Local time
Today, 00:24
Joined
Jan 22, 2016
Messages
269
I would implement the highlighting using Conditional Formatting. It is not affected by a filter. You can see this in action in the Northwind Dev Edition template, frmProductList.
 

Josef P.

Well-known member
Local time
Today, 09:24
Joined
Feb 2, 2023
Messages
866
Code:
If Not DCount("*", "tblmarkedpeople", "fldPeopleID = " & PeopleID & " AND fldUserID = " & intUser1) = 0 Then
Syntax error (missing operator) in query expression 'fldPeopleID = and fldUserID = 1'
=> PeopleID Is NULL or empty (or not initialized/defined ... if Option Explicit is not set).
 
Last edited:

George-Bowyer

Registered User.
Local time
Today, 08:24
Joined
Dec 21, 2012
Messages
178
I would implement the highlighting using Conditional Formatting. It is not affected by a filter. You can see this in action in the Northwind Dev Edition template, frmProductList.
I tried that first. and it didn't work - which is why I went down the Detail_Paint option.

However, I tried it again, just now, so that I could be absolutely sure that it didn't work - and it did 😵

For completeness, I'd still like to know how to resolve the other method, though...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:24
Joined
Feb 19, 2013
Messages
16,704
this does not make sense

'fldPeopleID = and fldUserID = 1'

'fldPeopleID = what? and fldUserID = 1'
 

George-Bowyer

Registered User.
Local time
Today, 08:24
Joined
Dec 21, 2012
Messages
178
this does not make sense

'fldPeopleID = and fldUserID = 1'

'fldPeopleID = what? and fldUserID = 1'
The code says "If Not DCount("*", "tblmarkedpeople", "fldPeopleID = " & PeopleID & " AND fldUserID = " & intUser1) = 0 Then"

So the "what" is supposed to be [PeopleID]

When you open the form it finds [PeopleID] fine.

When you apply the filter, it can't find it for some reason, hence the blank.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:24
Joined
May 7, 2009
Messages
19,248
you can use Nz() to supply missing values:


If Not DCount("*", "tblmarkedpeople", "fldPeopleID = " & Nz(PeopleID, 0) & " AND fldUserID = " & Nz(intUser1, 0)) = 0
 

Josef P.

Well-known member
Local time
Today, 09:24
Joined
Feb 2, 2023
Messages
866
Suggested troubleshooting:

1.) First check module header:
Code:
Option Explicit

2.) Test values:
Code:
Dim intUser1 As Integer
dim CriteriaString as String
 
   intUser1 = Form_NavigationForm.cmbUser

   debug.print "PeopleID: "; PeopleID

   CriteriaString = "fldPeopleID = " & PeopleID & " AND fldUserID = " & intUser1
   debug.print "CriteriaString: "; CriteriaString

   If Not DCount("*", "tblmarkedpeople", CriteriaString) = 0 Then
 
       txtContactName.ForeColor = 12500670
       Address.ForeColor = 12500670

   Else

       txtContactName.ForeColor = 404040
       Address.ForeColor = 404040

   End If
 

Users who are viewing this thread

Top Bottom