docmd.runcommand acsaverecord : which event 1

[p27br]

Hi,

I have a form bound to a recordset. When the user has filled in the form, he can press the ADD NEW button or the CLOSE button. Either way he receives a message 'OK to save ?' triggered by the Form's before update event, if he clicks No, then Cancel = true.
The question is : if he clicks YES, do I need to specify DoCmd.RunCommand acCmdSaveRecord ?
I am asking because the record is not getting saved in the database. Here is the full form code :

Option Compare Database
Option Explicit

Private Sub cmdAddNew_Click()
On Error GoTo ErrorHandler
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Call CarryOver(Me)

ExitHere:
Exit Sub

ErrorHandler:

Select Case Err.Number
Case 2105
'ignore error

Case Else
MsgBox Err.Number & " " & Err.Description
End Select
Resume ExitHere

End Sub

Private Sub cmdClose_Click()
Dim OKToClose As Boolean

OKToClose = True

If OKToClose Then
DoCmd.Close acForm, Me.Name
End If

End Sub

Private Sub cmdSelectEndPeriod_Click()
Dim intItemId As Integer
DoCmd.OpenForm "frmPeriodListSelect", acNormal, , , , acDialog, OpenArgs:=Me!dat_dtm_start
If CurrentProject.AllForms("frmPeriodListSelect").IsLoaded Then
intItemId = Forms!frmPeriodListSelect.lstPeriods.ItemsSelected(0)
Me!dat_dtm_end.Value = Forms!frmPeriodListSelect.lstPeriods.ItemData(intItemId)
DoCmd.Close acForm, "frmPeriodListSelect"
End If
End Sub

Private Sub cmdSelectStartPeriod_Click()
Dim intItemId As Integer
DoCmd.OpenForm "frmPeriodListSelect", acNormal, , , , acDialog, OpenArgs:=Me!dat_dtm_start
If CurrentProject.AllForms("frmPeriodListSelect").IsLoaded Then
intItemId = Forms!frmPeriodListSelect.lstPeriods.ItemsSelected(0)
Me!dat_dtm_start.Value = Forms!frmPeriodListSelect.lstPeriods.ItemData(intItemId)
DoCmd.Close acForm, "frmPeriodListSelect"
End If
End Sub


Private Sub dat_lng_formatref_AfterUpdate()
If Me!dat_lng_formatref < 3 Then
Me!dat_lng_templref.Enabled = False
Else
Me!dat_lng_templref.Enabled = True
End If
End Sub


Private Sub dat_lng_georef_NotInList(NewData As String, Response As Integer)

Dim rs As ADODB.Recordset
Dim lngGeoID As Long

Set rs = New ADODB.Recordset

If vbYes = MsgBox("'" & NewData & "' is not a current geo structure." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
rs.Open "SELECT * FROM [tblGeo_Struct] WHERE 1=2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
With rs
.AddNew
![geo_str_abbr] = UCase(NewData)
lngGeoID = ![geo_lng_id]
.Update
End With
rs.Close
Set rs = Nothing
DoCmd.OpenForm "frmgeostruct", , , "[geo_lng_id]=" & lngGeoID
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Private Sub dat_lng_mktref_NotInList(NewData As String, Response As Integer)
Dim rs As ADODB.Recordset
Dim lngMktID As Long

Set rs = New ADODB.Recordset

If vbYes = MsgBox("'" & NewData & "' is not a current market structure." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
rs.Open "SELECT * FROM [tblmarket_Struct] WHERE 1=2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
With rs
.AddNew
![mkt_str_abbr] = UCase(NewData)
lngMktID = ![mkt_lng_id]
.Update
End With
rs.Close
Set rs = Nothing
DoCmd.OpenForm "frmmktstruct", , , "[mkt_lng_id]=" & lngMktID
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Private Sub dat_str_desc_AfterUpdate()
dat_str_desc = UCase(Me!dat_str_desc)
End Sub

Private Sub dat_str_desc_BeforeUpdate(Cancel As Integer)
If DatasetExists Then
MsgBox "This dataset already exists or an error occured !"
Cancel = True
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to save this record ?", vbOKCancel, "Confirm") = vbOK Then
Me!dat_dtm_timestamp = Now()
DoCmd.RunCommand acCmdSaveRecord
Else
Cancel = True
Exit Sub
End If

End Sub
Function DatasetExists() As Boolean

Dim rst As ADODB.Recordset
Dim strSQL As String

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset

strSQL = "SELECT dat_str_desc, dat_str_clinr " & _
"FROM tblDataset " & _
"WHERE (dat_str_desc = '" & Me!dat_str_desc.Value & "') AND " & _
"(dat_str_repid = '" & Me!dat_str_repid.Value & "')"

With rst
.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
If .EOF Or .BOF Then
DatasetExists = False
Else
DatasetExists = True
End If
End With

ExitHere:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & " " & Err.Description
DatasetExists = True
End Select
Resume ExitHere
End Function

 

[RoyVidar]

Hi!

- on a guessing tour...

I think I'd placed some kind of save command in the "addnew" and at least in the "close" button events, not the before update (think of what the before update event is, "last chance to validate before the record is saved", so I'm not sure what effect - good or bad - a save command there would create). You don't say if this (not saving) occurs on addnew or close, but I'm guessing it might be when you close. Have a look at this Losing data when you close a form with relevant links to Microsoft articles on the subject, and see if it applies.

Roy-Vidar

 

[p27br]

hi royvidar,

thanks for taking the time to enlighten me with your wisdom once again

the not saving occurs in both cases.
I'll have a look at the link and keep you posted.

cheers

 

[p27br]

I found out that I need to fill in a required field that is hidden on the form.
where's the best event to set the hidden field value ?

 

[RoyVidar]

Before update of the form, in my opinion, then you don't risk "dirtying" the form unless it's "needed".

Roy-Vidar

 

[p27br]

Okido,


have a star