ERROR 2427 "EXPRESSION THAT HAS NO VALUE.."

[Angelique]

After spending probably two days with a form/subform that displays all the overdue clients in a listbox and as the user selects the > command button the clientname is removed from the listbox and appears to be inserted into the subform row.

The user can then print only those clients which are overdue as they wish. If they decide not to keep the newly added client on the subform, a < command button allows the user to re-add back to the listbox.

All that works except if the user tries to move back to the listbox an empty subform.

I get a Error 2427 and the line of code that it causes this is:

strClient = &quot;&quot;
strClient = strClient & &quot;DELETE DISTINCTROW tblClientSelect.ClientID&quot;
strClient = strClient & &quot;WHERE ((tblClientSelect.ClientID=&quot; & Forms![frmReminders]![frmClientSelected].Form![ClientID] & &quot)&quot;
Set MyQuery = dbCurrent.CreateQueryDef(&quot;&quot;, strClient)
MyQuery.Execute
MyQuery.Close
Me![frmClientSelected].Requery

If I add some error handling for cancel, I get the following error message:
Extra ] in query expression &quot;((tblClientSelected.ClientID=))'

All I want to do is capture the error, provide appropriate code so that the user isn't aware that they are doing something wrong.

Also, I am using DateAdd to find records which are a year old from today's date. Is the correct function?

Angelique

 

[AlexMidd]

Error 2427 appears to be non-trappable, so the query needs to be tested to see where the error is occurring, then modified so that it no longer occurs. The only other way is to write code that tests whether the subform is already empty before the query is run.

For your date function I would use DateDiff, e.g.
DateDiff(&quot;d&quot;,Now(), [DateOfRecord])) < -365

This compares today's date with the specified date, e.g. [DateOfRecord], and returns records that are earlier than 365 days ago. Other periods other than days can be specified, e.g. &quot;m&quot; for months and test for greater than 12 instead of 365. Alex Middleton