Visible Property

[jbento]

All,
I have a form with some sub forms on it. I have a text field (pull down list with values). The values are: Working, Submitted, Approved, & Not Approved. Based on a selection I want certain fields in a subform to be visible or not to be visible, but it is not working because the code I am using actually shows the fields in the subform no matter what. Here is the code I am using:

If status.Value = "Working" Then
Forms!frm_PAF!frm_Products_Subform.Form!Add.Visible = False
Forms!frm_PAF!frm_Products_Subform.Form!Date.Visible = False
End If

If status.Value = "Submitted" Then
Forms!frm_PAF!frm_Products_Subform.Form!Add.Visible = True
Forms!frm_PAF!frm_Products_Subform.Form!Date.Visible = True
End If

In the first case above I want the Add and Date field not to be visible, but the code is not working, because it shows on the form.

In the second case above I want the Add and Date field to show, which it does, but I don't think the code is right based on the first selection.

Can someone help me with this?

I would appreciate any help,

Thanks,


Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[RoyVidar]

Hi!

Some thoughts:

I'm assuming the status combo is on your main form.

Perhaps a syntax like:

[tt]Me!frm_Products_Subform.Form!Add.Visible = False[/tt]

would produce more results? The Me keyword references current form.

Control names:
Do you have controls and/or field named "Date" and "Add"? That should be avoided, cause they are reserved words in Access/VBA.

Some recommend using the keyword Me to refer to any control, so perhaps also use [tt]me!status[/tt]

Since you say you (at present) have four alternative values of the status combo, perhaps consider checking all possibilities.

The select case might be an alternative, short snippet to demonstrate (I've renamed the 'Add' control txtAdd - and VBA is case sensitive -> ucase)

[tt]Select Case Ucase(Me!Status)
Case "WORKING"
Me!frm_Products_Subform.Form!txtAdd.Visible = False ' ...
Case "NOT APPROVED"
' ...
Case Else
'...
End Select[/tt]

Hope some of these thoughts might provide you with a solution. If not, please report back. Then pls state current code and which event (are you using the combos after update?).

Roy-Vidar

 

[KenReay]

Hi

Try

If Status is no the main form:

If status.Value = "Working" Then
frm_PAF!frm_Products_Subform.Form.[Add].Visible = False
frm_PAF!frm_Products_Subform.Form![Date].Visible = False
End If

If Status is no the subform:

If status.Value = "Working" Then
[Add].Visible = False
[Date].Visible = False
End If

But two points

You are using reserved words as field names (Add and Date), this will almost always end in tears, I suggest you change the names

Where are you executing this code?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GoDawgs]

Also try requerying the subform once you've set the controls visible or hidden...

 

[jbento]

My fault on not giving enough information.

First of all I changed the fields names to: AddProduct & DateProduct.

There is a command button on the main form that is titled: Submit.

When that button is depressed that is when I want the code to run.

The subforms are in datasheet view, which as you know looks like a spreadsheet out of excel.

I tried your suggestions earlier, but the columns are still showing.

These are the suggestions are tried:
Me!frm_Products_Subform.Form!Add.Visible = False

If status.Value = "Working" Then
frm_PAF!frm_Products_Subform.Form.[Add].Visible = False
frm_PAF!frm_Products_Subform.Form![Date].Visible = False
End If

If status.Value = "Working" Then
[Add].Visible = False
[Date].Visible = False
End If

Could there be something that is needed to be done different since I have the subforms in datasheet view?




Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[RoyVidar]

Hi!

Try changing to continous forms. I'm not sure whether the hidden property is available in datasheet view.

Roy-Vidar

 

[jbento]

I found this code that works:

From the Mainform use the following syntax to hide a specfic column in the datasheet subform.
[frm_Products_Subform].[Form]![AddProduct].ColumnHidden = True

Then to unhide it when needed ... use:
[frm_Products_Subform].[Form]![AddProduct].ColumnHidden = False

That code works beautifully if I have the following for my command button:
[frm_Products_Subform].[Form]![AddProduct].ColumnHidden = True
[frm_Products_Subform].[Form]![DateProduct].ColumnHidden = True

but if I have the following, it doesn't work:
If status.Value = "Working" Then
[frm_Products_Subform].[Form]![AddProduct].ColumnHidden = True
[frm_Products_Subform].[Form]![DateProduct].ColumnHidden = True
End If

What could be wrong with the code above to make it not work when I add If status.Value = "Working"?




Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[jbento]

One more thing.

This is not on a command button.

It is on the OnLoad event of the form.

Sorry about that.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[RoyVidar]

Proven wrong again;-) Never seen that one - goodie!

It might be leading or trailing spaces (Trim$() function) or perhaps a upper/lover case thingie (se my first post).

Try seting a breakpoint in the line containing me!status (hit F9 when cursor is placed in the line), when the form opens, you should "thrown" to the code window, and be able to read the value be howering the mouse over me!status). This will enable you to check both the value of Me!status AND when the event fires.

If the users are permitted to move between records - or it's a possibility that they might change the selection in the combobox, you might consider using the forms on current event or the combos after update event (or both)(on load triggers only when the form loads/opens)

Roy-Vidar

 

[jsrothaus]

Try this:
function statusIs()
If status = "Working" Then
Forms!frm_PAF!frm_Products_Subform.Form.Add.Visible = False
Forms!frm_PAF!frm_Products_Subform.Form.Date.Visible = False
End If

If status = "Submitted" Then
Forms!frm_PAF!frm_Products_Subform.Form!Add.Visible = True
Forms!frm_PAF!frm_Products_Subform.Form!Date.Visible = True
End If
end function

Call it like:

private sub form_load()
call statusIs
end sub

or

private sub status_afterUpdate()
call statusIs
end sub
-Jerry

 

[jbento]

All,
This was killing me, so I just changed it to a Continuous Form.

You know how us men get, because we always try to figure out our problems, but I'm too tired of the datasheet view not working.

The Continuous form works PERFECT!!!!

THANK YOU ALL SO VERY MUCH,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!