UPDATE query only produces results on 2nd execution 2

[Magnetar]

Morning all (11:55, 19/11/04)

Can anyone help me with the following basic issue, please?

I have an UPDATE query, running within VBA. If a mandatory field on a form has not been completed, and the form is closed, then the UPDATE query marks that particular record for deletion (basically updates four fields: changes YES/NO field to Yes, date stamps Date field, time stamps Time field, and then logs the CurrentUser).

During testing, have noticed that whilst some of the time this query works fine, most of the time the record is not updated. I have to reopen the form again, and the record is still there (with blank mandatory field!).

Only then on closing the form, will the UPDATE query produce the required results.

Under 'Close' button of form, - code is as follows:

If IsNull(Me.Field1) Or Me. Field1 = 0 Then
ans = MsgBox("You have not entered a [Field1_Entry] for this record." & vbCr & _
"Do you want to discard this record (record will be deleted!)?", vbYesNo + vbQuestion, "Record Details will be DELETED!!")
If ans = vbYes Then
DoCmd.SetWarnings False

'Run 'Mark Record for Deletion' query in code. EB/DL-B. 15/11/04
MarkDeleteRecord ‘ß call sub procedure here.
Me.Requery
DoCmd.RunCommand acCmdRefreshPage

DoCmd.Close
Else
Me. Field1.SetFocus
End If
Exit Sub
End If

-------------------------------------------------------
The sub procedure contains the following code:

Sub MarkDeleteRecord()
'On selecting 'Delete', mark record for deletion only.
'Embed date, time and current user alongside record marked for deletion.


On Error GoTo cmdDeleteRecordErr

'Remove MS Access messages....
DoCmd.SetWarnings False

'Mark required, records for deletion, with date & time stamps, & CurrentUser()

DoCmd.RunSQL "UPDATE tblParentTable INNER JOIN tblChildTable ON tblParentTable.IDField = tblChildTable.IDField " & _
"SET " & _
" tblParentTable.DeleteRecord = -1, tblParentTable.DeleteDate = Date(), tblParentTable.DeleteTime = Time(),tblParentTable.DeletedBy = CurrentUser()," &_
" tblChildTable.DeleteRecord = -1, tblChildTable.DeleteDate = Date(),tblChildTable.DeleteTime = Time(),tblChildTable.DeletedBy = CurrentUser()" & _
"WHERE tblParentTable. IDField = " & [Forms]![frmParentTable]![txtIDField] & " AND tblChildTable.IDField = " & [Forms]![frmParentTable]![txtIDField]


'.....then replace.
DoCmd.SetWarnings True

cmdDeleteRecordExit:
Exit Sub

cmdDeleteRecordErr:

MsgBox "Error within Sub cmdDeleteRecord_Click(). Error no: " & Err.Number & _
". Error is: " & Err.Description
Resume cmdDeleteRecordExit

End Sub


I have refreshed the form, used debug, etc, and have even tried executing the code from within the form (I call a sub procedure normally, as the form has several mandatory fields).

Please help!

Kind regards, - Magnetar.

 

[lupins46]

I haven't gone through the code in detail but are you not trying to update a record that has not yet been saved?

 

[Magnetar]

Hi lupins46, thanks for your reply.

At the moment I have been testing the form, for all existing records.

Just to set the scene...
The situation is that if a mandatory field has even been deleted by mistake, the entire record is automatically deleted.

Unfortunately, the requirement for this (high profile) system that I have 'inherited' and now support, is such that records must not stored, without the completion of certain fields.
What do you suggest? (Many thanks in advance).

Magnetar

 

[randy700]

My first thought is to insert....
DoCmd.RunCommand acSaveRecord

...prior to running the MarkDeleteRecord routine.


Randy

 

[Magnetar]

Guys, hi there again.

Thanks you both EVER so much for your suggestions, (the 'DoCmd.RunCommand acSaveRecord' works a treat!).
[Have some understanding users, who've acquired, & are still getting acqainted with (!) the database, so this will help/please them no end!!].

Once again a big thank you to you both.

Kind regards

Magnetar