HELP w/ docmd.close

[Nick34]

I have a form that has some entry fields on it. When the focus is lost on a certain field, a search is initiated, and if duplicates are found, the fields are returned to null, and I want to close the form.

The problem I get is that there is a run time error 2585. "The action can't be carried out while processing a form event."

Is there a workaround for this? How can I close the form automatically?

 

[vbajock]

I don't know how your coding it so it is difficult to tell you what to do. Can you post the code that is in the event that kicks this off?

 

[Nick34]

Here is the code that I am using...

My problem is the last line before the end of the subroutine.

Private Sub Month_Year_LostFocus()

Dim sDate As String
Dim dDate As Date
Dim ddate1 As Date
Dim sUnit As String
Dim myBoxDate As Access.TextBox
Dim myBoxUnit As Access.ComboBox
Dim rst As ADODB.Recordset


Set myBoxDate = Me.Controls!Month_Year
Set myBoxUnit = Me.Controls!Unit


'format the date value to mmmm-yyyy
sDate = myBoxDate
dDate = CDate(sDate)
ddate1 = Format(dDate, "mmmm-yyyy&quot

'Set the value of the combo box to a variable
sUnit = myBoxUnit.Value

'create the recordset to be searched for duplicates
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockReadOnly
rst.Source = "NGSCommunityCommitmentTons"
rst.Open Options:=adCmdTable

'search for the unit # that was entered and the date that was entered.
'If either one exist, clear them out so a new record is not added
'and then close the form. If one of them does not exist, assume that
'this record will be a new one.

With rst
.Find "Unit = '" & sUnit & "'"
If Not .EOF Then
.Find ("Month_Year = " & ddate1)
If Not .EOF Then
msgbox "There is already data entered for this month. Use the " & _
"NGS Community Commitment Update Form.", vbOKOnly

myBoxDate.Value = Null
myBoxUnit.Value = Null

End If
End If
End With

Set rst = Nothing

'Move the focus to the next control so the form can be closed.
Me.Controls!Unit.SetFocus

DoCmd.Close acForm, "NGS Community Commitment Entry Form", acSaveNo

End Sub

 

[vbajock]

Let's try this first:

comment out this line
'rst.CursorType = adOpenKeyset
Me.Controls!Unit.SetFocus
add this command
DOEVENTS
Me.refresh
DoCmd.Close acForm, "NGS Community Commitment Entry Form", acSaveNo

If that doesn't work, instead of doing all the finds, why don't you just use a select statement?

it would look something like:
Dim sql as string

sql=SELECT * FROM NGSCommunityCommitmentTons
sql=sql& "WHERE Unit = '" & sUnit & "' AND Month_Year = #" & ddate1 &"#"
rst.Source = sql
rst.Open Options:=adCmdTEXT

WITH rst
If Not .EOF Then 'TABLE IS NOT EMPTY

msgbox "There is already data entered for this month. Use the " & _
"NGS Community Commitment Update Form.", vbOKOnly

myBoxDate.Value = Null
myBoxUnit.Value = Null

End With

 

[vbajock]

Correction to above:
WITH rst

====>If Not .EOF Then 'TABLE IS NOT EMPTY
should read==>

If .BOF and .EOF then
'tables empty do what ever

else
'table ahs data in it
msgbox "There is already data entered for this month. Use the " & _
"NGS Community Commitment Update Form.", vbOKOnly

myBoxDate.Value = Null
myBoxUnit.Value = Null

End With