Small Question - VBA & table

[Sniper-BoOyA-]

Good Morning,

Ive got a question.

I have a form which has 2 values.

Value A
Value B

On that form ive made 2 checkboxes

If Checkbox 1 = True Value A.visible = True Else Value A.visible = False
If Checkbox 2 = True Value B.visible = True Else Value B.visible = False

It works great. But now i have to make a report, with the exact same idea. Allthough i would like to use the checkboxes on the form.

Now from what i can see, the value of the checkbox (on the form) has to be saved in a table in order for it to be available for future Reports, right?

So my question is,

Can i just make a table with 2 yes/no fields, and add to the VBA on the form to also set the table.value1 to True IF checkbox1 = True?

If yes, does that table need to be in the query the form uses as source?


Cheers!

Michael Schrijver

 

[vbaInet]

Use the exact same logic you have in the form but this time reference the control in the form.

 

[Sniper-BoOyA-]

Use the exact same logic you have in the form but this time reference the control in the form.

But the thing is, if i lets say

Check checkbox 1. Close form and then open it again. The check in checkbox1 is gone.

Dont i have to,somehow save or memorize the value of the 2 checkboxes, in order to actually use it in a later stage?

 

[vbaInet]

Do you have to close the form?

Use a global variable.

 

[Sniper-BoOyA-]

Do you have to close the form?

Use a global variable.

Yeah i was just thinking about using a variable.

Even though i was hoping not to, its been a while since i had to work with them.

And to be honest, i forgot half of it.

Well, thanks for your quick reply. Ill do some research on how to use those variables.

 

[vbaInet]

Any problems, just fire a quick message back

 

[Sniper-BoOyA-]

Im back ,

The afterupdate events of the 2 checkboxes have to be Public instead of Private right ?

 

[DCrake]

If you want a variable to be totally global you need to declare it in a standard module.

Public MyVariableName as .....

This will be available thoughout the open session in the database and can be called from anywhere in the application.

 

[vbaInet]

Im back ,

That was quick

No. When do you want to run the report? After closing the form?

 

[Sniper-BoOyA-]

Correct. The idea is that the user enters data, then he/she can start the calculations.

Then he/she will go to the Report section, open the report.

 

[vbaInet]

How is the form closed, via a customised button whereby the form's default 'X' close button is disabled? Or they are allowed to close via the 'X' button?

 

[Sniper-BoOyA-]

They click on a customised button.

 

[vbaInet]

Perform the save in the button's click event just before the Close action is called.

 

[Sniper-BoOyA-]

Allright, so i have to make a new module.

Add

Public checkbox1 As Boolean
Public checkbox2 As Boolean

End Module

then go to the form, and on the click event of the close button add

If Me.chk1 = True Then chk1 = checkbox1
If Me.chk2 = True Then chk2 = checkbox2

.. ?

Can't believe i forgot all this. Its been a good 2-3 years. So much for documentation

 

[vbaInet]

Allright, so i have to make a new module.

Add

Public checkbox1 As Boolean
Public checkbox2 As Boolean

End Module

Correct but don't call the variable the same name as your field or control.

then go to the form, and on the click event of the close button add

If Me.chk1 = True Then chk1 = checkbox1
If Me.chk2 = True Then chk2 = checkbox2

.. ?

Can't believe i forgot all this. Its been a good 2-3 years. So much for documentation

Not quite. Simply:

theVariable1 = Me.checkbox1
theVariable2 = Me.checkbox2

 

[Sniper-BoOyA-]

Aah ya ofcourse,

The thing i cant get to work (for some reason) is making a value unvisible on the report.

Not through VBA at least. It sais it cant find the method or dataset.

Example

If checkbox1 = true then me.rondd7.Visible = True

^ .. .. .. .. .. .. .. .. .. .. .. .. ..^
Global Variable ___________ Value

Result : Error..

Seems like only .Value works.

 

[vbaInet]

You're still calling the global variable the same name as your control??

Put the code in the load event of the report. Rondd7 is not the name of the control. Also, Rondd7 should not be the same name as the field.

 

[Sniper-BoOyA-]

the checkboxes are called chk1 and chk 2

The global variables are called Checkbox1 and Checkbox2

The fieldname (in query) is called Rondd7.

The whole idea is to make Rondd7 invisible. But when checkbox1 = True then Rondd7.Visible.

On the form i used the same concept.

On the open event i made the fields invisible

Me.gem7.Visible = False
Me.gemd28.Visible = False
Me.D7.Visible = False
Me.D28.Visible = False

And on the afterupdate events of the checkboxes i did the following:

If Me.chk1 = True Then Me.gem7.Visible = True Else Me.gem7.Visible = False
etc..

So this is what i did so far:

- Made a new module with Public Checkbox1 and Checkbox2 as Boolean

Then on the close event of the form i used :

checkbox1 = Me.chk1
checkbox2 = Me.chk2

So is there still something i am doing wrong ? Because i cant seem to figure out why i cant set Rondd7 to Visible=False. And make it True again when Checkbox1 = True.

 

[JANR]

The fieldname (in query) is called Rondd7

Queries dosen't have a visible property you can switch on and of other than modify the query SQL using QueryDefs object.

Put the code in the load event of the report. Rondd7 is not the name of the control. Also, Rondd7 should not be the same name as the field.

Only forms and reports has a visible property, so base your report on the query and use the reports on_load event to set the visible on and off based on your global checkbox.

JR

 

[vbaInet]

Maybe I should highlight this bit -->

Also, Rondd7 should not be the same name as the field.

Call the textbox txtRondd7 in the report.