I'm getting a runtime error

[Afrosheen via AccessMonster.com]

Thanks for reading this post and helping me out. What I'm trying to do is to
close my program if there are no records, but I'm getting a runtime error
2585. This action can't be carried while processing a form or report event.
Here's my code

Private Sub Form_Current()
Dim rsClone As Recordset
15 pictdisplay
20 If Me.RecordsetClone.RecordCount = 0 Then
50 Call MsgBox(" End of Records on file ", vbInformation, Application.
Name)
60 DoCmd.Close
70 End If

80 Set rsClone = Me.RecordsetClone
90 rsClone.Bookmark = Me.Bookmark
100 rsClone.MovePrevious
110 cmdFirst.Enabled = Not (rsClone.BOF)
120 cmdPrevious.Enabled = Not (rsClone.BOF)
130 rsClone.MoveNext
140 rsClone.MoveNext
150 cmdNext.Enabled = Not (rsClone.EOF)
160 cmdLast.Enabled = Not (rsClone.EOF)

'********** Closes the Cloned Recordset ****************
170 rsClone.Close
end sub

The problem line is 60

 

[Allen Browne]

As the error message said, you can't close the form during its Current
event.

If you are seeking to do this when the form first opens, use its Open event:
Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
Cancel = True
MsgBox "No records."
End If
End Sub

 

[Afrosheen via AccessMonster.com]

Thanks Allen for getting back to me.

What I'm trying to do is after filling in some info checking to see if the
recordset is empty. If it is then tell me No Records and closes the form

As the error message said, you can't close the form during its Current
event.

If you are seeking to do this when the form first opens, use its Open event:
Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
Cancel = True
MsgBox "No records."
End If
End Sub

Thanks for reading this post and helping me out. What I'm trying to do is
to

[quoted text clipped - 28 lines]

The problem line is 60

 

[Allen Browne]

Not sure I get what's going on.

It's okay to open your form without any records, but after you've entered
one then that has to be records?

Or are you looking on a different form? Could you use DLookup() to see if
there are any records directly in a table/query? Help on DLookup():
http://allenbrowne.com/casu-07.html

Or are you looking to see if the one being entered is a duplicate of an
existing record, before you allow it to be saved?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for getting back to me.

What I'm trying to do is after filling in some info checking to see if the
recordset is empty. If it is then tell me No Records and closes the form

As the error message said, you can't close the form during its Current
event.

If you are seeking to do this when the form first opens, use its Open
event:
Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
Cancel = True
MsgBox "No records."
End If
End Sub

Thanks for reading this post and helping me out. What I'm trying to do
is
to

[quoted text clipped - 28 lines]

The problem line is 60

 

[Afrosheen via AccessMonster.com]

Thanks again for getting back to me.

What I'm doing is opening a form where a field called Supervisor is empty. I
then select a supervisor from a combo box to be entered in the supervisor
field when I click on the name. There may be 3 or 4 or more records to go
through. Once I'm done then of course there are no more records. This is
where I'm having the problem. I'm trying to display a message that there are
no more records in the query.

This is after I select the record:

Private Sub Combo28_AfterUpdate()
Requery
End Sub

This is what you gave me the first time and it works ok.
Private Sub Form_Open(Cancel As Integer)
20 If Me.RecordsetClone.RecordCount = 0 Then
50 Call MsgBox(" End of Records on file ", vbInformation, Application.
Name)
Cancel = True
60 DoCmd.Close
70 End If
End Sub

Not sure I get what's going on.

It's okay to open your form without any records, but after you've entered
one then that has to be records?

Or are you looking on a different form? Could you use DLookup() to see if
there are any records directly in a table/query? Help on DLookup():
http://allenbrowne.com/casu-07.html

Or are you looking to see if the one being entered is a duplicate of an
existing record, before you allow it to be saved?

Thanks Allen for getting back to me.

[quoted text clipped - 19 lines]

 

[Allen Browne]

Okay, so you have filtered the form to where the Supervisor field is empty,
i.e.:
[Supervisor] Is Null

If Combo28 is where you enter the supervisor, you could:
a) force the entry to be saved, and then
b) use DLookup() to see if there are any more records like that in the
table.

Private Sub Combo28_AfterUpdate()
Me.Dirty = False 'save this record.
If IsNull(DLookup("ID", "Table1", "[Supervisor] Is Null")) Then
MsgBox "No more blank supervisor records. Closing."
DoCmd.Close acForm, Me.Name
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again for getting back to me.

What I'm doing is opening a form where a field called Supervisor is empty.
I
then select a supervisor from a combo box to be entered in the supervisor
field when I click on the name. There may be 3 or 4 or more records to go
through. Once I'm done then of course there are no more records. This is
where I'm having the problem. I'm trying to display a message that there
are
no more records in the query.

This is after I select the record:

Private Sub Combo28_AfterUpdate()
Requery
End Sub

This is what you gave me the first time and it works ok.
Private Sub Form_Open(Cancel As Integer)
20 If Me.RecordsetClone.RecordCount = 0 Then
50 Call MsgBox(" End of Records on file ", vbInformation,
Application.
Name)
Cancel = True
60 DoCmd.Close
70 End If
End Sub

Not sure I get what's going on.

It's okay to open your form without any records, but after you've entered
one then that has to be records?

Or are you looking on a different form? Could you use DLookup() to see if
there are any records directly in a table/query? Help on DLookup():
http://allenbrowne.com/casu-07.html

Or are you looking to see if the one being entered is a duplicate of an
existing record, before you allow it to be saved?

Thanks Allen for getting back to me.

[quoted text clipped - 19 lines]

The problem line is 60

 

[Afrosheen via AccessMonster.com]

Good morning Allen. At least it's morning on the east coast of the US.

I think I understand what is happening, but you'll have to explain.

If IsNull(DLookup("ID", "Table1", "[Supervisor] Is Null")) Then.

I understand the "Table1" being my table and the"[Supervisor] is Null.
What is the ID for? Is it supposed to be for the Supervisor or the employee?

The supervisor has no Id. The employee does. I'm sorry, I just don't
understand.

Thanks for the help.

Okay, so you have filtered the form to where the Supervisor field is empty,
i.e.:
[Supervisor] Is Null

If Combo28 is where you enter the supervisor, you could:
a) force the entry to be saved, and then
b) use DLookup() to see if there are any more records like that in the
table.

Private Sub Combo28_AfterUpdate()
Me.Dirty = False 'save this record.
If IsNull(DLookup("ID", "Table1", "[Supervisor] Is Null")) Then
MsgBox "No more blank supervisor records. Closing."
DoCmd.Close acForm, Me.Name
End If
End Sub

Thanks again for getting back to me.

[quoted text clipped - 41 lines]

 

[Allen Browne]

The example uses ID as the name of the primary key field.

Use whatever it's actually called instead of that field, and whatever your
table is actually called instead of Table1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good morning Allen. At least it's morning on the east coast of the US.

I think I understand what is happening, but you'll have to explain.

If IsNull(DLookup("ID", "Table1", "[Supervisor] Is Null")) Then.

I understand the "Table1" being my table and the"[Supervisor] is Null.
What is the ID for? Is it supposed to be for the Supervisor or the
employee?

The supervisor has no Id. The employee does. I'm sorry, I just don't
understand.

Thanks for the help.

Okay, so you have filtered the form to where the Supervisor field is
empty,
i.e.:
[Supervisor] Is Null

If Combo28 is where you enter the supervisor, you could:
a) force the entry to be saved, and then
b) use DLookup() to see if there are any more records like that in the
table.

Private Sub Combo28_AfterUpdate()
Me.Dirty = False 'save this record.
If IsNull(DLookup("ID", "Table1", "[Supervisor] Is Null")) Then
MsgBox "No more blank supervisor records. Closing."
DoCmd.Close acForm, Me.Name
End If
End Sub

Thanks again for getting back to me.

[quoted text clipped - 41 lines]

The problem line is 60

 

[Afrosheen via AccessMonster.com]

Thanks again Allen for coming back to me.

This is what I have:

Private Sub Combo28_AfterUpdate()
10 On Error GoTo Combo28_AfterUpdate_Error

20
'Requery

Me.Dirty = False 'save this record.
If IsNull(DLookup("lname", "qryStaffnoSup", "[Supervisor] Is Null")) Then
MsgBox "No more blank supervisor records. Closing."
'DoCmd.Close acForm, Me.Name
Else
DoCmd.GoToRecord , , acNext
End If

30 On Error GoTo 0
40 Exit Sub

Combo28_AfterUpdate_Error:
50 Err.Description = Err.Description & " In Procedure " &
"Combo28_AfterUpdate of VBA Document Form_frmStaffnoSup"
60 Call LogError(Err.Number, Err.Description, "Combo28_AfterUpdate")

End Sub

It seems to find all the records that have no Supervisor and it seems to work.
Now I have to find a way where it will isolate just the person/supervisors
employee. That's the next part of the project.
For example:
Supervisor Fred may be deleted in one table so all of his employees with the
same supervisor will be set to null in another table. The problem comes up
when a new employee has no supervisor, then they will come up under the above
code. If I can build a query based on that supervisor then I can set them to
null and then continue with the above.

Thanks for your help Allen. I really appreciate it.

The example uses ID as the name of the primary key field.

Use whatever it's actually called instead of that field, and whatever your
table is actually called instead of Table1.

Good morning Allen. At least it's morning on the east coast of the US.

[quoted text clipped - 34 lines]

 

[Allen Browne]

In general, it's best to start a new thread when you have a new question.

The specifics will depend on your table structure, but basically you will
use criteria such as:
"[Supervisor] = 99"
as the filter for your form, or possibly as the Criteria for DLookup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again Allen for coming back to me.

This is what I have:

Private Sub Combo28_AfterUpdate()
10 On Error GoTo Combo28_AfterUpdate_Error

20
'Requery

Me.Dirty = False 'save this record.
If IsNull(DLookup("lname", "qryStaffnoSup", "[Supervisor] Is Null"))
Then
MsgBox "No more blank supervisor records. Closing."
'DoCmd.Close acForm, Me.Name
Else
DoCmd.GoToRecord , , acNext
End If

30 On Error GoTo 0
40 Exit Sub

Combo28_AfterUpdate_Error:
50 Err.Description = Err.Description & " In Procedure " &
"Combo28_AfterUpdate of VBA Document Form_frmStaffnoSup"
60 Call LogError(Err.Number, Err.Description,
"Combo28_AfterUpdate")

End Sub

It seems to find all the records that have no Supervisor and it seems to
work.
Now I have to find a way where it will isolate just the person/supervisors
employee. That's the next part of the project.
For example:
Supervisor Fred may be deleted in one table so all of his employees with
the
same supervisor will be set to null in another table. The problem comes up
when a new employee has no supervisor, then they will come up under the
above
code. If I can build a query based on that supervisor then I can set them
to
null and then continue with the above.

Thanks for your help Allen. I really appreciate it.

The example uses ID as the name of the primary key field.

Use whatever it's actually called instead of that field, and whatever your
table is actually called instead of Table1.

Good morning Allen. At least it's morning on the east coast of the US.

[quoted text clipped - 34 lines]

The problem line is 60

 

[Afrosheen via AccessMonster.com]

Sorry about that Allen. I'll repost sometime this week. As for your routine
it works..
Thanks again for the help.

In general, it's best to start a new thread when you have a new question.

The specifics will depend on your table structure, but basically you will
use criteria such as:
"[Supervisor] = 99"
as the filter for your form, or possibly as the Criteria for DLookup().

Thanks again Allen for coming back to me.

[quoted text clipped - 52 lines]