Newbie Q: Retrieve values stored in a module (Access 2000)

[jamesbrown]

Hi.

I have a module with the following:

Option Compare Database

Public strMyName As String

strMyName takes the value of a text field on a form after I click a command button:

strMyName = Me.cboFirstname.Value

The Q is, how do I collect the value of strMyName and use throughout the application (in form lables etc).

Thanks,

jb

[Pasvorto]

I think you have 3 choices:

1. Just hide the form and done't close it. Then refer to the field (ie; formname.fieldname.text)

2. Create a Global variable in a module and move the value to there

3. Create a public variable on the form and hide the form.

[Ecniv]

On the On_Load event of the form put

FillForm

In a module, define a public sub FillForm:

public sub FillForm(byref frmMe as form)
    frmMe.lblMyName.caption = strMyName
end sub

Something like that, with a label lblMyName on each form you place the fillform call on.



Vince

[jamesbrown]

Thanks for the help guys.

I'm working with the code suggested by ecniv, just to expand, how would I then retrieve that value and use in a query to filter on firstnames for example:

strMyName = "James"

SELECT * FROM tblUsers WHERE Firstname = [strMyName]

strMyName being a public sub in a module

Thanks again

jb

[Ecniv]

Depends if you are setting that sql statement via code (string manipulation) or via query/query builder (need a function).

Sql statement - code

strSql = "select blah from blah where blah='" & strMyName &"'"

Note the single quotes - for more info see the second sticky post on the forum lists.


In a query

public function GetMyName() as string
    GetMyName = strMyName
end function

In the query builder - where clause part put - GetMyName
Note: This should work in Access, but not in Sql server etc..


Vince