Please help with pop up message when entering wrong date period 3

[tnguyen315]

Hello all, please help. I'm very appreciated.
On the form, I have 1 combo box with values list "Annual, Semi-Annual, Quarter" for users to select only in this list.
And 2 text boxes named "txtBeginDate" and "txtEndDate" to users to enter the date period. I would like if users select "Annual" in the combox box above, then enter date in "txtBeginDate" = "03/01/YYYY" and "txtEndDate" = "02/28/YYYY" (or either "02/29/YYYY" if available). I would like in the "lost focus" control or "after update" ...when users enter wrong date period, then message pop up and saying something like "Wrong date period, please re-enter!". Samething with Semi-Annual and Quarter" also.

The period cover:
Annual : "03/01/YYYY" - "02/28/YYYY" (or 02/29/YYYY)
Semi-Annual: "03/01/YYYY" - "08/31/YYYY"
"09/01/YYYY" - "02/28/YYYY" (or 02/29/YYYY)
Quarter: "03/01/YYYY" - "05/31/YYYY"
"06/01/YYYY" - "08/31/YYYY"
"09/01/YYYY" - "11/30/YYYY"
"12/01/YYYY" - "02/28/YYYY" (or 02/29/YYYY"


Right now, I can write something in the lost focus control to work:

Below is my code:

Private Sub txtBeginDate_LostFocus()
Dim strGetDate As String
strGetDate = Format$(Me.txtBeginDate, "MM/DD")
If me.cboPeriod = "Annual" then
If strGetDate <> "03/01" then
Msgbox "Wrong date period, please re-enter!"
me.txtBeginDate.SetFocus
Exit Sub
End If
ElseIf me.cboPeriod = "Semi-Annual" then
If (strGetDate <> "03/01") or (strGetDate <> "09/01")then
Msgbox "Wrong date period, please re-enter!"
me.txtBeginDate.SetFocus
Exit Sub
End If
ElseIf me.cboPeriod = "Quarter" then
If (strGetDate <> "03/01") or (strGetDate <> "06/01") or (strGetDate <> "09/01") or (strGetDate <> "12/01")
Msgbox "Wrong date period, please re-enter!"
me.txtBeginDate.SetFocus
Exit Sub
End If
End If
End Sub

I do the samething for "txtEndDate" but I have the problem is when the year has Feb 29, then it will also work Feb 28. So please help on this part, if the year has Feb 29, then when users enter "02/28/YYYY" it'll also need to pop up a message and saying wrong date. Please help. Thank you very much.

 

[MazeWorX]

Why not prompt for the year and hard code your to and from dates based on user selection. You could change the feb 29 based on the year selected. This way you dont have to burden your users with getting the dates correct for what seems to me; a 'constant' and should be selected for them since they have already indicated what type they want 'annual' 'semi' etc.

HTH M

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[tnguyen315]

Thanks MazeWorX for your respond...
I don't know how to hard code on that...Do you have any sample?
Thanks alots.

 

[MazeWorX]

not on hand but it will give me something to play with over the weekend

M

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[GingerR]

what are you doing with these dates? Generating a report or a view of data on a form?

You could make two hidden text boxes on the form, i.e. txtStartDate and txtEndDate. You will fill these in with code based on what the user selects, then reference them in your report/form filter.

Make radio buttons (so only one is selectable):

Annual
Semi-Annual
Quarter

If applicable, next to Annual, put a combo box for the year if applicable (2008, 2009 for example)

For Semi-Annual, put a combo box or other way for them to select MARCH or SEPT.

For QUARTER, put a combo box or other method for them to select MAR, JUN, SEPT, DEC or whatever.

Make each of these additional controls visible only when the user picks Annual, Semi-Annual or Quarterly.

THEN, make a button that says "VIEW" or something on it. When they click the button, the first thing the code behind the button does is evaluate their choices.

'First validate that they have chosen something appropriate
Select Case Me.fraReportChoice
    Case 1 'Annual
           'Ensure that a year is chosen; if not, prompt user to pick a year. If ok, fill in txtStartDate and txtEndDate with something appropriate based on the year chosen.
    Case 2 'Semi Annual
           'Ensure that a semi-annual choice is chosen; if not, prompt user to pick something and end. If ok, fill in the txtStartDate and txtEndDate with proper data.
    Case 3 'Quarterly
           'same as above

End Select

then in your report/form, you can filter on the the txtStartDate and txtEndDate on this form.

This is just a sample idea. You will surely have to tweak it for your specific needs.




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[tnguyen315]

This is for data entry to elimiate the wrong date period based on the combo box. If the user do data entry select "Annual" in the combo box, then the date period for them must be "03/01/YYYY" and "02/28/YYYY" ( or 02/29/YYYY"). No other dates would allow to enter.
Hope it makes sense.

 

[MazeWorX]

Data Entry" >> to be stored in the db for recall when ever... or to be used for the intended purpose of your form?

M

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[MazeWorX]

Ok here you go. The following code assumes several things
A table with the constants

TypeID > Auto number >pk
Description > Text
StartDate > Text
EndDate > Text

Description would be Annual - 1st half, annual 2nd half etc.
StartDate 03/01, 06/01, etc matching the description
EndDate the same idea

A form with a combobox(cboType) populated from the table by query

column count = 4
Bound column 1
column widths = 0;.5;0;0

A command button (cmdDoSomething) which of course the code is attahced to with the onClick event

A text box unbound (txtYear) for user input. Could be changes to a combo and populated frfom a table

'MaZeWorx 2009
Private Sub cmdDoSomething_Click()

Dim EndDate As String
Dim StartDate As String
Dim IsLeapYear As String
Dim mYear As String
Dim mType As Integer
Dim nYear As String

mYear = Me.txtYear
mType = Me.cboType.Value

If IsNull(Me.txtYear) Then
MsgBox "Please enter a Year!"
Else

'check if the end date is at the end of the year
If mType = 1 Or mType = 3 Or mType = 7 Then

IsLeapYear = (Day(DateSerial(mYear, 2, 28) + 1) = 29)
'check to see if the year passed is a leap year

If IsLeapYear = True Then 'if leap year returns true then change last day in feb
EndDate = "2/29"
Else
EndDate = "3/28"
End If

'increment the year by 1 to add to the end date and build the date strings
nYear = mYear + 1
StartDate = Me.cboType.Column(2) & "/" & mYear
EndDate = EndDate & "/" & nYear

Else 'take end date from the table - leap year doesnt apply
StartDate = Me.cboType.Column(2) & "/" & mYear
EndDate = Me.cboType.Column(3) & "/" & mYear
End If


MsgBox StartDate & " " & EndDate
'from here you would need to format the result as it is currently a string(text)
'if you want to compare it to a date in the db or write it to a table
End If
End Sub

HTH Have a terrific Weekend!
I'm Out

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[MazeWorX]

Opps forgot attachment

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[TheAceMan1]

How are ya tnguyen315 . . .

Consider that [blue]txtBeginDate[/blue] and [blue]txtEndDate[/blue] are uneditable (thru properties), because code is going to insert proper dates!

Now ... because the dates are dependent on the year, you'll have an [blue]unbound textbox[/blue] for this (lets call it [blue]usrYear[/blue]). Along with the combobox which shows:

[blue]Annual
Semi Annual 1st Half
Semi Annual 2nd Half
Qtr 1st Qtr
Qtr 2nd Qtr
Qtr 3rd Qtr
Qtr 4th Qtr[/blue]

Including a function to return the proper date for the end of Feb, Would it not be easy to just plug in the dates in [blue]txtBeginDate[/blue] and [blue]txtEndDate[/blue] thru code?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Found an error

Change
If IsLeapYear = True Then 'if leap year returns true then change last day in feb
EndDate = "2/29"
Else
EndDate = "3/28"
End If

to >> EndDate = "2/28"

M

Remember amateurs built the ark - professionals built the Titanic

[flush]