Close a form two ways

[Robert_L_Ross]

Here is my problem...

I am trying to code two ways of closing a form:
One way cancells the existing record, closes the form and returns to the
previous form.
The second way closes the form and requeries the previous form.

The basis for this decision is the [date finalized] field on my form. Here
is the code I'm trying:
Dim SearchBatchID As String

SearchBatchID = Forms!CurrentAuditStatusIncompleteAudits!BatchID

If Forms!Audits![Date Finalized] Is Null Then
SendKeys "{ESC}{ESC}", False
DoCmd.Close acForm, "Audits", acSaveNo
Else
DoCmd.Close acForm, "Audits", acSaveNo
DoCmd.Requery ""
Me![BatchID].SetFocus
DoCmd.FindRecord SearchBatchID, , , , False
End If

Any ideas?

 

[Robert_L_Ross]

I found the answer...

In an unrelated post I read that Null is not a valid operator in VBA, that
you need to use the IsNull function, so I re-wrote the code as follows:
Private Sub CloseAudit_Click()

Dim SearchBatchID As String

SearchBatchID = Forms!CurrentAuditStatusIncompleteAudits!BatchID

If IsNull(Forms!Audits![Date Finalized]) Then
SendKeys "{ESC}", True
SendKeys "{ESC}", True
SendKeys "{ESC}", True
DoCmd.Close acForm, "Audits", acSaveNo
Else
DoCmd.Close acForm, "Audits", acSaveNo
DoCmd.Requery ""
DoCmd.GoToControl "BatchID"
DoCmd.FindRecord SearchBatchID, , , , False
End If

End Sub

I had to modify the SendKeys statement to set Wait to True, otherwise the
form closed so quickly it would inadvertently save the record.

Just out of curiosity, how would I replace the SendKeys statements (who's
purpose is only to make sure the record is cancelled before saving) with
something else? I've heard that the SendKeys statement is a bad idea in
VB...all I want to do is to cancel the current record before saving so when I
close the form it acts as if the record never existed.

THX!

 

[Klatuu]

If IsNull(Forms!Audits![Date Finalized]) Then
Me.Undo
DoCmd.Close acForm, "Audits", acSaveNo
Else
DoCmd.Close acForm, "Audits", acSaveNo
Me.Requery
Me!BatchID.SetFocus
DoCmd.FindRecord SearchBatchID, , , , False
End If

 

[Robert_L_Ross]

Klatuu,

THX, that works perfect!

If IsNull(Forms!Audits![Date Finalized]) Then
Me.Undo
DoCmd.Close acForm, "Audits", acSaveNo
Else
DoCmd.Close acForm, "Audits", acSaveNo
Me.Requery
Me!BatchID.SetFocus
DoCmd.FindRecord SearchBatchID, , , , False
End If

Here is my problem...

I am trying to code two ways of closing a form:
One way cancells the existing record, closes the form and returns to the
previous form.
The second way closes the form and requeries the previous form.

The basis for this decision is the [date finalized] field on my form. Here
is the code I'm trying:
Dim SearchBatchID As String

SearchBatchID = Forms!CurrentAuditStatusIncompleteAudits!BatchID

If Forms!Audits![Date Finalized] Is Null Then
SendKeys "{ESC}{ESC}", False
DoCmd.Close acForm, "Audits", acSaveNo
Else
DoCmd.Close acForm, "Audits", acSaveNo
DoCmd.Requery ""
Me![BatchID].SetFocus
DoCmd.FindRecord SearchBatchID, , , , False
End If

Any ideas?