Reference VB variable in script builder

[bluwind]

(RESOLVED)Reference VB variable in script builder

o.k i would like to reference a vb variable stored within a module, within the script builder.

that is to say i have a number stored in a module that i need to check that reference in a query using the script builder.

 

[pbaldy]

You can not refer directly to a variable in a query, as in:

WHERE FieldName = VariableName

You have to create a public function that returns the value of the variable, and call that:

WHERE FieldName = FunctionName()

Functions are available in the Expression Builder.

 

[bluwind]

O.k not working yet, i have created the function which is producing a test msgbox with the correct value in it.. the query is set up with what seems to be the correct code,.

I have a text box on a form whose control source is the query and the variable e.g

vb code =
txtstreet.controlsource = QueryERSelect!street

the query has a primary index, and the query should only be display the record whose primary index is the same as the variable in the function.

i have tested the function it is handling and producing the correct information.

when running this the text box is comming up with "#name", has anyone got any ideas?, i know its complicated and i have explained it badly.

 

[pbaldy]

Does the query return the correct value if run independently? Is the query the record source of the form? If not, you can't simply refer to it that way as the control source of a textbox.

 

[bluwind]

Does the query return the correct value if run independently? Is the query the record source of the form? If not, you can't simply refer to it that way as the control source of a textbox.

No the query is not the record source of the form!, i didnt know that would make such a difference.

can you give me some tips on the workaround or point me in the right direction

 

[bluwind]

and data type mismatch in criteria expression is what i get when trying to run the query independantly

 

[pbaldy]

Can you post a sample db?

 

[bluwind]

o.k here is a copy, warning though my code is poorly presented, and it is my first attempt at a database, there are lots of lose ends. in it, if you load up the main form, and on the tabs go to tab Enforcement requests

its the bottom 4 text boxs


file attached

 

[pbaldy]

Well, there are a number of issues, but see if this is doing what you want. If so, I'll try to remember the changes I made.

 

[bluwind]

that looks great, cant get it working at home, it is my work machine that is configured for it.. the code in the text box looks like what i was after..

Thanks very much for taking the time to help...


what did you think of the program as a whole as my first time?....

will try the new code tomorrow night at work.. cheers again buddy


NOTE* i have had a chance to test it now and it does work great...it was that one sentance in the text box that did it.....