467,188 Members | 1,435 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,188 developers. It's quick & easy.

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
  1. Private Sub Form_Load()
  2. Me!CustomDateBox.Visible = False
  3. Me!dtpStartDate.Value = Date
  4. Me!dtpEndDate.Value = Date
  5. Me!dtpStartDate.Visible = False
  6. Me!cstStartTime.Visible = False
  7. Me!dtpEndDate.Visible = False
  8. Me!cstEndTime.Visible = False
  9. Me!lblCstDateRange.Visible = False
  10. Me!lblFrom.Visible = False
  11. Me!lblTo.Visible = False
  12.  
  13. End Sub
  14.  
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
  1. DateSelectorValue = Me!DateSelector
  2.  
  3. Select Case DateSelectorValue
  4.  
  5. If DateSelectorValue Is = 8 Then
  6.   Me!CustomDateBox.Visible = True
  7.   Me!dtpStartDate.Visible = True
  8.   Me!cstStartTime.Visible = True
  9.   Me!dtpEndDate.Visible = True
  10.   Me!cstEndTime.Visible = True
  11.   Me!lblCstDateRange.Visible = True
  12.   Me!lblFrom.Visible = True
  13.   Me!lblTo.Visible = True
  14. Else
  15.   Me!CustomDateBox.Visible = False
  16.   Me!dtpStartDate.Visible = False
  17.   Me!cstStartTime.Visible = False
  18.   Me!dtpEndDate.Visible = False
  19.   Me!cstEndTime.Visible = False
  20.   Me!lblCstDateRange.Visible = False
  21.   Me!lblFrom.Visible = False
  22.   Me!lblTo.Visible = False
  23. End If
  24.  
  25.  
  26. Case "1" 'Today
  27.   Me!START = DateString(Now()) & "000000"
  28.   Me!END = DateString(Now()) & "235959"
  29.   Me!END2 = DateString(Date + 1) & "013000"
  30.  
  31. Case "2" 'Yesterday
  32.   Me!START = DateString(Date - 1) & "000000"
  33.   Me!END = DateString(Date - 1) & "235959"
  34.   Me!END2 = DateString(Date) & "013000"
  35.  
  36. Case "3" 'Week-To-Date
  37.   Me!START = DateString(Date - Weekday(Date) + 1) & "000000"
  38.   Me!END = DateString(Date) & "235959"
  39.   Me!END2 = DateString(Date + 1) & "013000"
  40.  
  41. Case "4" 'Last Week
  42.   Me!START = DateString(Date - Weekday(Date) - 6) & "000000"
  43.   Me!END = DateString(Date - Weekday(Date) - 7 + 7) & "235959"
  44.   Me!END2 = DateString(Date - Weekday(Date) - 7 + 8) & "013000"
  45.  
  46. Case "5" 'Month-To-Date
  47.   Me!START = DateString(DateSerial(YEAR(Date), MONTH(Date), 1)) & "000000"
  48.   Me!END = DateString(Date) & "235959"
  49.   Me!END2 = DateString(Date + 1) & "013000"
  50.  
  51. Case "6" 'Last Month
  52.   Me!START = DateString(DateSerial(YEAR(Date), MONTH(Date) - 1, 1)) & "000000"
  53.   Me!END = DateString(DateSerial(YEAR(Date), MONTH(Date), 0)) & "235959"
  54.   Me!END2 = DateString(DateSerial(YEAR(Date), MONTH(Date), 1)) & "013000"
  55.  
  56. Case "7" 'Last 20 Weeks
  57.   Me!START = DateString(([L20W]) - Weekday([L20W]) + 1) & "000000"
  58.   Me!END = DateString(Date - Weekday(Date) - 7 + 7) & "235959"
  59.   Me!END2 = DateString(Date - Weekday(Date) - 7 + 8) & "013000"
  60.  
  61. Case "8" 'Custom
  62.   Me!START = DateString([dtpStartDate]) & TimeString([cstStartTime])
  63.   Me!END = DateString([dtpEndDate]) & TimeString([cstEndTime])
  64.   Me!END2 = GetDateString([cEND2])
  65.  
  66. End Select
  67.  
  68. End Sub
  69.  
Mar 14 '08 #1
  • viewed: 2118
Share:
3 Replies
Expert Mod 2GB
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
  1. Dim MakeVisible as Boolean
  2.  
  3. DateSelectorValue = Me!DateSelector
  4.  
  5. MakeVisible = DateSelector = "8"
  6.  
  7. Me!CustomDateBox.Visible = MakeVisible
  8. Me!dtpStartDate.Visible = MakeVisible
  9. Me!cstStartTime.Visible = MakeVisible
  10. Me!dtpEndDate.Visible = MakeVisible
  11. Me!cstEndTime.Visible = MakeVisible
  12. Me!lblCstDateRange.Visible = MakeVisible
  13. Me!lblFrom.Visible = MakeVisible
  14. Me!lblTo.Visible = MakeVisible
  15.  
  16. Select Case DateSelectorValue
  17.  
  18. Case "1" 'Today
  19.   Me!START = DateString(Now()) & "000000"
  20.   Me!END = DateString(Now()) & "235959"
  21.   Me!END2 = DateString(Date + 1) & "013000"
  22. '... and all other cases as before
-Stewart
Mar 15 '08 #2
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!
Mar 17 '08 #3
Expert Mod 2GB
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
  1. 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
Mar 17 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by StressPuppy | last post: by
12 posts views Thread by Alex Hunsley | last post: by
22 posts views Thread by Mr Newbie | last post: by
1 post views Thread by jack | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.