dlookup compare date values

  • Thread starter Thread starter niuginikiwi
  • Start date Start date
N

niuginikiwi

I have a query called qryWHP which has a caluclated date field which I would
like to compare with today's date and throw a vbYesNo popup depeding on the
condition. If Date() is less than WHPDate then fire vbYesNo and then follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID] = " &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
 
Hi niuginikiwi

By formatting your dates before you compare them, you are actually comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use cboPlantingDetailsID.Undo. It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID
 
Hi Graham,

Thank you so much for the reply with the corrections and suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am trying to
do.

I have a list of crop plantings which get sprayed with different products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date (ApplicationDate) with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to actaully
check serveral of the WHPDates on the go to make sure they are not more than
today's date.

So basically when I click on cboPlantingDetailsID to select a planting, the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some WHPDate values
for that selected planting which are higher than todays date, but the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP", "[qryWHP.PlantingDetailsID] = "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



Graham Mandeno said:
Hi niuginikiwi

By formatting your dates before you compare them, you are actually comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use cboPlantingDetailsID.Undo. It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
I have a query called qryWHP which has a caluclated date field which I
would
like to compare with today's date and throw a vbYesNo popup depeding on
the
condition. If Date() is less than WHPDate then fire vbYesNo and then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID] = " &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
 
Hi niuginikiwi
For one planting, serveral spray products are used so I have to actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.
Click to expand...

No you don't - you just need to check the latest (maximum) one.
So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.
Click to expand...

Just replace your DLookup by DMax and it should work for you.

A more efficient solution would be to open a recordset with only the single
value you require:

Dim rs as DAO.Recordset
Dim dtWHP as Date
Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from ApplicationsTable where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close
If Date < dtWHP Then
...
End If


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thank you so much for the reply with the corrections and suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am trying
to
do.

I have a list of crop plantings which get sprayed with different products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date (ApplicationDate)
with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some WHPDate
values
for that selected planting which are higher than todays date, but the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP", "[qryWHP.PlantingDetailsID] = "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



Graham Mandeno said:
Hi niuginikiwi

By formatting your dates before you compare them, you are actually
comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not
what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date
data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You
cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use cboPlantingDetailsID.Undo.
It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
I have a query called qryWHP which has a caluclated date field which I
would
like to compare with today's date and throw a vbYesNo popup depeding on
the
condition. If Date() is less than WHPDate then fire vbYesNo and then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID] =
" &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

Thankyou for the help. That really did sort me out and it appears to be
working now the way I wanted.

One more thing is that I find is that some crop plantings do not get
sprayed so in that case I will need to check first whethere there is an entry
in tblApplications for PlantingdetailsID and if there is then the above code
should fire bit if not then it should ignore the above code and carry on to
save the data.
tblPlantingDetails---<tblApplications have a one to many relationship linked
by plantingdetailsID field as FK.

Here is what I'd like to do:
The code in there is just pyseudo code, just to give you an idea.

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
'Check to see if there is any spray applications for the crop planting
'Pyseudoco

If cboPlantingDetailsID.column(0) In tblApplications.PlantingdetailsID then
******

' Check to see if this planting is still inside With Holding Period

Dim strMsg As String
Dim iResponse As Integer
Dim rs As DAO.Recordset
Dim dtWHP As Date

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from qryWHP where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close

If Date < dtWHP Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Continue or No to Back Out."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If

Else

*** carry on as normal

End Sub


--
niuginikiwi
Nelson, New Zealand


Graham Mandeno said:
Hi niuginikiwi
For one planting, serveral spray products are used so I have to actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.
Click to expand...

No you don't - you just need to check the latest (maximum) one.
So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.
Click to expand...

Just replace your DLookup by DMax and it should work for you.

A more efficient solution would be to open a recordset with only the single
value you require:

Dim rs as DAO.Recordset
Dim dtWHP as Date
Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from ApplicationsTable where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close
If Date < dtWHP Then
...
End If


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thank you so much for the reply with the corrections and suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am trying
to
do.

I have a list of crop plantings which get sprayed with different products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date (ApplicationDate)
with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some WHPDate
values
for that selected planting which are higher than todays date, but the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP", "[qryWHP.PlantingDetailsID] = "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



Graham Mandeno said:
Hi niuginikiwi

By formatting your dates before you compare them, you are actually
comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not
what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date
data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You
cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use cboPlantingDetailsID.Undo.
It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a query called qryWHP which has a caluclated date field which I
would
like to compare with today's date and throw a vbYesNo popup depeding on
the
condition. If Date() is less than WHPDate then fire vbYesNo and then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID] =
" &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi NGK

The code should be working that way, I think. If there are no sprayings (no
records in tblApplications) then rs.RecordCount should be zero, so dtWHPDate
should be zero (initialised as declared); therefore, as Date() is NOT be
less than zero, the If...Then block will not be executed.

Hang on... I just saw you are using qryWHP in the SELECT statement instead
of tblApplications. I have no idea what qryWHP does, but I think you should
cut out the middle man and go straight to the table.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thankyou for the help. That really did sort me out and it appears to be
working now the way I wanted.

One more thing is that I find is that some crop plantings do not get
sprayed so in that case I will need to check first whethere there is an
entry
in tblApplications for PlantingdetailsID and if there is then the above
code
should fire bit if not then it should ignore the above code and carry on
to
save the data.
tblPlantingDetails---<tblApplications have a one to many relationship
linked
by plantingdetailsID field as FK.

Here is what I'd like to do:
The code in there is just pyseudo code, just to give you an idea.

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
'Check to see if there is any spray applications for the crop planting
'Pyseudoco

If cboPlantingDetailsID.column(0) In tblApplications.PlantingdetailsID
then
******

' Check to see if this planting is still inside With Holding Period

Dim strMsg As String
Dim iResponse As Integer
Dim rs As DAO.Recordset
Dim dtWHP As Date

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from qryWHP where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close

If Date < dtWHP Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Continue or No to Back Out."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If

Else

*** carry on as normal

End Sub


--
niuginikiwi
Nelson, New Zealand


Graham Mandeno said:
Hi niuginikiwi
For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.
Click to expand...

No you don't - you just need to check the latest (maximum) one.
So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.
Click to expand...

Just replace your DLookup by DMax and it should work for you.

A more efficient solution would be to open a recordset with only the
single
value you require:

Dim rs as DAO.Recordset
Dim dtWHP as Date
Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from ApplicationsTable where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close
If Date < dtWHP Then
...
End If


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thank you so much for the reply with the corrections and suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am
trying
to
do.

I have a list of crop plantings which get sprayed with different
products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date
(ApplicationDate)
with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some WHPDate
values
for that selected planting which are higher than todays date, but the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP", "[qryWHP.PlantingDetailsID]
= "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



:

Hi niuginikiwi

By formatting your dates before you compare them, you are actually
comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not
what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date
data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You
cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use
cboPlantingDetailsID.Undo.
It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you
do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a query called qryWHP which has a caluclated date field which
I
would
like to compare with today's date and throw a vbYesNo popup depeding
on
the
condition. If Date() is less than WHPDate then fire vbYesNo and then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID]
=
" &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?"
&
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

Thankyou for writing again, yes it would have been better to have it going
straight to tblApplications. However, the reason why I changed to qryWHP is
that ApplicationDate and WHP fields are in two different tables.
ApplicationDate is in tblApplications and WHP is in tblProductDetails. Here
is what the relationship between tblApplications and tblProductDetails.

tblApplications ---<tblApplicationDetails>---tblProductDetails.

Maybe it might pay put the SQL behind qryWHP here to go straight to the
tables. Here is what the SQL of qryWHP looks like:

SELECT tblApplications.ApplicationDate, tblProductDetails.WHP
FROM tblProductDetails INNER JOIN (tblApplications INNER JOIN
tblApplicationDetails ON tblApplications.ApplicationID =
tblApplicationDetails.ApplicationID) ON tblProductDetails.ProductDetailsID =
tblApplicationDetails.ProductDetailsID
GROUP BY tblApplications.ApplicationDate, tblProductDetails.WHP;

Any suggetions?

Graham Mandeno said:
Hi NGK

The code should be working that way, I think. If there are no sprayings (no
records in tblApplications) then rs.RecordCount should be zero, so dtWHPDate
should be zero (initialised as declared); therefore, as Date() is NOT be
less than zero, the If...Then block will not be executed.

Hang on... I just saw you are using qryWHP in the SELECT statement instead
of tblApplications. I have no idea what qryWHP does, but I think you should
cut out the middle man and go straight to the table.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thankyou for the help. That really did sort me out and it appears to be
working now the way I wanted.

One more thing is that I find is that some crop plantings do not get
sprayed so in that case I will need to check first whethere there is an
entry
in tblApplications for PlantingdetailsID and if there is then the above
code
should fire bit if not then it should ignore the above code and carry on
to
save the data.
tblPlantingDetails---<tblApplications have a one to many relationship
linked
by plantingdetailsID field as FK.

Here is what I'd like to do:
The code in there is just pyseudo code, just to give you an idea.

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
'Check to see if there is any spray applications for the crop planting
'Pyseudoco

If cboPlantingDetailsID.column(0) In tblApplications.PlantingdetailsID
then
******

' Check to see if this planting is still inside With Holding Period

Dim strMsg As String
Dim iResponse As Integer
Dim rs As DAO.Recordset
Dim dtWHP As Date

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from qryWHP where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close

If Date < dtWHP Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Continue or No to Back Out."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If

Else

*** carry on as normal

End Sub


--
niuginikiwi
Nelson, New Zealand


Graham Mandeno said:
Hi niuginikiwi

For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

No you don't - you just need to check the latest (maximum) one.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Just replace your DLookup by DMax and it should work for you.

A more efficient solution would be to open a recordset with only the
single
value you require:

Dim rs as DAO.Recordset
Dim dtWHP as Date
Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from ApplicationsTable where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close
If Date < dtWHP Then
...
End If


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thank you so much for the reply with the corrections and suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am
trying
to
do.

I have a list of crop plantings which get sprayed with different
products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date
(ApplicationDate)
with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some WHPDate
values
for that selected planting which are higher than todays date, but the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP", "[qryWHP.PlantingDetailsID]
= "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



:

Hi niuginikiwi

By formatting your dates before you compare them, you are actually
comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not
what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date
data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You
cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use
cboPlantingDetailsID.Undo.
It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you
do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a query called qryWHP which has a caluclated date field which
I
would
like to compare with today's date and throw a vbYesNo popup depeding
on
the
condition. If Date() is less than WHPDate then fire vbYesNo and then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID]
=
" &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?"
&
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

I have done two changes which appears to work the way I wanted. Thank you so
much for your help.

1. put in SQL in vba to work straight from tables as you have suggested.
2. put the Nz() around the rs!WHPDate bit to have a zero if null.

Here is the full code again incase anyone would need it in the future:

Private Sub PlantingDetailsID_BeforeUpdate(Cancel As Integer)

' Check to see if this planting is still inside With Holding Period

Dim strMsg As String
Dim iResponse As Integer
Dim rs As DAO.Recordset
Dim dtWHP As Date

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from tblProductDetails INNER JOIN (tblApplications INNER JOIN
tblApplicationDetails ON tblApplications.ApplicationID =
tblApplicationDetails.ApplicationID) ON tblProductDetails.ProductDetailsID =
tblApplicationDetails.ProductDetailsID where PlantingDetailsID = " _
& PlantingDetailsID.Column(0), dbOpenForwardOnly)

If rs.RecordCount > 0 Then dtWHP = Nz(rs!WHPDate, 0)
rs.Close

If Date < dtWHP Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Continue or No to Back Out."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If

End Sub



Thanks again Graham.

Regards,
--
niuginikiwi
Nelson, New Zealand


Graham Mandeno said:
Hi NGK

The code should be working that way, I think. If there are no sprayings (no
records in tblApplications) then rs.RecordCount should be zero, so dtWHPDate
should be zero (initialised as declared); therefore, as Date() is NOT be
less than zero, the If...Then block will not be executed.

Hang on... I just saw you are using qryWHP in the SELECT statement instead
of tblApplications. I have no idea what qryWHP does, but I think you should
cut out the middle man and go straight to the table.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thankyou for the help. That really did sort me out and it appears to be
working now the way I wanted.

One more thing is that I find is that some crop plantings do not get
sprayed so in that case I will need to check first whethere there is an
entry
in tblApplications for PlantingdetailsID and if there is then the above
code
should fire bit if not then it should ignore the above code and carry on
to
save the data.
tblPlantingDetails---<tblApplications have a one to many relationship
linked
by plantingdetailsID field as FK.

Here is what I'd like to do:
The code in there is just pyseudo code, just to give you an idea.

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
'Check to see if there is any spray applications for the crop planting
'Pyseudoco

If cboPlantingDetailsID.column(0) In tblApplications.PlantingdetailsID
then
******

' Check to see if this planting is still inside With Holding Period

Dim strMsg As String
Dim iResponse As Integer
Dim rs As DAO.Recordset
Dim dtWHP As Date

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from qryWHP where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close

If Date < dtWHP Then

strMsg = "Still inside WHP. Do you wish to continue?" & Chr(10)
strMsg = strMsg & "Click Yes to Continue or No to Back Out."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If

Else

*** carry on as normal

End Sub


--
niuginikiwi
Nelson, New Zealand


Graham Mandeno said:
Hi niuginikiwi

For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

No you don't - you just need to check the latest (maximum) one.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Just replace your DLookup by DMax and it should work for you.

A more efficient solution would be to open a recordset with only the
single
value you require:

Dim rs as DAO.Recordset
Dim dtWHP as Date
Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from ApplicationsTable where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close
If Date < dtWHP Then
...
End If


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thank you so much for the reply with the corrections and suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am
trying
to
do.

I have a list of crop plantings which get sprayed with different
products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date
(ApplicationDate)
with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not more
than
today's date.

So basically when I click on cboPlantingDetailsID to select a planting,
the
before insert event should fire and compare todays date to several date
entries in the qryWHP to make sure none of them are more than today's
date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some WHPDate
values
for that selected planting which are higher than todays date, but the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP", "[qryWHP.PlantingDetailsID]
= "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



:

Hi niuginikiwi

By formatting your dates before you compare them, you are actually
comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is not
what
you want!

If [WHPDate] is a date/time field, then varX should already be a Date
data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You
cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use
cboPlantingDetailsID.Undo.
It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1, you
do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a query called qryWHP which has a caluclated date field which
I
would
like to compare with today's date and throw a vbYesNo popup depeding
on
the
condition. If Date() is less than WHPDate then fire vbYesNo and then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo called
cboplantingdetailsID looks like. I even dont know if dlookup in this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP", "[qryWHP.PlantingDetailsID]
=
" &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to continue?"
&
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi NGK

I see - you have a many-many relationship between Applications and Products.

No problem - just insert the full FROM clause from your query into the code:

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "FROM tblProductDetails INNER JOIN (tblApplications " _
& "INNER JOIN tblApplicationDetails " _
& "ON tblApplications.ApplicationID " _
& "= tblApplicationDetails.ApplicationID) " _
& "ON tblProductDetails.ProductDetailsID " _
& "= tblApplicationDetails.ProductDetailsID " _
& "WHERE PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)

I'm not entirely sure where PlantingDetailsID comes from. I assume it is in
tblApplications, but it does not seem to be included in qryWHP.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thankyou for writing again, yes it would have been better to have it going
straight to tblApplications. However, the reason why I changed to qryWHP
is
that ApplicationDate and WHP fields are in two different tables.
ApplicationDate is in tblApplications and WHP is in tblProductDetails.
Here
is what the relationship between tblApplications and tblProductDetails.

tblApplications ---<tblApplicationDetails>---tblProductDetails.

Maybe it might pay put the SQL behind qryWHP here to go straight to the
tables. Here is what the SQL of qryWHP looks like:

SELECT tblApplications.ApplicationDate, tblProductDetails.WHP
FROM tblProductDetails INNER JOIN (tblApplications INNER JOIN
tblApplicationDetails ON tblApplications.ApplicationID =
tblApplicationDetails.ApplicationID) ON tblProductDetails.ProductDetailsID
=
tblApplicationDetails.ProductDetailsID
GROUP BY tblApplications.ApplicationDate, tblProductDetails.WHP;

Any suggetions?

Graham Mandeno said:
Hi NGK

The code should be working that way, I think. If there are no sprayings
(no
records in tblApplications) then rs.RecordCount should be zero, so
dtWHPDate
should be zero (initialised as declared); therefore, as Date() is NOT be
less than zero, the If...Then block will not be executed.

Hang on... I just saw you are using qryWHP in the SELECT statement
instead
of tblApplications. I have no idea what qryWHP does, but I think you
should
cut out the middle man and go straight to the table.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

niuginikiwi said:
Hi Graham,

Thankyou for the help. That really did sort me out and it appears to be
working now the way I wanted.

One more thing is that I find is that some crop plantings do not get
sprayed so in that case I will need to check first whethere there is an
entry
in tblApplications for PlantingdetailsID and if there is then the above
code
should fire bit if not then it should ignore the above code and carry
on
to
save the data.
tblPlantingDetails---<tblApplications have a one to many relationship
linked
by plantingdetailsID field as FK.

Here is what I'd like to do:
The code in there is just pyseudo code, just to give you an idea.

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
'Check to see if there is any spray applications for the crop planting
'Pyseudoco

If cboPlantingDetailsID.column(0) In tblApplications.PlantingdetailsID
then
******

' Check to see if this planting is still inside With Holding Period

Dim strMsg As String
Dim iResponse As Integer
Dim rs As DAO.Recordset
Dim dtWHP As Date

Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from qryWHP where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close

If Date < dtWHP Then

strMsg = "Still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Continue or No to Back Out."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If

Else

*** carry on as normal

End Sub


--
niuginikiwi
Nelson, New Zealand


:

Hi niuginikiwi

For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not
more
than
today's date.

No you don't - you just need to check the latest (maximum) one.

So basically when I click on cboPlantingDetailsID to select a
planting,
the
before insert event should fire and compare todays date to several
date
entries in the qryWHP to make sure none of them are more than
today's
date,
and if they are, it fires the vbYesNo.

Just replace your DLookup by DMax and it should work for you.

A more efficient solution would be to open a recordset with only the
single
value you require:

Dim rs as DAO.Recordset
Dim dtWHP as Date
Set rs = CurrentDb.OpenRecordset( _
"Select Max(ApplicationDate + WHP) as WHPDate " _
& "from ApplicationsTable where PlantingDetailsID = " _
& cboPlantingDetailsID.Column(0), dbOpenForwardOnly)
If rs.RecordCount > 0 Then dtWHP = rs!WHPDate
rs.Close
If Date < dtWHP Then
...
End If


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thank you so much for the reply with the corrections and
suggestions.
I have changed the code to suit your suggestions. See below.
I also want to give you a clear idea of the scenario and what I am
trying
to
do.

I have a list of crop plantings which get sprayed with different
products
that have different with holding periods (WHP).
Say if a crop planting was spray yesterday with a spray that had a
witholding period of 7 days, then I add the spray date
(ApplicationDate)
with
WHP (7days) which gives me a field called WHPDate. ie WHPDate:
ApplicationDate + WHP
For one planting, serveral spray products are used so I have to
actaully
check serveral of the WHPDates on the go to make sure they are not
more
than
today's date.

So basically when I click on cboPlantingDetailsID to select a
planting,
the
before insert event should fire and compare todays date to several
date
entries in the qryWHP to make sure none of them are more than
today's
date,
and if they are, it fires the vbYesNo.

Here is the code after your corrction. It appears to let me select a
planting from cboPlantingDetailsID but I know that I have some
WHPDate
values
for that selected planting which are higher than todays date, but
the
vbYesNo is not firing.

What else am I missing?

Private Sub cboPlantingDetailsID_BeforeUpdate(Cancel As Integer)
' Check to see if this planting is still inside WHP
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup("[WHPDate]", "qryWHP",
"[qryWHP.PlantingDetailsID]
= "
& cboPlantingDetailsID.Column(0))

If Date < CDate(varX) Then

strMsg = "Still inside WHP. Do you wish to continue?" &
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

PlantingDetailsID.Undo

Cancel = True
End If
End If
End Sub



:

Hi niuginikiwi

By formatting your dates before you compare them, you are actually
comparing
two *strings*.

So "06/29/2009" would be less than "12/12/2008", which I'm sure is
not
what
you want!

If [WHPDate] is a date/time field, then varX should already be a
Date
data
type, so just compare it with Date() (which is already a Date):

If Date < varX Then ...

You might need to force a conversion to a Date:

If Date < CDate(varX) Then ...

Some other problems:

1. This should be in your BeforeUpdate event, not AfterUpdate. You
cannot
cancel the AfterUpdate event.

2. Instead of DoCmd.RunCommand acCmdUndo, use
cboPlantingDetailsID.Undo.
It
is much more specific and less prone to glitches.

3. Assuming this code is in the module of frmOrderDetailsSubform1,
you
do
not need the full form reference:
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID
should be
cboPlantingDetailsID

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
I have a query called qryWHP which has a caluclated date field
which
I
would
like to compare with today's date and throw a vbYesNo popup
depeding
on
the
condition. If Date() is less than WHPDate then fire vbYesNo and
then
follow
depending on whether user chooses yes or no.

Here is what what my code on the afterupdate event of a combo
called
cboplantingdetailsID looks like. I even dont know if dlookup in
this
situation is the right approach.

Private Sub cboPlantingDetailsID_AfterUpdate()

Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strMsg As String
Dim iResponse As Integer
Dim varX As Variant

varX = DLookup([WHPDate], "qryWHP",
"[qryWHP.PlantingDetailsID]
=
" &
Forms!frmOrderDetailsSubform1!cboPlantingDetailsID)

If Format(Date, conJetDate) < Format(varX, conJetDate) Then

strMsg = "Crop still inside WHP. Do you wish to
continue?"
&
Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard
changes."

iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save
Record?")

If iResponse = vbNo Then

DoCmd.RunCommand acCmdUndo

Cancel = True
End If
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top