Applying Filter causes odd runtime error (1 Viewer)

[George-Bowyer]

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).

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]

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.]

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).

 

[George-Bowyer]

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]

this does not make sense

'fldPeopleID = and fldUserID = 1'

'fldPeopleID = what? and fldUserID = 1'

 

[George-Bowyer]

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]

you can use Nz() to supply missing values:


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

 

[Josef P.]

Suggested troubleshooting:

1.) First check module header:

Option Explicit

2.) Test values:

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