Hiding/Unhiding fields in a form using a pick list

[Jerry911]

I have a form that uses a pick list to select a specified date/time range based on the case value. Case 1-7 have pre-defined date/time functions, but case 8 is for a custom date/time range. I only want to show the custom fields when the custom date range option is selected. When the form loads I have all the fields hidden.

Private Sub Form_Load()
Me!CustomDateBox.Visible = False
Me!dtpStartDate.Value = Date
Me!dtpEndDate.Value = Date
Me!dtpStartDate.Visible = False
Me!cstStartTime.Visible = False
Me!dtpEndDate.Visible = False
Me!cstEndTime.Visible = False
Me!lblCstDateRange.Visible = False
Me!lblFrom.Visible = False
Me!lblTo.Visible = False
 
End Sub
 

When case "8" is selected the same fields are set to true and the fields are visible and all works well. What I want to try and accomplish is a cleaner way in my code to hide these fields whenever one of the other options is reselected.

I have placed the above code in each case statement and that works, but it seams to me that I should be able to simly make an If-Then-Else type statement so that I only have to enter the code 1 time.

I have tried several variations of the following but I get syntax errors or compile errors.

Any thoughts?

DateSelectorValue = Me!DateSelector
 
Select Case DateSelectorValue
 
If DateSelectorValue Is = 8 Then
  Me!CustomDateBox.Visible = True
  Me!dtpStartDate.Visible = True
  Me!cstStartTime.Visible = True
  Me!dtpEndDate.Visible = True
  Me!cstEndTime.Visible = True
  Me!lblCstDateRange.Visible = True
  Me!lblFrom.Visible = True
  Me!lblTo.Visible = True
Else
  Me!CustomDateBox.Visible = False
  Me!dtpStartDate.Visible = False
  Me!cstStartTime.Visible = False
  Me!dtpEndDate.Visible = False
  Me!cstEndTime.Visible = False
  Me!lblCstDateRange.Visible = False
  Me!lblFrom.Visible = False
  Me!lblTo.Visible = False
End If
 
 
Case "1" 'Today
  Me!START = DateString(Now()) & "000000"
  Me!END = DateString(Now()) & "235959"
  Me!END2 = DateString(Date + 1) & "013000"
 
Case "2" 'Yesterday
  Me!START = DateString(Date - 1) & "000000"
  Me!END = DateString(Date - 1) & "235959"
  Me!END2 = DateString(Date) & "013000"
 
Case "3" 'Week-To-Date
  Me!START = DateString(Date - Weekday(Date) + 1) & "000000"
  Me!END = DateString(Date) & "235959"
  Me!END2 = DateString(Date + 1) & "013000"
 
Case "4" 'Last Week
  Me!START = DateString(Date - Weekday(Date) - 6) & "000000"
  Me!END = DateString(Date - Weekday(Date) - 7 + 7) & "235959"
  Me!END2 = DateString(Date - Weekday(Date) - 7 + 8) & "013000"
 
Case "5" 'Month-To-Date
  Me!START = DateString(DateSerial(YEAR(Date), MONTH(Date), 1)) & "000000"
  Me!END = DateString(Date) & "235959"
  Me!END2 = DateString(Date + 1) & "013000"
 
Case "6" 'Last Month
  Me!START = DateString(DateSerial(YEAR(Date), MONTH(Date) - 1, 1)) & "000000"
  Me!END = DateString(DateSerial(YEAR(Date), MONTH(Date), 0)) & "235959"
  Me!END2 = DateString(DateSerial(YEAR(Date), MONTH(Date), 1)) & "013000"
 
Case "7" 'Last 20 Weeks
  Me!START = DateString(([L20W]) - Weekday([L20W]) + 1) & "000000"
  Me!END = DateString(Date - Weekday(Date) - 7 + 7) & "235959"
  Me!END2 = DateString(Date - Weekday(Date) - 7 + 8) & "013000"
 
Case "8" 'Custom
  Me!START = DateString([dtpStartDate]) & TimeString([cstStartTime])
  Me!END = DateString([dtpEndDate]) & TimeString([cstEndTime])
  Me!END2 = GetDateString([cEND2])
 
End Select
 
End Sub
 

[Stewart Ross]

Hi Jerry911. Your IF test has a syntax error in it; it should just be IF ... = 8, not Is = 8. You are also enclosing this within the scope of the SELECT CASE statement, which is incorrect and is another syntax error.

With the addition of one boolean variable you can do away with the IF..THEN..ELSE, as shown below. I have assumed that the dateselectorvalue is a string, as that is how you are referring to it in your Case statements.

Dim MakeVisible as Boolean
 
DateSelectorValue = Me!DateSelector
 
MakeVisible = DateSelector = "8"
 
Me!CustomDateBox.Visible = MakeVisible
Me!dtpStartDate.Visible = MakeVisible
Me!cstStartTime.Visible = MakeVisible
Me!dtpEndDate.Visible = MakeVisible
Me!cstEndTime.Visible = MakeVisible
Me!lblCstDateRange.Visible = MakeVisible
Me!lblFrom.Visible = MakeVisible
Me!lblTo.Visible = MakeVisible
 
Select Case DateSelectorValue
 
Case "1" 'Today
  Me!START = DateString(Now()) & "000000"
  Me!END = DateString(Now()) & "235959"
  Me!END2 = DateString(Date + 1) & "013000"
'... and all other cases as before

-Stewart

[Jerry911]

Thanks for the response!

I just had to make one adjustment to your code. You had: MakeVisible = DateSelector = "8" and it needed to be: MakeVisible = DateSelectorValue = "8" . Worked like a champ after that. Only took me an hour of fiddling with it...

Thanks again!

[Stewart Ross]

Hi Jerry. Glad you got it working after my accidental shortening of the variable name...

If you include the statement

Option Explicit

at the top of your code the compiler itself will throw an error if a variable name does not match a DIM statement, or if there is no DIM in the first place. This would have caught my incorrect version of your variable without you having to spend an hour tring to work out what was wrong...

Option explicit is usually included by default as the very top line in a code module.

Cheers

Stewart