pop message for subform

[MAINOH]

Hi,

I have a form frmTIMESHEET with a subform subfrmTIMESHEET.

On frmTIMESHEET there are fields POPBEG and POPEND (both date fields).

On subfrmTIMESHEET there is a field DAY (date field).

What I would like is after DAY is updated, for access to check to see if the value of DAY false between the values of POPBEG and POPEND. If it does not I want a pop up message to say "The entered date does not fall within this invoice POP! Please enter a new date."

Could anyone help me code this? I'm thinking I would put code on the after update event of the DAY field in the subform.

Thank you so very much for your help!

 

[PHV]

In the BeforeUpdate event procedure of the DAY control:
If Not IsDate(Me!DAY) Or Me!DAY < Me.Parent.Form!POPBEG Or Me!DAY > Me.Parent.Form!POPEND Then
MsgBox "The entered date does not fall within this invoice POP! Please enter a new date."
Cancel = True
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MAINOH]

Thanks PHV, you have gotten me much closer. However, the message pops up no matter what value I enter in DAY.

How can the code be changed to cause the message ONLY IF the value of DAY is not between or equal to POPBEG and POPEND?

Thanks!

 

[ThomasLafferty]

Hi there!
You want to use a public function called from the [!]BeforeUpdate[/!] event of your control so that the user can't actually enter inappropriate values.

Maybe something like this:

[COLOR=blue]Sub[/color] DAY_BeforeUpdate()
     CheckInvDate
     [COLOR=blue]If[/color] Cancel = [COLOR=blue]True Then[/color] DoCmd.CancelEvent
[COLOR=blue]End Sub[/color]

[COLOR=blue]Public Function[/color] CheckInvDate()
[COLOR=green]'*****VARIABLE DECLARATIONS*****[/color]
[COLOR=blue]Dim[/color] BDate [COLOR=blue]As[/color] Date, EDate [COLOR=blue]As[/color] Date, IDate [COLOR=blue]As[/color] Date, Cancel [COLOR=blue]As[/color] Boolean
     BDate = Forms!frmTimeSheet!POPBEG.Value
     EDate = Forms!frmTimeSheet!POPEND.Value
     IDate = Forms!subfrmTimeSheet!DAY.Value
     
[COLOR=green]'Test to see if date is appropriate[/color]
     [COLOR=blue]If[/color] IDate >= BDate [COLOR=blue]And[/color] IDate <= EDate [COLOR=blue]Then[/color]
          [COLOR=green]'Allow the date[/color]
     [COLOR=blue]Else[/color] [COLOR=green]'Disallow the date[/color]
         Msgbox "The entered date does not fall within this invoice POP!  Please enter a new date.",vbExclamation,"Invalid Date"
          Cancel = [COLOR=blue]True[/color]
     [COLOR=blue]End If

End Function[/color]

This is liable to need some tweaking since I wrote it directly online and haven't tested it.

Hope this gets you started in the right direction.

Tom

Born once die twice; born twice die once.

 

[ThomasLafferty]

I think I like PHV's method better - it's simpler and liable to work.
Try switching the comparison:
Me!DAY < Me.Parent.Form!POP[!]END[/!] Or Me!DAY > Me.Parent.Form!POP[!]BEG[/!]

Hope this helps.

How the heck are ya, PHV?

Tom

Born once die twice; born twice die once.

 

[MAINOH]

Tom - I tried to complete your first post - but being somewhat inexperienced at coding - I couldn't figure out how to create the Public Function (or where to create it).

So I tried your change to the comparison, here is what I have:

Private Sub DAY_BeforeUpdate(Cancel As Integer)
If Not IsDate(Me!DAY) Or Me!DAY < Me.Parent.Form!POPEND Or Me!DAY > Me.Parent.Form!POPBEG Then
MsgBox "The entered date does not fall within this invoice POP! Please enter a new date."
Cancel = True
End If

Now the message does not come up no matter what date I enter.

Thanks for your continued help.

 

[missinglinq]

As an aside, MAINOH, I see a couple of things you might want to think about. Access often has a problem when multiple objects have identical name, i.e. frmTimeSheet and subfrmTimeSheet! You also want to be careful not to name an object the same as a reserved word or words used as for functions or macros. While DAY isn't a reserved word, it is the name of an inherent function and also likely to confuse Access. Better to use a derivative, such as DDAY.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[ThomasLafferty]

Ever had one of those days?
Try this:
Me!DAY [!]>[/!] Me.Parent.Form!POPEND Or Me!DAY [!]<[/!] Me.Parent.Form!POPBEG

Need to read a little more closely...

Tom


Born once die twice; born twice die once.