Open pop-up form for certain client

[Guest]

I have a process that I need to move from an Access .mdb database to an
Access .adp project and I'm having trouble with one part.

I have a macro that opens a pop-up form for the client on the main form and
shows a "count" of how many employees that client has. Then I copy that
count, close the pop-up and paste the number in a text box on the main form.
It works perfectly in the mdb, but the pop-up won't open in the .adp.

The error I get says "invalid syntax near !". If I try to open the form
manually, I get the same thing. The datasourse for the form is below, and it
works in the .mdb:

SELECT COUNT(RandomEmployeeList.ClientNumber) AS CountOfClientNumber,
RandomEmployeeList.ClientNumber
FROM RandomEmployeeList
GROUP BY RandomEmployeeList.ClientNumber
HAVING (((RandomEmployeeList.ClientNumber) = Forms ! Form1 !
clientNumber));

The error appears to be caused by the "HAVING" line. Can anybody help me
with how to make this work the the project?
Thanks!
Susan

 

[Robert Morley]

Because the query is being executed on the server, it has no idea what
"Forms!Form1!clientNumber" is. You'll have to change the recordsource
dynamically at run-time (probably in the OnOpen event is best) and do the
following:

Me.RecordSource = "SELECT COUNT(RandomEmployeeList.ClientNumber) AS
CountOfClientNumber, RandomEmployeeList.ClientNumber FROM
RandomEmployeeList GROUP BY RandomEmployeeList.ClientNumber HAVING
RandomEmployeeList.ClientNumber = " & Forms!Form1!clientNumber



Rob

 

[PBsoft]

Because the query is being executed on the server, it has no idea what

"Forms!Form1!clientNumber" is. You'll have to change the recordsource
dynamically at run-time (probably in the OnOpen event is best) and do
the following:

Me.RecordSource = "SELECT COUNT(RandomEmployeeList.ClientNumber) AS
CountOfClientNumber, RandomEmployeeList.ClientNumber FROM
RandomEmployeeList GROUP BY RandomEmployeeList.ClientNumber HAVING
RandomEmployeeList.ClientNumber = " & Forms!Form1!clientNumber

Or, you can use a parameterized stored procedure.