Public Variable- Why doesnt this work?

[Sunil Korah]

I have declared 'db;ProgID' as a public variable in a module

I have a form with a combo box in which I choose a particular
programme and the value gets stored as dblProgID. I know this part
works correctly, because MsgBox(dblProgID) at this point displays the
correct value.

I then open a report for which the following query (saved query) is
the record source.

SELECT Programmes.*
FROM Programmes
WHERE (((Programmes.ProgID)=[dblProgID]));

But the query does not take the already set value of 'dblProgID'.
Instead, it pops up the parameter input box asking for dblProgID. Why?

I have solved my problem by building a SQL string concatenating the
value of dblProgID into it. But I would still like to know why this
happens

Sunil Korah

[xzzy]

my 2 loonies, try getting the value from a function

public function GetProgID()

GetProgID = ProgID
end function
then

SELECT Programmes.*
FROM Programmes
WHERE Programmes.ProgID=" & GetProgID() & ";"

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72**************************@posting.google.c om...

I have declared 'db;ProgID' as a public variable in a module

I have a form with a combo box in which I choose a particular
programme and the value gets stored as dblProgID. I know this part
works correctly, because MsgBox(dblProgID) at this point displays the
correct value.

I then open a report for which the following query (saved query) is
the record source.

SELECT Programmes.*
FROM Programmes
WHERE (((Programmes.ProgID)=[dblProgID]));

But the query does not take the already set value of 'dblProgID'.
Instead, it pops up the parameter input box asking for dblProgID. Why?

I have solved my problem by building a SQL string concatenating the
value of dblProgID into it. But I would still like to know why this
happens

Sunil Korah

[Trevor Best]

On 26 Dec 2003 21:19:23 -0800 in comp.databases.ms-access,
hb*****@indiatimes.com (Sunil Korah) wrote:

I have declared 'db;ProgID' as a public variable in a module

I have a form with a combo box in which I choose a particular
programme and the value gets stored as dblProgID. I know this part
works correctly, because MsgBox(dblProgID) at this point displays the
correct value.

I then open a report for which the following query (saved query) is
the record source.

SELECT Programmes.*
FROM Programmes
WHERE (((Programmes.ProgID)=[dblProgID]));

But the query does not take the already set value of 'dblProgID'.
Instead, it pops up the parameter input box asking for dblProgID. Why?

I have solved my problem by building a SQL string concatenating the
value of dblProgID into it. But I would still like to know why this
happens

What John said + why it happens is because variables (whether private
or public/global) are exposed to VBA only, not to SQL. Functions are
exposed to VBA, SQL, macros, and controls.

--
A)bort, R)etry, I)nfluence with large hammer.

[Sunil Korah]

Thanks a lot. I didn't know that. I used to programme mainly in Foxpro
and switched to Access only about an year back. In Foxpro I could use
the variables in SQL and so I was continuing to try the same.

Sunil Korah

Trevor Best <bouncer@localhost> wrote in message news:<a3********************************@4ax.com>. ..

On 26 Dec 2003 21:19:23 -0800 in comp.databases.ms-access,
hb*****@indiatimes.com (Sunil Korah) wrote:

I have declared 'db;ProgID' as a public variable in a module

I have a form with a combo box in which I choose a particular
programme and the value gets stored as dblProgID. I know this part
works correctly, because MsgBox(dblProgID) at this point displays the
correct value.

I then open a report for which the following query (saved query) is
the record source.

SELECT Programmes.*
FROM Programmes
WHERE (((Programmes.ProgID)=[dblProgID]));

But the query does not take the already set value of 'dblProgID'.
Instead, it pops up the parameter input box asking for dblProgID. Why?

I have solved my problem by building a SQL string concatenating the
value of dblProgID into it. But I would still like to know why this
happens

What John said + why it happens is because variables (whether private
or public/global) are exposed to VBA only, not to SQL. Functions are
exposed to VBA, SQL, macros, and controls.