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.

Expand|Select|Wrap|Line Numbers

 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?

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

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

Hiding/Unhiding fields in a form using a pick list

Post your reply

Similar topics