form vba assistance on if statement

[miken5678]

I am new to vba so still learning and reading up.

I currently utilize the below. This works fine however when i click new records in the split form view or go to next record if the Y is met the first time all records after that are invisible until i go back to the record i changed and alter it to = "n". Is there some sort of reset so that the fields are initially availble for new records? I have tried an after event on form load etc etc to no luck. I am open to suggestions or what I am missing please keep in mind my experience is not great but working on it.

Private Sub ComboStreetDirectoryWindArea_BeforeUpdate(Cancel As Integer)
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub

 

[pbaldy]

Typically you'd want the same code in the form's Current event (or a function called from both places).

 

[miken5678]

I assumed I would have to assign the code to the combo box itself.

 

[miken5678]

Private Sub OnCurrent()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub


if i do this access 07 just crashes repeatedly.

 

[pbaldy]

Well, typically in a situation where the value of a control affects the visibility of other controls, you need code in both places. The update event of the control handles the changing of the value, and the current event handles the changing of records. I can't think of why it would crash, but I haven't used a split form yet. Can you post a sample of the db?

 

[miken5678]

My Goal is was to have the ability when street directory equals y to hide and default certain fields as well as the same thing for some other fields such as the individual or entity field resulting in hiding of other fields below it so figuring this out will help me big time in the creation of the rst.

I also noticed in split for view each time i create an option group manually or via the wizard it has the original data field plus it sticks in a duplicate one that shows the framexxx title.. I have no clue how to get this crud off other than trying to dupcliate the field name if that makes sense.

 

[miken5678]

Here is the attachment

 

[miken5678]

Private Sub combostreetdirectorywindarea_AfterUpdate()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub
Private Sub Form_Current()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub


I did this and it appears to work slightly however if i go back to a record where ="Y" it re appears. But what acts really weird is that the field name labels disappear.. this has me scratching my head.

 

[miken5678]

sheesusm.. i had to utilize all three to get it to work while scrolling through records


Private Sub combostreetdirectorywindarea_AfterUpdate()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub
Private Sub ComboStreetDirectoryWindArea_BeforeUpdate(Cancel As Integer)
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub
Private Sub Form_Current()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Sub



Can you point me in the right direction on how to make a module to reference these?

Also, can the same setup be used to lock the whole form and all cells once say a review is tagged as complete?

 

[pbaldy]

Glad you got it going. Here's the basics of creating a function:

http://www.baldyweb.com/Function.htm

Given what this is doing, I'd have it in the form module rather than a standard module. Then you just call the function from the various events.

Yes, you can also lock the form. One way would be to toggle the AllowEdits property of the form.

Me.AllowEdits = False

 

[miken5678]

When you reference form module is that the same as class module?

Also is it just as simple as copying the form vba into the module without worrying about the before/after events?

 

[miken5678]

I use the following for a module

Option Compare Database
Function StreetDirectoryWindArea()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboOptOutFormInFile = "N/A"
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Function

and I am trying to call it this way

Private Sub combostreetdirectorywindarea_AfterUpdate()
Call StreetDirectoryWindArea
End Sub
Private Sub ComboStreetDirectoryWindArea_BeforeUpdate(Cancel As Integer)
Call StreetDirectoryWindArea
End Sub
Private Sub Form_Current()
Call StreetDirectoryWindArea
End Sub


but i just run into issues any help>?

 

[pbaldy]

One reason I recommended putting it in the form module instead of a standard module was because you can't use "Me." in a standard module. If you want to leave it in a standard module, you'll need to replace Me with a full form reference.

 

[miken5678]

so everything else was right except my form reference when i put it in a module.

Question, how exactly do i do a form specific module? I see how to do modules and class module.

Wait, I think I might finally get what you are saying now. Within the form itself define the function one time then call the function in the before/after events? if so a big duh for me.. guess i just didnt understtand the wording.

 

[pbaldy]

I think you have it now! Sorry for what was probably a poor description.

 

[miken5678]

Paul,

I set this up inside by calling and then just referencing the function but neither are working. I hope this is just a "" or name issue or maybe I am not bringing it up correctly. Can you help me out on this?

Public Function ComboStreetDirectory(strVariable As String, lngVariable As Long) As String
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Function

Private Sub combostreetdirectorywindarea_AfterUpdate()
Function ComboStreetDirectory()
End Function

Private Sub ComboStreetDirectoryWindArea_BeforeUpdate(Cancel As Integer)
Function ComboStreetDirectory()
End Function

Private Sub Form_Current()
Function ComboStreetDirectory()
End Function

 

[pbaldy]

You function is calling for 2 input parameters, which you don't provide. Since you don't use them, change the function declaration to:

Public Function ComboStreetDirectory()

Also you don't use the word function to call it. Just the name:

Private Sub combostreetdirectorywindarea_AfterUpdate()
ComboStreetDirectory()
End Function

 

[miken5678]

Thanks that worked perfectly.

I notice everything works fine when using the forward and back buttons in the form itself however If i utilize the split form part it is as if it skips the on form button and those cells referenced tend to stick on either visible or invisible and do not refresh unless i use the manual forward/back record.

Is there another item in need to reference similiar to Form_current but instead for the split form version?

this is what I came up with thus far..

Public Function StreetDirectory()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Me.ComboOptOutFormInFile.Visible = False
Me.ComboUWDocumentException.Visible = False
Me.ComboPolicyholderIndividualEntity.Visible = False
Else:
Me.ComboOptOutFormInFile.Visible = True
Me.ComboUWDocumentException.Visible = True
Me.ComboPolicyholderIndividualEntity.Visible = True
End If
End Function

Public Function IndividualEntity()
If Me.ComboPolicyholderIndividualEntity = "E" Then
Me.optNameInsuredSignedForm.Visible = False
Me.optAdditionalInsuredSignedForm.Visible = False

Else:
Me.optNameInsuredSignedForm.Visible = True
Me.optAdditionalInsuredSignedForm.Visible = True

End If
End Function

Private Sub combostreetdirectorywindarea_AfterUpdate()
StreetDirectory
End Sub

Private Sub ComboStreetDirectoryWindArea_BeforeUpdate(Cancel As Integer)
StreetDirectory
End Sub

Private Sub Form_Current()
StreetDirectory
IndividualEntity
End Sub

Private Sub combopolicyholderindividualentity_AfterUpdate()
IndividualEntity
End Sub

Private Sub combopolicyholderindividualentity_BeforeUpdate(Cancel As Integer)
IndividualEntity
End Sub

 

[miken5678]

found the issue. Have to actually reference the form instead of the me.youritem or else selecting the record in the subform wont update my visible items

http://bytes.com/topic/access/answers/735432-access-2007-split-forms-subforms


Public Function StreetDirectory()
If Me.ComboStreetDirectoryWindArea = "Y" Then
Forms!WindOptOutV11.ComboOptOutFormInFile.Visible = False
Forms!WindOptOutV11.ComboUWDocumentException.Visible = False
Forms!WindOptOutV11.ComboPolicyholderIndividualEntity.Visible = False
Else:
Forms!WindOptOutV11.ComboOptOutFormInFile.Visible = True
Forms!WindOptOutV11.ComboUWDocumentException.Visible = True
Forms!WindOptOutV11.ComboPolicyholderIndividualEntity.Visible = True
End If
End Function
Public Function IndividualEntity()
If ComboPolicyholderIndividualEntity = "E" Or Null Then
Forms!WindOptOutV11.optNameInsuredSignedForm.Visible = False
Forms!WindOptOutV11.optAdditionalInsuredSignedForm.Visible = False

Else:
Forms!WindOptOutV11.optNameInsuredSignedForm.Visible = True
Forms!WindOptOutV11.optAdditionalInsuredSignedForm.Visible = True

End If
End Function

 

[miken5678]

My next question is Normally in 03 I would split the db and the users would all utilize the same form. This time short of using a machine name or user logon name users are not supposed to be able to see or view others records. The easiest solution I could come up with is making 6 forms each with its own filter. However does having six forms connected to a back end present an issue? I see it acting like one form being shared among many but you never know with access.

The only other option would be to setup a filter based on user name and make them put in a password.