Cascading Field Visibility (code/event reference problem?)

[sniffingvixen]

Hey all,

I just started using Access about two weeks ago and am managing for the most part. Just now though, I've been stumped.

I'm using the Function below to ensure that data entry doesn't occur out of order. In other words, additional fields aren't visible until the currently visible field(s) have data in them.

I call the function for each of the 10 fields in response to AfterUpdate and Change events. I also call it in response to the Form's Current event.

I have similar code for other fields that works well. The difference is that their visibility doesn't involve successive field references (e.g., check Field1 to decide on Field2, then check Field2 to decide on Field3, etc.).

The code below works well until I try to delete an entry.
Suppose:
Enter "Peru" into Area00, Area01 appears. (good!)
Enter "Italy" into Area01, Area02 appears. (good!)
But, delete "Italy" and Area02 remains visible. (BAD!)

I don't even know if the problem is with my code or my event references. Can anyone help?

Thanks,
j

Public Function CascadingAreaVisibility()

'Block 1
If Me.Area00 = "" Then
Me.Area01 = ""
Me.Area01.Visible = False

Else 'Block 2
Me.Area01.Visible = True
If Me.Area01 = "" Then
Me.Area02 = ""
Me.Area02.Visible = False

Else 'Block 3
Me.Area02.Visible = True
If Me.Area02 = "" Then
Me.Area03 = ""
Me.Area03.Visible = False

Else 'Block 4
Me.Area03.Visible = True
If Me.Area03 = "" Then
Me.Area04 = ""
Me.Area04.Visible = False

Else 'Block 5
Me.Area04.Visible = True
If Me.Area04 = "" Then
Me.Area05 = ""
Me.Area05.Visible = False

Else 'Block 6
Me.Area05.Visible = True
If Me.Area05 = "" Then
Me.Area06 = ""
Me.Area06.Visible = False

Else 'Block 7
Me.Area06.Visible = True
If Me.Area06 = "" Then
Me.Area07 = ""
Me.Area07.Visible = False

Else 'Block 8
Me.Area07.Visible = True
If Me.Area07 = "" Then
Me.Area08 = ""
Me.Area08.Visible = False

Else 'Block 9
Me.Area08.Visible = True
If Me.Area08 = "" Then
Me.Area09 = ""
Me.Area09.Visible = False
Else 'Block 10
Me.Area09.Visible = True

End If 'End Block 9
End If 'End Block 8
End If 'End Block 7
End If 'End Block 6
End If 'End Block 5
End If 'End Block 4
End If 'End Block 3
End If 'End Block 2
End If 'End Block 1

End Function

 

[TheAceMan1]

How are ya sniffingvixen . . .

What you want can be done, but it generates so much extra code not needed. Consider this:

[blue]Using the forms BeforeUpdate event you validate all the fields of interest, stopping on any failure (using the [purple]Cancel[/purple] arguement) and setting focus to that control. If it has to be in sequence then you simply validate so![/blue]

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

 

[danvlas]

For i = 1 to 9
If Len(Nz(Me("Area" & Format(i-1,"00")),""))= 0 Then
me("Area" & Format(i,"00")) = Null
me("Area" & Format(i,"00")).Visible = False
End If
Next

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[sniffingvixen]

AceMan and DanVlas,

I can't get either of your suggestions to work.

Can you be more explicit as to how to incorporate them?

Remember! I'm a novice!

Thanks,
j

 

[TheAceMan1]

sniffingvixen . . .

The following simply checks that all controls of interest have data (are not empty). If any controls is empty saving the record is cancelled and focus is set to that control. Note:
[ol][li]All controls of interest are checked relinquishing the need to [blue]check in order/sequence[/blue].[/li]
[li]Because of the above there's [blue]no need to hide controls![/blue].[/li]
[li]Because all controls are visible the [blue]user can easily correct any typo's[/blue].[/li][/ol]

OK here we go . . .
[ol][li][purple]Make a copy of the form under another name so you can come back to square one.[/purple][/li]
[li]Open the form in design view.[/li]
[li]Delete existing code involved in this scenario.[/li]
[li]For the controls of interest set the [blue]Visible[/blue] property to [purple]Yes[/purple].[/li]
[li]In the [blue]Tag[/blue] property of the controls of interest
enter a question mark [purple]?[/purple][/li]
[li]Finally in the [blue]BeforeUpdate[/blue] event of the form, copy/paste the following:

[blue]   Dim ctl As Control, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            Msg = "'" & ctl.Name & "' has no data!" & DL & _
                  "Saving this record is cancelled!" & DL & _
                  "Check the data and try again . . ."
            Style = vbInformation + vbOKOnly
            Title = "Required Data Missing! . . ."
            MsgBox Msg, Style, Title
            ctl.SetFocus
            Cancel = True
            Exit For
         End If
      End If
   Next[/blue]

[/li][/ol]

Now . . . and more importantly . . . if your gonna understand what your doing and to better know and communicate about your DB, grab your favorite beverage and plan to spend some time with the following links (in order). Before your done you may find yourself making changes you never would've thought of:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

BTW: [blue]Welcome to Tek-Tips![/blue] To get a grip on [blue]Tek-Tips Etiquette[/blue] and whats expected of you be sure to have a look at FAQ219-2884

See Ya! . . . . . .

 

[sniffingvixen]

AceMan,

Thanks for replying and for the links. I certainly do want to understand as much as possible. Sorry for all my Tek-Tips faux pas, especially if my reply to your initial recommendation was offensive. Upon re-reading it, I realize that what seemed succinct but playful in my head came off as rude, flippant, and impatient. That certainly wasn't my intent. Sorry!

Thanks also for the code, which seems to work mostly as intended, but is giving me some trouble...

1) I don't want any of the fields to be required, just some to be off-limits until others have been completed. Do I understand correctly that the code requires data entry in all fields of interest?

2) When I enter data into the first field, the sub's error message appears and references the second field.

3) When I click "OK" I get the following error message:

Run-time error '2108'

You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.

4) If I delete the data in the first field, the sub's error message references it.

I double checked all my steps, but couldn't see the error. Any ideas where I might've gone wrong?

Thanks again for your help,
j

PS-If you're bored and feel like helping me in another area, take a look at thread700-1314103, which I posted regarding the percent format.