Discussion Locked

Access 2000 Recordset problem

0 helpful
20 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a subform which is used to keep track of open payments. When the payment status changes, the operator chooses the new status from a dropdown box. A Onchange procedure runs and can remove that record from the table if it closes. This sometimes leaves no records in the table.

The problem is that when closing the form, a "No Current Record" dialog box appears. The Microsoft fix of using Movefirst or Movelast won't work because there are NO records in the recordset currently. SetWarnings does not seem to suppress the dialog box either. It confuses the users and I would like to get rid of the message. Any simple solutions?

20 replies

by mackem

You could try to disable the box using an event procedure on close for the form.
Something like:
If is null then
enabled = False
Else enabled = True
Remember to enabele the box when the form opens.
Hope thisworks

by donq

run Requery on whatever produces your operating recordset within the procedure that deletes the last record - on the next line just before the procedure ends so the recordset will ALWAYS contain at least one empty record.

.Requery

Good Luck, Don Quigg

by donq

Kevin, After you requery enter the line
DoCmd.GoToRecord , , acNewRec

and after that enter a line

.SetFocus

You WILL get a clean record ready for user input!

by it_cowboy

in the on close event for the form, run a requery then check for EOF. Im not sure what procedures you may be running on the close event, but there must be a line that causes the error. if EOF is true, do not process that line.

by fofa

usually in a case like this I set a table field (Yes/No) and when the delete I set this to true (or false) and display DELETED on the form somewhere. When the form is closed I run a query which actually Deletes the rows. This also gives the advantage of allowing the user to UNDELETE the record before they close the form.

by sankulpune

Hello Kevin,

My limited experience with sub-forms suggests that most of the sub-form problems are related to relationship between the tables having one to many relationship and other data related properties like 'AllowEdits'.

The best way to create trouble free sub-form is to create a query based on multiple tables having 'one to many' relationship and include primary key and not the foreign key in the query. When you create an auto-form based on this query, the form, created with subform, works without any of the problems mentioned in various questions.

Please study carefully the topic 'How Microsoft Access links main forms and subforms' in the on-line help of Access 2000. Make sure especially that you satisfy both the conditions specified therein

If it does not help you, I might be able help out if I can have look at the tables, their relation ship and the form/sub-form design.

Your early feedback will be highly appreciated.

SanganakSakha,
A Friend In Need

by shmaltz

I'm not sure in which event you should place the following code but I would put it in all the events that the form goes thru when closing (dont put it in an event that the form is raising some times when its not closing like lost focus).
on error resume next
I'm not sure if its going to work but I think it is going to work.
If you want you can do instead of the above that: on error go to frm_err: and at frm_err put code to check for the error number to see if its that one so you can ignore it.
Example:
frm_err:
if err.number = x then
resume next
eles
msgbox "The error number is " & err.number & vbcrlf & err.description
exit
end if

If it dosnt have an error number then assign it your own number that you can check for it.Im not sure how exactly it works to raise your own error numbers but I think you can do it.

by rsv

HI,
Before moving the record in the recordset (movefirst or moveprevious or movenext or movelast) check the recordset's current pointer like recordset.EOF and recordset.BOF
if both returns true, it means there is no records in the table, so skip the record movement, elseif it returns false then use the movefirst or moveprevious or movenext or movelast as per required.

Hope this solves your problem !!!!

by limbo

Programmer paradigm #286 -

"If it can't be fixed - don't fix it".

First find the failure point at the procedural level. With said procedure, set in place an error handling routine...

i.e.

On Error Resume Next

OK that's the brute force method, a more graceful solution would be...

Private Sub Onchange()
On Error Goto ErrorBreak
'method...
'method...
'method...
Exit Sub
ErrorBreak:
' Ignore the error number here, i.e....
If err.number = 75 then
Resume Next
Else
msgboxErr.Number
End if
End Sub

You see, your failure point is occurring... but it is not critical. Just make it go away.

ADO will be produce 'spurious' errors like this even with the tightest of implementations.

It's probably an event sequencing problem anyway. You could find the exact nature of the failure point, but this is likely to be more work than its worth.

by limbo

RATE ME!!!!!!

How do I know if i'm a failure or not if I'm not rated??????

This discussion is closed to new replies.