Hiding buttons on form

[thatlem]

I want to create a form with y/n selectors to define which audits I currently wish to run on a set-up form. Then on a second application form, I want to display or hide buttons based on whether the initial audit was checked or not.

I am assuming I would need to define a macro on open form to indicate if [Z]=false, then do not display.

Any suggestions. I don't write code, so other options are preferred. Running Access2007.

Thanks

 

[dkinley]

I don't write code, so other options are preferred.

What if it's your only option?

-dK

 

[thatlem]

I'll consider any options.....
Thanks

 

[dkinley]

Let's start with the forms to check the complexity of what you need to do ... the first form with the checkboxes - FormA and then you have the form where you want hide stuff - Form B.

Is Form A open when you look at Form B or has it already closed?

The second question is, is both Form A and Form B bound to same table or dataset?

-dK

 

[thatlem]

FormA would be a system form which would be defined at setup by the user, but could be altered later. It would be closed at the time that the user accesses FormB.

FormB would be a form with buttons, each of which would open another form C, D, E, etc. for data entry on audits 1,2, ....15. Each of the subsequent data entry forms would tie back to a unique table. Each audit is unique and not related to other audits.

 

[thatlem]

Form B is not bound to any table or dataset

 

[dkinley]

Okay ... I am also supposing that the tables that make up Form A verses Form B (the rest will all follow suit) have some sort of links attached to them. The reason is, you have to establish something on Form B that correlates to form A to decide whether or not to hide a control or not.

This can be approached from several different ways, and even though probably not the most optimitized way to do it since I do not know the full gambit I will propose the DLookup ...

DLookup("FieldNameofCheckbox","TableNameForFormA", _
"[FormAIdentifyingLinkField] = " & Me.txtControlNameonFormBThatHasLink

I am operating on the presumption that you are using autonumbers for your primary and foreign keys on each table. In this expression, you would put the checkbox name of the checkbox you want to check in the first quotes. The second set of quotes the table that the checkbox sits in. The third set is the primary key for the table that has the checkbox. The last bit after the & is the control name of the foreign key of the audit record you are on.

So, now that you have that, on Form B. Go to the properties of the form and on the OnCurrent() Event you can use something like ...

Dim vIsChecked As Variant
 
vIsChecked = Nz(DLookup("FieldNameofCheckbox","TableNameForFormA", _
    "[FormAIdentifyingLinkField] = " & Me.txtControlNameonFormBThatHasLink),3)
 
If vIsChecked = -1 Then
    Me.ControlNameonFormYouWantToSee.Visible = True
    Me.ControlNameonFormYouDoNOTWantToSee.Visible = False
End If

Hope that helps,
-dK

 

[thatlem]

Great, I'll give it a try.

 

[dkinley]

In light of the other post - I told you incorrectly based on that fact and seeing how you have so many buttons, I did a quick demo that is attached.

Seeing how you have alot of command buttons, I simplified the code so there is less effort and to make it look alot cleaner. To be even more efficient, I used the Right function to take advantage of the button name with the data stored in the table to provide the criteria based on the button name. That way if you enter more audit forms later, you will not have to manage the code, but keep the button naming and table entry standard so all you have to do is move buttons around on the form to make them look pretty - the code will do the rest.

You can modify this to meet your needs - but wanted to provide an example of it for full automation. You can dissect the code and see how I set the table for the workings.

-dK

 

[thatlem]

thanks again