Deleting in vba code

[Timppa]

My problem is how could I get previos record in the form after I had
deleted the current record.
What should I write after code below ? docmd.showallrecords or what ?
If I do so I'll get error message: "Index or primary key cannot
contain a Null Value"

I'am trying to delete current record with following code in vba:

Private Sub Delete_record (glngLasno as long)

Set dbsCurrent = CurrentDb()
strSql = "SELECT * FROM bill where [company]= '" & gstrComp & "'
and lasno = " & glngLasno & ";"
Set rstBill = dbsCurrent.OpenRecordset(strSql)

If rstBill.EOF Or rstBill.BOF Then

Set dbsCurrent = Nothing
Set rstBill = Nothing
Exit Sub
End If
Application.Echo False
Do Until rstBill.EOF

rstBill.Delete
rstBill.MoveNext
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Close
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Echo True
Exit Sub
err_sec:
Set dbsCurrent = Nothing
Set rstBill = Nothing

MsgBox Err.Description
Exit Sub
End Sub

Colpo

[Wayne Morgan]

Try
strSQL = DELETE * FROM bill where [company]= '" & gstrComp & "' and [lasno]
= " & glngLasno & ";"
dbsCurrent.Execute strSQL, dbFailOnError

This should be much faster than stepping through a recordset one record at a
time.
--
Wayne Morgan
Microsoft Access MVP
"Timppa" <co***@jippii.fi> wrote in message
news:37**************************@posting.google.c om...

My problem is how could I get previos record in the form after I had
deleted the current record.
What should I write after code below ? docmd.showallrecords or what ?
If I do so I'll get error message: "Index or primary key cannot
contain a Null Value"

I'am trying to delete current record with following code in vba:

Private Sub Delete_record (glngLasno as long)

Set dbsCurrent = CurrentDb()
strSql = "SELECT * FROM bill where [company]= '" & gstrComp & "'
and lasno = " & glngLasno & ";"
Set rstBill = dbsCurrent.OpenRecordset(strSql)

If rstBill.EOF Or rstBill.BOF Then

Set dbsCurrent = Nothing
Set rstBill = Nothing
Exit Sub
End If
Application.Echo False
Do Until rstBill.EOF

rstBill.Delete
rstBill.MoveNext
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Close
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Echo True
Exit Sub
err_sec:
Set dbsCurrent = Nothing
Set rstBill = Nothing

MsgBox Err.Description
Exit Sub
End Sub

Colpo

[David Neagle]

co***@jippii.fi (Timppa) wrote in message news:<37**************************@posting.google. com>...

My problem is how could I get previos record in the form after I had
deleted the current record.
What should I write after code below ? docmd.showallrecords or what ?
If I do so I'll get error message: "Index or primary key cannot
contain a Null Value"

I'am trying to delete current record with following code in vba:

Private Sub Delete_record (glngLasno as long)

Set dbsCurrent = CurrentDb()
strSql = "SELECT * FROM bill where [company]= '" & gstrComp & "'
and lasno = " & glngLasno & ";"
Set rstBill = dbsCurrent.OpenRecordset(strSql)

If rstBill.EOF Or rstBill.BOF Then

Set dbsCurrent = Nothing
Set rstBill = Nothing
Exit Sub
End If
Application.Echo False
Do Until rstBill.EOF

rstBill.Delete
rstBill.MoveNext
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Close
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Echo True
Exit Sub
err_sec:
Set dbsCurrent = Nothing
Set rstBill = Nothing

MsgBox Err.Description
Exit Sub
End Sub

Colpo

Don't know if this will help, but I use the following code found on
the Dev Ashish web site.

Private Sub DeleteCurrentRecord_Click()
If Not (fDelCurrentRec(Me)) Then
MsgBox "An Error occurred!"
End If
End Sub
The actual function is:

'************** Code Start *************
Function fDelCurrentRec(ByRef frmSomeForm As Form) As Boolean
On Error GoTo Err_Section

With frmSomeForm
If .NewRecord Then
.Undo
fDelCurrentRec = True
GoTo Exit_Section
End If
End With

With frmSomeForm.RecordsetClone
.Bookmark = frmSomeForm.Bookmark
.Delete
frmSomeForm.Requery
End With
fDelCurrentRec = True
Exit_Section:
Exit Function

Err_Section:
fDelCurrentRec = False
Resume Exit_Section
End Function
'************** Code End *************

[Timppa]

dn*****@accuride.com (David Neagle) wrote in message news:<bf**************************@posting.google. com>...

co***@jippii.fi (Timppa) wrote in message news:<37**************************@posting.google. com>...

My problem is how could I get previos record in the form after I had
deleted the current record.
What should I write after code below ? docmd.showallrecords or what ?
If I do so I'll get error message: "Index or primary key cannot
contain a Null Value"

I'am trying to delete current record with following code in vba:

Private Sub Delete_record (glngLasno as long)

Set dbsCurrent = CurrentDb()
strSql = "SELECT * FROM bill where [company]= '" & gstrComp & "'
and lasno = " & glngLasno & ";"
Set rstBill = dbsCurrent.OpenRecordset(strSql)

If rstBill.EOF Or rstBill.BOF Then

Set dbsCurrent = Nothing
Set rstBill = Nothing
Exit Sub
End If
Application.Echo False
Do Until rstBill.EOF

rstBill.Delete
rstBill.MoveNext
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Close
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Echo True
Exit Sub
err_sec:
Set dbsCurrent = Nothing
Set rstBill = Nothing

MsgBox Err.Description
Exit Sub
End Sub

Colpo

Don't know if this will help, but I use the following code found on
the Dev Ashish web site.

Private Sub DeleteCurrentRecord_Click()
If Not (fDelCurrentRec(Me)) Then
MsgBox "An Error occurred!"
End If
End Sub
The actual function is:

'************** Code Start *************
Function fDelCurrentRec(ByRef frmSomeForm As Form) As Boolean
On Error GoTo Err_Section

With frmSomeForm
If .NewRecord Then
.Undo
fDelCurrentRec = True
GoTo Exit_Section
End If
End With

With frmSomeForm.RecordsetClone
.Bookmark = frmSomeForm.Bookmark
.Delete
frmSomeForm.Requery
End With
fDelCurrentRec = True
Exit_Section:
Exit Function

Err_Section:
fDelCurrentRec = False
Resume Exit_Section
End Function
'************** Code End *************

Thanks for your advice David, but there is still problems with
"frmSomeForm.Requery " There comes error with it. Otherwise it works
fine.

There is such functionality that I choose a bill from Form 1 (in list
box) and
after that the control moves to the Form 2. In the Form 2 I'll try to
delete the current record. In the Form 2 I'll like to see previos
record after deletion. That's what doesn't work now.
I hope you undestand what I mean.

Colpo