Calling variable via button

[Jen0dorf]

Hi



had to take a sideways step on my database to add another search option.

I wrote this code and saved it in a module called DeclareVariable

Option Compare Database

Sub DeclareVariables()
Dim varGraveNo As String
varGraveNo = Me![GraveNo]

End Sub

I then created a form called frmPlotSearch with a text box called GraveNo

Added a button to open another form - frmPlotandGraveNo - based on the query below.

SELECT tblDeceased.GraveID, tblDeceased.Mlink, tblDeceased.Plot, tblDeceased.GraveNo, tblDeceased.Forenames, tblDeceased.Surname, tblDeceased.Age, tblDeceased.YearOfDeath
FROM tblDeceased
WHERE (((tblDeceased.GraveNo)="varGraveNo"));

My logic was the user opens the form types in a grave no say 22 pressed the button and the query runs filtering the results to only include grave no 22

I've now after hours of looking realised I have not included creating my variables. How can I include the creation of the variable in my sequence or am I totaly off track

thanks

Ian

[ItsMe]

You just need to declare your variable as Public and place it in a Standard module.
public gstrGraveNo as string

Then you can set it from behind one of your forms or whatever.
gstrGraveNo = Me![GraveNo]

And retrieve it from anywhere
msgbox gstrGraveNo

If you are going to use a public variable, be sure to assign it to a default in the form or reports OnLoad event.
gstrGraveNo = ""

Just be aware that if your application experiences a Run Time exception, the variable will reset to empty. So you need to error trap.

[Jen0dorf]

Thanks

thats the project for today

Ian

[Jen0dorf]

Hi

working under my ethos of never just using someone elses code I have been searching the web for tutorials about defining and using global/private variables in Access.

I can't seem to find any either at idiots level ( ME) or advanced. I can't even find a definition of the differences between a global and public variable

can someone post some links

thanks

Ian

[ItsMe]

Variables and constants keywords
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

Public Statement
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

[Jen0dorf]

Hi

ok spent all day getting absolutely nowhere. Time for a bottle of wine ok 2 will do.

Went back to the start and created a query based on one table

as below
SELECT tblDeceased.Plot, tblDeceased.GraveNo, tblDeceased.Forenames, tblDeceased.Surname, tblDeceased.YearOfBurial, tblDeceased.GraveID
FROM tblDeceased;
[/CODE]
And of course it worked. I then created a form based on the query and that worked.

I need/want to filter the query so that only records with a specific user entered GraveNo will display.

As per suggestions I created a public variable in a module called

PublicDeclareVariable

with the code

Option Compare Database
Option Explicit
Public varGraveNo As String

I then created an empty form with a text box and a button.

The button opens the form created using my unfiltered query.

In my form with the button and text box I selected the form properties and in the when deactivated option used the code

varGraveNo = Me![GraveNo]

Thinking this would set the public variable.

Lastly I went to my first query and in the conditions added the variable

giving
SELECT tblDeceased.Plot, tblDeceased.GraveNo, tblDeceased.Forenames, tblDeceased.Surname, tblDeceased.YearOfBurial, tblDeceased.GraveID
FROM tblDeceased
WHERE (((tblDeceased.GraveNo)="varGraveNo"));

However it does not work, it runs with no errors but it shows no results no matter what I input ie if I type 21 into my text box and press the button nothing is hown in the results screen.

Off for a glass of the red stuff.

Would appreciate any thoughts

as always

thanks

Ian

[ItsMe]

So you will not be able to resolve the value of the variable directly in a Query Object or an SQL statement.

The following is taking the literal text, "varGraveNo" and comparing it to the field, GraveNo.
WHERE (((tblDeceased.GraveNo)="varGraveNo"));

If the value of varGraveNo is equal to fifteen, you would want your SQL to look like
WHERE (((tblDeceased.GraveNo)="fifteen"));

I know this is not what you want. Just trying to show something here. One option is to incorporate a function. And I guess this is where you started but did not have the global variable set yet. You need both the variable and the Function and I apologize if I side tracked you there.
Create a function that grabs the global variable.

public function fGraveNumber() as string
fGraveNumber = varGraveNo
end function

Then you can use the function within your Query Object and an SQL statement. I believe you need to include the parenthesis when using function names in SQL. fGraveNumber() vs. fGraveNumber

WHERE (((tblDeceased.GraveNo)= fGraveNumber()));

[Jen0dorf]

Hi

I'm a couple of glasses down the road so I'll leave it till tomorrow;-)

No you didn't side track me, it's me trying to grasp the concepts to get the results I need confusing everyone else.

THanks for all the help

Ian

ps Malbec is very nice!!!

[ItsMe]

OK, let us know. And yeah, I am a fan of the reds, too. However, some of those >15% bottles can get pricey.

[Jen0dorf]

Hi

just got back to work after a spell under the weather!!

Some kind person replied with the suggestion of using temporary variables

Can't find the post but it solved all my problems!! Thanks to the unknown person

In case anyone else requires info I found this free tutorial well worth a look if like me you need visual elucidation


https://www.youtube.com/watch?v=FIYZjoleNT8

cheers

Ian