Delete from Record Set

[OhBother30]

I have a form with numerous records. Each record has an autonumber field as the key. When adding a new record I want to look at the previous record. If a particular field is null I do not want a new record to be added. I was trying the following code. I was able to get the message, but I don't know how to delete the record that the user was trying to add.
I had the code on "BeforeUpdate" for my autonumber and it didn't do anything. I changed it to "BeforeUpdate" on my second field. The problem is that an autonumber has already been asigned.

Any help would be appreciated.

Dim dbs As Database
Dim rst As Recordset
Dim CheckID As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qryCheckoutRecord&quot

With rst
.MoveLast

End With
rst.Close
If IsNull(Me![InDate]) = True Then
MsgBox "Warning! This file is already checked out.", vbOKOnly, "Check Out Status"
Else
Exit Sub
End If
End Sub

 

[vb6novice]

OhBother30,

How are you adding the new record? If you are using the navigation buttons on the form, then you will create the new record, no matter what, and the form will move to display the new record. If you have your own button, then the record will only be added when you give the Addnew command in the code.

Your code opens the recordset, moves to the last record and closes. The end result of that is nothing. It does not change the current record on the form. As a result, the Me![InDate] (assuming this is a control onyour form) value will be whatever it was before you ran your code.


If you use your the navigation button, then in your code, change everything after the .MoveLast to the following:

.MovePrevious
If IsNull(rst![MyField]) = True then
.MoveNext
.Delete
Msgbox "Warning..."
end if
rst.Close
dbs.Close
End Sub

If you have your own button to add the new record, use the following:

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qryCheckoutRecord&quot

rst.MoveLast

If IsNull(rst![InDate]) = True Then
MsgBox "Warning! This file is already checked out.",vbOKOnly, "Check Out Status"
Else
rst.Addnew
End If
rst.Close
dbs.Close
End Sub

 

[OhBother30]

Thanks for your response!
I am adding records with a continuous form.
I tried your navigation button code, but I got an error for "With with no End With" and "No current Record".

I can always add a delete button on the form to delete the record, but I would still like a warning that the file can not be checked out. I'm trying to eliminate as many user errors as I can. This one just has me a little stumped.

Also, is there a statement I can use to avoid getting an error when there are no records in my record set and I am asking to look at the last or previous record?

Thanks again for your response. I would appreciate and other suggestions you might have.

 

[vb6novice]

OhBother30,

Include the complete procedure in your response and I'll trry to find where the "With..." error is coming from.

There is a very easy way to check if there are records to prevent the "No current record" error. Most eople on this form use an If.. Then loop to check if there are any records as follows.

Set rst = dbs.OpenRecordset("tblMyTable&quot
If rst.RecordCount > 0 then
Do until rst.eof
do something
Loop
end if

When you open the recordset and there are no records, then .EOF is true. If you want to see if its ok to movelast as soon as you open the recordset, check EOF first

Set rst = dbs.OpenRecordset("tblMyTable&quot
If rst.EOF = False then
rst.MoveLast
End if

 

[JeremyNYC]

Actually, this is relatively easy to do. You'll want to put your code in the beforeInsert event of the form.

This should do the trick:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = Me.RecordsetClone
rst.MoveLast
If IsNull(rst!AccountName) Then
Call MsgBox("Can't add record.", _
vbInformation, "Data Conflict&quot
Cancel = True
End If
End Sub

It's important to use the me.recordsetclone here, because otherwise you won't be able to count on the records being in the same order on the form and in the recordset. Records in a table are not stored in any particular order.

And, of course, you'll want to use your own field name, and probably be a little more specific in the messagebox.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[OhBother30]

Jeremy
That worked perfectly!
Thanks a bunch!