By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Public Variable- Why doesnt this work?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.