using global variables in stored procedures

[KevCon]

if I have a global variable:

Public Client_ID

how can I get a SQL statement to reference that?


SELECT ...
WHERE SysClientID = (Client_ID)

 

[cmmrfrds]

Can you at least show the definition part of your stored procedure.

 

[KevCon]

I may have been asking the wrong question, sorry.

I had problems with ServerFilter getting stuck....read about that a bit, so then I abandoned that and tried setting it through code, which worked but briefly showed the old record before it refreshed, which bothered me.
so then I tried an SP (using other examples I found here) and it was nothing but problems, and I can't see putting 57 lines of code in to open to a single record...!!

I found a better way to bind a filtered record source to a form -- SQL string in the OnOpen event for RecordSource using a Global as a parameter...it appears to work well.

Me.RecordSource = "SELECT * " _
& " FROM dbo.tblCustomers" _
& " WHERE (Customer_ID = " & Cust & ")"

-- global 'Cust' having been set from the prior form that opened this form

only thing then is I have to set all the subforms' Child & Master links to nothing, setting them through code instead.
Seems they look to establish the links prior to the OnOpen event, causing a parameter prompt.

 

[cmmrfrds]

Did you try creating the sub form first with the query and then run it once so that there are some fields to bind with. Then, run the main form 1 time and then drop the sub form into the main form. At this point try looking for the link fields.

 

[belovedcej]

I've never tried it with a form, but I have with reports. I imagine it works the same way.

In the properties dialog for the report I put the name of the stored procedure as the record source (don't put "dbo." first - just the name of the SP).

Then on the "input parameters" I put the following:
@MonthCleared = [Forms]![frmMonth]![txtMonth]