Reference Std Module in Select Query

[geeves1293]

Hello all,

I have a standard module which contains the following

Public MySELLERID As Long

Public MySELLERNAME As String

How can a reference the MySELLERNAME or MySELLERID (If there is a
difference) in the criteria part of a select query?

Any help would be much appreciated as always.

Many Thanks

Geeves1293

 

[Dirk Goldgar]

Hello all,

I have a standard module which contains the following

Public MySELLERID As Long

Public MySELLERNAME As String

How can a reference the MySELLERNAME or MySELLERID (If there is a
difference) in the criteria part of a select query?

Any help would be much appreciated as always.

Didn't I just see Doug Steele answer this question in another thread? To
use the value of a variable in a query, you need to create a function (or
property, but that's more complicated) that returns that value. For
example:

Public MySELLERID As Long
Public MySELLERNAME As String

Function fncSELLERID() As Long
fncSELLERID = MySELLERID
End Function

Function fncSELLERNAME() As String
fncSELLERNAME = MySELLERNAME
End Function

Then, in your query, you can do something like this:

SELECT * FROM Sellers WHERE SellerID = fncMySELLERID()

 

[geeves1293]

Tanks Dirk,

Do I put that code in the saved module under objects?

Thanks

Graham

 

[geeves1293]

I have tried to put the code in below my existing variable names. Then done
the query and it returns:-

'Undefined function 'fncMySELLERID' in expression.'

Is there something I'm missing here.

Thanks again
Graham

PS My wife who is also helping did reply to the earlier thread without my
knowledge. Sorry about this and thanks to Douglas for replying aswell.

 

[geeves1293]

Sorry about all the messages. But I have done it, it works, brilliant!!

Thanks go to Dirk Goldgar, Marshall Barton and Douglas O'Steele.

Best Regards

Graham