Problem with Auto setting of Date

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo![ProCategory]) Then
sStatusText = Forms!projectinfo!ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date when
the status in another field is
either "Cancelled", "Closed" or "Deferred". However this
date value changes any time the record is eddited.

Can anyone please Help?
 
Nick,

<<However this date value changes any time the record is eddited.>>
No it doesn't! On its own, this code does absolutely nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited, then you need to take
a closer look at *when* you're calling pSetClosedDate().

By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks for your reply graham

What I sjould have said is:
<<However this date value changes any time the record is
Click to expand...
eddited when the status is set to "closed", cancelled"
or "deferred" .>>

This is what is calling the code:



Private Sub form_beforeupdate(Cancel As Integer)

' Set the modified date to the change occured
Me.txtDateModified = Date

' Set the closed date if the status has been set to
closed or cancelled
Call pSetClosedDate

'set the project type
Call pSetProjectType
End Sub

Thanks for your help
-----Original Message-----
Nick,

<<However this date value changes any time the record is eddited.>>
No it doesn't! On its own, this code does absolutely nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited, then you need to take
a closer look at *when* you're calling pSetClosedDate().

By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo![ProCategory]) Then
sStatusText = Forms!projectinfo!ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date when
the status in another field is
either "Cancelled", "Closed" or "Deferred". However this
date value changes any time the record is eddited.

Can anyone please Help?
Click to expand...


.
Click to expand...
 
Nick,

But I don't understand your question/problem. What do you want it to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Thanks for your reply graham

What I sjould have said is:
<<However this date value changes any time the record is
Click to expand...
eddited when the status is set to "closed", cancelled"
or "deferred" .>>

This is what is calling the code:



Private Sub form_beforeupdate(Cancel As Integer)

' Set the modified date to the change occured
Me.txtDateModified = Date

' Set the closed date if the status has been set to
closed or cancelled
Call pSetClosedDate

'set the project type
Call pSetProjectType
End Sub

Thanks for your help
-----Original Message-----
Nick,

<<However this date value changes any time the record is eddited.>>
No it doesn't! On its own, this code does absolutely nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited, then you need to take
a closer look at *when* you're calling pSetClosedDate().

By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo![ProCategory]) Then
sStatusText = Forms!projectinfo!ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date when
the status in another field is
either "Cancelled", "Closed" or "Deferred". However this
date value changes any time the record is eddited.

Can anyone please Help?
Click to expand...


.
Click to expand...
Click to expand...
 
Sorry Mate,I'll try and be clearer this time.

When the status of a record is changed
to "Closed", "cancelled" or "deferred" i would like the
date of this change recorded in a field called "Date
Closed". This works OK at at the moment. The problem is,
if I then change the value of another field in the same
record, the "date closed" will also change. I would
like "Date closed" to only change when status is changed
to "Closed", "cancelled" or "deferred".

The "date closed" is acting as type of date modified when
status is set to "Closed", "cancelled" or "deferred".

I hope this is clearer.

Thanks
-----Original Message-----
Nick,

But I don't understand your question/problem. What do you want it to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Thanks for your reply graham

What I sjould have said is:
<<However this date value changes any time the record
Click to expand...
is
eddited when the status is set to "closed", cancelled"
or "deferred" .>>

This is what is calling the code:



Private Sub form_beforeupdate(Cancel As Integer)

' Set the modified date to the change occured
Me.txtDateModified = Date

' Set the closed date if the status has been set to
closed or cancelled
Call pSetClosedDate

'set the project type
Call pSetProjectType
End Sub

Thanks for your help
-----Original Message-----
Nick,

<<However this date value changes any time the record
Click to expand...
is
eddited.>>
No it doesn't! On its own, this code does absolutely nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited, then you need to take
a closer look at *when* you're calling pSetClosedDate ().

By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo![ProCategory]) Then
sStatusText = Forms!projectinfo!ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date when
the status in another field is
either "Cancelled", "Closed" or "Deferred". However this
date value changes any time the record is eddited.

Can anyone please Help?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Nick,

OK, gotcha!

I'm assuming the Status control is a Combo Box, and that you have a textbox
on the same form, called txtDateClosed.

In the AfterUpdate event for the cboStatus control, add the following:
Select Case Me!cboStatus
Case "Closed", "Cancelled", "Deferred"
Me!txtDateClosed = Date
Case Else
'No action
End Select

Then get rid of that other stuff in Form_BeforeUpdate().

The date will be saved when the record is saved.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Sorry Mate,I'll try and be clearer this time.

When the status of a record is changed
to "Closed", "cancelled" or "deferred" i would like the
date of this change recorded in a field called "Date
Closed". This works OK at at the moment. The problem is,
if I then change the value of another field in the same
record, the "date closed" will also change. I would
like "Date closed" to only change when status is changed
to "Closed", "cancelled" or "deferred".

The "date closed" is acting as type of date modified when
status is set to "Closed", "cancelled" or "deferred".

I hope this is clearer.

Thanks
-----Original Message-----
Nick,

But I don't understand your question/problem. What do you want it to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Thanks for your reply graham

What I sjould have said is:
<<However this date value changes any time the record is
eddited when the status is set to "closed", cancelled"
or "deferred" .>>

This is what is calling the code:



Private Sub form_beforeupdate(Cancel As Integer)

' Set the modified date to the change occured
Me.txtDateModified = Date

' Set the closed date if the status has been set to
closed or cancelled
Call pSetClosedDate

'set the project type
Call pSetProjectType
End Sub

Thanks for your help
-----Original Message-----
Nick,

<<However this date value changes any time the record is
eddited.>>
No it doesn't! On its own, this code does absolutely
nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited,
then you need to take
a closer look at *when* you're calling pSetClosedDate ().

By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-
0764559036.html


message
I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo![ProCategory])
Then
sStatusText = Forms!projectinfo!ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date
when
the status in another field is
either "Cancelled", "Closed" or "Deferred". However this
date value changes any time the record is eddited.

Can anyone please Help?


.
Click to expand...


.
Click to expand...
Click to expand...
 
Works a treat except...

In the one form I have the same status control combo box
in five different pages. These all have the same control
source, but have different names. The below only works
when status is changed in the cboStatus Contol. is it
necessary to add for each of the controls or is there a
better way of doing the same??

Cheers,

Nick
-----Original Message-----
Nick,

OK, gotcha!

I'm assuming the Status control is a Combo Box, and that you have a textbox
on the same form, called txtDateClosed.

In the AfterUpdate event for the cboStatus control, add the following:
Select Case Me!cboStatus
Case "Closed", "Cancelled", "Deferred"
Me!txtDateClosed = Date
Case Else
'No action
End Select

Then get rid of that other stuff in Form_BeforeUpdate().

The date will be saved when the record is saved.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Sorry Mate,I'll try and be clearer this time.

When the status of a record is changed
to "Closed", "cancelled" or "deferred" i would like the
date of this change recorded in a field called "Date
Closed". This works OK at at the moment. The problem is,
if I then change the value of another field in the same
record, the "date closed" will also change. I would
like "Date closed" to only change when status is changed
to "Closed", "cancelled" or "deferred".

The "date closed" is acting as type of date modified when
status is set to "Closed", "cancelled" or "deferred".

I hope this is clearer.

Thanks
-----Original Message-----
Nick,

But I don't understand your question/problem. What do
Click to expand...
you
want it to do?
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Thanks for your reply graham

What I sjould have said is:
<<However this date value changes any time the
Click to expand...
record
is
eddited when the status is set to "closed", cancelled"
or "deferred" .>>

This is what is calling the code:



Private Sub form_beforeupdate(Cancel As Integer)

' Set the modified date to the change occured
Me.txtDateModified = Date

' Set the closed date if the status has been set to
closed or cancelled
Call pSetClosedDate

'set the project type
Call pSetProjectType
End Sub

Thanks for your help
-----Original Message-----
Nick,

<<However this date value changes any time the
Click to expand...
record
is
eddited.>>
No it doesn't! On its own, this code does absolutely
nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited,
then you need to take
a closer look at *when* you're calling
Click to expand...
pSetClosedDate
().
By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-
0764559036.html


message
I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo! [ProCategory])
Then
sStatusText = Forms!projectinfo! ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date
when
the status in another field is
either "Cancelled", "Closed" or "Deferred".
Click to expand...
However
this
date value changes any time the record is eddited.

Can anyone please Help?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Nick,

You have several controls on the same form bound to the same field???

Pull the redundant controls off the pages and leave a single control on the
main form. If you really want to, you can always control its visibility by
checking the Page No.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Works a treat except...

In the one form I have the same status control combo box
in five different pages. These all have the same control
source, but have different names. The below only works
when status is changed in the cboStatus Contol. is it
necessary to add for each of the controls or is there a
better way of doing the same??

Cheers,

Nick
-----Original Message-----
Nick,

OK, gotcha!

I'm assuming the Status control is a Combo Box, and that you have a textbox
on the same form, called txtDateClosed.

In the AfterUpdate event for the cboStatus control, add the following:
Select Case Me!cboStatus
Case "Closed", "Cancelled", "Deferred"
Me!txtDateClosed = Date
Case Else
'No action
End Select

Then get rid of that other stuff in Form_BeforeUpdate().

The date will be saved when the record is saved.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Sorry Mate,I'll try and be clearer this time.

When the status of a record is changed
to "Closed", "cancelled" or "deferred" i would like the
date of this change recorded in a field called "Date
Closed". This works OK at at the moment. The problem is,
if I then change the value of another field in the same
record, the "date closed" will also change. I would
like "Date closed" to only change when status is changed
to "Closed", "cancelled" or "deferred".

The "date closed" is acting as type of date modified when
status is set to "Closed", "cancelled" or "deferred".

I hope this is clearer.

Thanks
-----Original Message-----
Nick,

But I don't understand your question/problem. What do you
want it to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-
0764559036.html


Thanks for your reply graham

What I sjould have said is:
<<However this date value changes any time the record
is
eddited when the status is set to "closed", cancelled"
or "deferred" .>>

This is what is calling the code:



Private Sub form_beforeupdate(Cancel As Integer)

' Set the modified date to the change occured
Me.txtDateModified = Date

' Set the closed date if the status has been set to
closed or cancelled
Call pSetClosedDate

'set the project type
Call pSetProjectType
End Sub

Thanks for your help
-----Original Message-----
Nick,

<<However this date value changes any time the record
is
eddited.>>
No it doesn't! On its own, this code does absolutely
nothing! What causes it
to do anything is the code that calls it.

If the date is changing any time the record is edited,
then you need to take
a closer look at *when* you're calling pSetClosedDate
().

By the way, you could simplify your Case block like so:
Select Case sStatusText
Case "Closed", "Cancelled", "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-
0764559036.html


message
I Currently have this:

Private Sub pSetClosedDate()

Dim sStatusText As String
If Not IsNull(Forms!projectinfo! [ProCategory])
Then
sStatusText = Forms!projectinfo! ProCategory

Select Case sStatusText
Case "Closed"
Forms!projectinfo!txtDateClosed = Date
Case "Cancelled"
Forms!projectinfo!txtDateClosed = Date
Case "Deferred"
Forms!projectinfo!txtDateClosed = Date
Case Else
Forms!projectinfo!txtDateClosed = ""
End Select
End If

End Sub
__________
This is supposed to set and maintain the current date
when
the status in another field is
either "Cancelled", "Closed" or "Deferred". However
this
date value changes any time the record is eddited.

Can anyone please Help?


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top