Open pop-up form for certain client

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
Back
Top