Public Variable- Why doesnt this work?

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

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" <hblinus@indiatimes.com> wrote in message
news:7230d2ed.0312262119.77e0bb8e@posting.google.c om...[color=blue]
> 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[/color]

On 26 Dec 2003 21:19:23 -0800 in comp.databases.ms-access,
hblinus@indiatimes.com (Sunil Korah) wrote:
[color=blue]
>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[/color]

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.

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:<a33ruvcc04rm7gelg2v0snrar1nk3vvojk@4ax.com>. ..[color=blue]
> On 26 Dec 2003 21:19:23 -0800 in comp.databases.ms-access,
> hblinus@indiatimes.com (Sunil Korah) wrote:
>[color=green]
> >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[/color]
>
> 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.[/color]