Hide and show texboxes based on Dropdown selection

[Robb58]

I've been tasked to add some new fields to an existing form in an old Access database for a design request Form and I've hit a bit of a snag!
Basically I've added a checkbox to indicate whether or not the customer has supplied their own media to include in an artwork design process. When the checkbox is clicked it reveals two dropdown boxes named "Supplied_Media_Dropdown" and "Media_Delivered_Method".
In the "Media_Delivered_Method" there are 4 options "DVD", "Email", "Hard Copy" and "Multiple Sources" and, depending on which option is selected, either or both of two different text boxes are displayed - "DVD_Serial" or "Email_Address2" are displayed.
Before a selection is made no text boxes are visible and the following states are triggered by the following selections:

• When "DVD" is selected the "DVD_Serial" textbox is shown and the "Email_Address2" textbox is hidden.
• When "Email" is selected the "Email_Address2" is shown and "DVD_Serial" is hidden.
• When "Multiple Sources" is selected both textboxes are revealed
• When "Hard Copy" is selected both textboxes are hidden

The first part of the process (clicking the checkbox and displaying the two dropdowns) works perfectly and when I navigate through the records the dropdowns show or hide correctly on each record based on the checkbox status. However, the second part of the process is causing problems. Although the dropdown selection works perfectly on the current record that is being worked on, when I navigate through the records the textboxes don't display correctly and show the textbox correlating to the last updated record regardless of what is displayed (correctly) in the "Media_Delivered_Method".
I'm sure I'm missing something obvious so I've supplied the code I have at the moment and would greatly appreciate any (polite)suggestions on how to fix the textbox display issues

Private Sub Form_Current()
   
        Me.Supplied_Media_Dropdown.Visible = Me.Supplied_media_Checkbox
        Me.Media_Delivered_Method.Visible = Me.Supplied_media_Checkbox
        
  
End Sub




Private Sub Supplied_media_Checkbox_AfterUpdate()
    If Me.Supplied_media_Checkbox.Value = True Then
        Me.Supplied_Media_Dropdown.Visible = True
    Me.Media_Delivered_Method.Visible = True
    Else
        Me.Supplied_Media_Dropdown.Visible = False
    Me.Media_Delivered_Method.Visible = False
End If

End Sub

Private Sub Media_Delivered_Method_AfterUpdate()
If Me.Media_Delivered_Method = "DVD" Then
        Me.DVD_Serial.Visible = True
        Me.Email_Address2.Visible = False

    ElseIf Me.Media_Delivered_Method = "Email" Then
        Me.Email_Address2.Visible = True
        Me.DVD_Serial.Visible = False

    ElseIf Me.Media_Delivered_Method = "Multiple Sources" Then
        Me.DVD_Serial.Visible = True
        Me.Email_Address2.Visible = True
Else
        Me.Email_Address2.Visible = False
        Me.DVD_Serial.Visible = False
   
End If
End Sub

 

[isladogs]

Try adding Me.Requery as the first line in the Form_Current event.
If you do that, I'm not sure if you actually need the 2 lines you have in that event now.

Was that polite enough?

 

[Robb58]

Hi isladogs,

thanks for the quick and polite response

I added the the Me.Requery code and removed the other code in the Form_current as suggested, but it seems to cause a loop or something so, when I click onto the next record, the form flickers randomly and returns to the record I'm trying to navigate away from.... very curious

 

[isladogs]

Hmm. That's no good....
Can you upload the relevant part of your app so someone can look at it.

 

[Robb58]

Hi Colin,
Unfortunately, the database is on a secure server so I can't copy any part of the Db without incurring the wrath of the powers that be

 

[Robb58]

Quick update...

Hi Colin,

I seem to have solved the problem by copying the If statement from the Media_Delivered_Method_AfterUpdate() private sub and duplicating it into the Form_Current() private sub.

It seems to be working but, in your valued opinion, is this likely to cause any problems further down the line?

 

[Gasman]

and returns to the record I'm trying to navigate away from.... very curious

Is that because it happens to be the first record in the form/query?

 

[Robb58]

Not entirely sure Gasman but adding the "If" code above to the Form_Current seems to have solved the problem without causing any conflicts so far

 

[isladogs]

Quick update...

Hi Colin,

I seem to have solved the problem by copying the If statement from the Media_Delivered_Method_AfterUpdate() private sub and duplicating it into the Form_Current() private sub.

It seems to be working but, in your valued opinion, is this likely to cause any problems further down the line?

Duplicating code in form current as well as an after update event can be problematic. Try temporarily disabling it in the after update event. Does it still work correctly with form current alone?

As Gasman suggested, the requery I unhelpfully suggested will have tried returning you to the first record. Not sure why it was looping/flickering however

 

[The_Doc_Man]

The "flicker" will depend on what else is going on at the time including the possibility of an ongoing and cycle Timer event running in the background. Normally that flicker isn't visible because what happens is finished before the screen has a chance to repaint. But if a timer is running, then remember that events cannot interrupt each other, so they serialize. In that case, the event that would not flicker normally is suddenly slowed down enough by an intervening timer event that the screen painter has a chance to be seen actually working.