Commit Action During Record Navigation

[bvbowes]

I want to be able to add some code that gets run every time someone uses the embedded "go to next record" navigation arrow at the bottom of the form, is that possible?

When users add some notes to an unbounded text box on the form the text will stay in each record they navigate to until the user hits an "Add Note" button that I created.

 

[PHV]

The Current event procedure is fired each time the user navigates to another record.
BTW, why mixing bound and unbound (but updatable) controls ?

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

 

[bvbowes]

Nice, thanks, I didn't know about the "On Current" event

I've added some code to just pop up a reminder to add the text (via a cmd button that already exists) but the form goes to the next record any way. Any way to stop it?

Here is what I've built so far.

Private Sub Form_Current()
If Not IsNull(Me!Notes12) Then 'stop navigation forward and then...
MsgBox "Please add your note before moving forward!", vbOKOnly, "Oops..."
Else ' or something needs to be here to stop the navigation to the next record
End If
End Sub

 

[PHV]

Just a though.
In the AfterUpdate event procedure of Notes12:
Me.Dirty = True ' the Form_BeforeUpdate will fire

In the BeforUpdate event procedure of the Form:
If Trim(Me!Notes12 & "") <> "" Then
MsgBox "Please add your note before moving forward!", vbOKOnly, "Oops..."
Cancel = True
Exit Sub
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

 

[bvbowes]

Yeah I tried that already, only bound fields can be modified with BeforeUpdate.

 

[PHV]

Are you saying that the AfterUpdate event procedure of Notes12 never fires ?

 

[bvbowes]

It fires great...I get this error message though in the after_update:

Run-time error '7768':

In order to change data through this form, the focus must be in a bound field tha can be modified.

 

[bvbowes]

What I meant to say is that dirty only works with bounded items....

 

[PHV]

Me![some bound control].SetFocus
Me.Dirty = True

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

 

[bvbowes]

Same error...

 

[PHV]

What is the value of the AllowEdits property of the form ?
Is the underlaying query updatable ?
You still didn't reply my question:
why mixing bound and unbound (but updatable) controls ?

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

 

[bvbowes]

Allow Edits = Yes

Yes it is updatable

I have it unbound so that I can pass the notes to a tbl that houses only the notesid, notes, windows user name, and timestamp where any form can pull the notes data into itself.

 

[PHV]

And how are the notes related (linked, joined) to the current record ?

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

 

[bvbowes]

I have a note table that is linked to the main form where the case number is via case number. But in order to have a one to many I just insert the max note id + 1 and insert the current case number at the same time.

Here is the code.

Function AddNotes()
Dim SqlAddNote As Variant
Dim db As Database
Set db = CurrentDb
Dim con As New ADODB.Connection
Set con = CurrentProject.Connection
Dim rst As New ADODB.Recordset

autoplus1 = "SELECT Max([tblNotes.NotesID]) AS MaxOfNotesID FROM tblNotes"
rst.Open autoplus1, con, adOpenDynamic, adLockOptimistic

AutoID_Count = rst!MaxOfNotesID
Autonumber = AutoID_Count + 1

CaseNumber = Forms!frmMainEntry.ControlName.CaseNumber1
Note1 = Forms!frmMainEntry.Notes12
SqlAddNote = "INSERT INTO [tblNotes] values ('" & Autonumber & "','" & Note1 & "','" & Now() & "','" & CaseNumber & "','" & WinUserName() & "')"
CurrentDb.Execute SqlAddNote, dbFailOnError
Notes = ""

con.Close
Set con = Nothing
rst.Close
Set rst = Nothing
End Function