Passing variable from form to report

[fredk]

I am having a problem in figuring out how to pass a variable from a form to a report so I can use the varible to assign the recordset of the report - I initially had the code on the report and it worked fine. However, for other reasons, I want to move the code to the form.

I have an option group and the variable "strtest" is assigned a name, depending on the option. The name is used in the were clause of the sql.

I don't understand how to get "strtest" from the form to the report.

Thank you very much for the help!!!!

Fred

 

[MikeLoon]

One way to do it is have the report data source include public a function that will return the value of the control on your form:

Public Function MyValue() as DATATYPE
MyValue = Forms("MyForm&quot.MyControl
End Function

The function will have to be written in a module other than the forms module.

In the query you will include the field:
MyFieldName:MyValue()
Good luck

 

[hwkranger]

mmm.. passing variables between reports and forms i would say is tricky.

this is my suggestion.

1) use a disposable table. meaning, a table you don't ever use, i'd suggest naming it something like usysVariable.

usys automatically makes the table a system table and it's hidden from most users.

then just modify the 1x1 table to hold the variable you need using vba.

2) my 2nd suggestion, which isn't a great one, is using a txt file. you could store your variable to a txt file, (via vba) then have vba read from the txt file. You'd have to be careful, but it's doable.

3) last suggestion is wait for more suggestions

Peace Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[fredk]

Thank you both for the help- Let me digress for one moment and give you the bigger picture which may allow for an easier answer:

My form has an option group on it - I have a function that uses the novell signon to determine who is signed in to the datbase. Each user has different assignments... so for example if Joe Smith is in the database, the option group has different choices than if someone else opens the database. (I used a select case in the form open event to determine the user and assign values to the optionvalues and caption names depending on who is logged on)

The user selects the option and then clicks a button to run the report. In the report open event I have a rather large selec case statement that assigns the selection name based on the option that is selected. Then, I use that variable as the where clause to assign the recordset in the event.

This all works beautiful (thanks to the help from the experts on this site)

However, I wanted to add the name of the option group on the report - So, if someone selects option 1 and the name is "Tools" - I want the report to say it is for "Tools" in the report header. That is where my problem started!

Normally, I would refer back to the form for this but all of the coding is in the open event of the report so I wanted to avoid having the same code in the form and the report.

I thought that if I moved the code to the form I could just pass the variable to the report but maybe there is an easier way to get the name from the form.

I hope I made sense - Thanks so much for the help!!!

Fred

 

[hwkranger]

do as MikeLoon suggested.

make a module, have the module return your value, cut the code from the form, and in place of the open event have it call the module.



Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[fredk]

Yes, I think that is the route - Thank you both for your help! Now to the fun part!!!

Fred