Form Insert/Update by Procedure call

[jasenpeters]

XPPro SP2, Access 2003

I would like to call a custom function on the 'before Insert/Update'
events of a form.

I've tried the following procedure call in the event code...

LogDate "Insert", Me.Recordset

The procedure definition is....

Public Sub LogDate( strEvent as string, rs as DAO.Recordset)
With rs
If strActivity = "Insert" Then
.Edit
![InsertDate] = Now()
.Update
ElseIf strActivity = "Update" Then
.Edit
![UpdateDate] = Now()
.Update
End If
End With
End sub

However, the form does not reflect the changes. Is there something
missing in the code?
I'm sure it has to do with being on the "current" record. I assume that
when the recordset is passed to the procedure that it is still pointing
at the "new" record?

Ultimately, I would like to add Date and other information to the
current/new record by using a procedure call.

TIA

Jasen

 

[Baz]

XPPro SP2, Access 2003

I would like to call a custom function on the 'before Insert/Update'
events of a form.

I've tried the following procedure call in the event code...

LogDate "Insert", Me.Recordset

The procedure definition is....

Public Sub LogDate( strEvent as string, rs as DAO.Recordset)
With rs
If strActivity = "Insert" Then
.Edit
![InsertDate] = Now()
.Update
ElseIf strActivity = "Update" Then
.Edit
![UpdateDate] = Now()
.Update
End If
End With
End sub

However, the form does not reflect the changes. Is there something
missing in the code?
I'm sure it has to do with being on the "current" record. I assume that
when the recordset is passed to the procedure that it is still pointing
at the "new" record?

Ultimately, I would like to add Date and other information to the
current/new record by using a procedure call.

TIA

Jasen

For a start, you only need to code the BeforeUpdate event, it fires for both
inserts and updates.

Secondly, I can't understand why you want to update the form's recordset.
Surely this is what you want (called ONLY from BeforeUpdate):

Public Sub LogDate()

If Me.NewRecord Then
[InsertDate] = Now()
Else
[UpdateDate] = Now()
End If

End Sub

 

[jasenpeters]

Baz,

I'm sorry, after reading, I realized I didn't explain very well.

I would like to call custom procedure in a separate module, so all
forms/subforms can call it on the Update event.

This is why my code is passing the forms recordset to the procedure.

Can this still be done?

TIA

Jasen

XPPro SP2, Access 2003

I would like to call a custom function on the 'before Insert/Update'
events of a form.

I've tried the following procedure call in the event code...

LogDate "Insert", Me.Recordset

The procedure definition is....

Public Sub LogDate( strEvent as string, rs as DAO.Recordset)
With rs
If strActivity = "Insert" Then
.Edit
![InsertDate] = Now()
.Update
ElseIf strActivity = "Update" Then
.Edit
![UpdateDate] = Now()
.Update
End If
End With
End sub

However, the form does not reflect the changes. Is there something
missing in the code?
I'm sure it has to do with being on the "current" record. I assume that
when the recordset is passed to the procedure that it is still pointing
at the "new" record?

Ultimately, I would like to add Date and other information to the
current/new record by using a procedure call.

TIA

Jasen

For a start, you only need to code the BeforeUpdate event, it fires for both
inserts and updates.

Secondly, I can't understand why you want to update the form's recordset.
Surely this is what you want (called ONLY from BeforeUpdate):

Public Sub LogDate()

If Me.NewRecord Then
[InsertDate] = Now()
Else
[UpdateDate] = Now()
End If

End Sub

 

[jasenpeters]

Again apologies,

I discovered that passing the form instead of the form's recordset
worked as expected...

Public Sub LogDate(frm as Form)
With frm
If Me.NewRecord Then
[InsertDate] = Now()
Else
[UpdateDate] = Now()
End If
End With
End Sub

Seems to work fine. I had thought the approach would be to directly
access the form's recordset.

Thanks

Jasen

Baz,

I'm sorry, after reading, I realized I didn't explain very well.

I would like to call custom procedure in a separate module, so all
forms/subforms can call it on the Update event.

This is why my code is passing the forms recordset to the procedure.

Can this still be done?

TIA

Jasen

XPPro SP2, Access 2003

I would like to call a custom function on the 'before Insert/Update'
events of a form.

I've tried the following procedure call in the event code...

LogDate "Insert", Me.Recordset

The procedure definition is....

Public Sub LogDate( strEvent as string, rs as DAO.Recordset)
With rs
If strActivity = "Insert" Then
.Edit
![InsertDate] = Now()
.Update
ElseIf strActivity = "Update" Then
.Edit
![UpdateDate] = Now()
.Update
End If
End With
End sub

However, the form does not reflect the changes. Is there something
missing in the code?
I'm sure it has to do with being on the "current" record. I assume that
when the recordset is passed to the procedure that it is still pointing
at the "new" record?

Ultimately, I would like to add Date and other information to the
current/new record by using a procedure call.

TIA

Jasen

For a start, you only need to code the BeforeUpdate event, it fires for both
inserts and updates.

Secondly, I can't understand why you want to update the form's recordset.
Surely this is what you want (called ONLY from BeforeUpdate):

Public Sub LogDate()

If Me.NewRecord Then
[InsertDate] = Now()
Else
[UpdateDate] = Now()
End If

End Sub

 

[Baz]

Again apologies,

I discovered that passing the form instead of the form's recordset
worked as expected...

Public Sub LogDate(frm as Form)
With frm
If Me.NewRecord Then
[InsertDate] = Now()
Else
[UpdateDate] = Now()
End If
End With
End Sub

Seems to work fine. I had thought the approach would be to directly
access the form's recordset.

Thanks

Jasen

I'd be amazed if that works. This will work:

Public Sub LogDate(frm as Form)
With frm
If frm.NewRecord Then
frm![InsertDate] = Now()
Else
frm![UpdateDate] = Now()
End If
End With
End Sub

 

[Baz]

Again apologies,

I discovered that passing the form instead of the form's recordset
worked as expected...

Public Sub LogDate(frm as Form)
With frm
If Me.NewRecord Then
[InsertDate] = Now()
Else
[UpdateDate] = Now()
End If
End With
End Sub

Seems to work fine. I had thought the approach would be to directly
access the form's recordset.

Thanks

Jasen

I'd be amazed if that works. This will work:

Public Sub LogDate(frm as Form)
With frm
If frm.NewRecord Then
frm![InsertDate] = Now()
Else
frm![UpdateDate] = Now()
End If
End With
End Sub

Sorry, or this <doh>:

Public Sub LogDate(frm as Form)
With frm
If .NewRecord Then
![InsertDate] = Now()
Else
![UpdateDate] = Now()
End If
End With
End Sub