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.showallre cords 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.Open Recordset(strSq l)

If rstBill.EOF Or rstBill.BOF Then

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

rstBill.Delete
rstBill.MoveNex t
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Clos e
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Ech o 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.Exec ute 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.f i> wrote in message
news:37******** *************** ***@posting.goo gle.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.showallre cords 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.Open Recordset(strSq l)

If rstBill.EOF Or rstBill.BOF Then

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

rstBill.Delete
rstBill.MoveNex t
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Clos e
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Ech o 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.go ogle.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.showallre cords 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.Open Recordset(strSq l)

If rstBill.EOF Or rstBill.BOF Then

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

rstBill.Delete
rstBill.MoveNex t
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Clos e
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Ech o 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 DeleteCurrentRe cord_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.Rec ordsetClone
.Bookmark = frmSomeForm.Boo kmark
.Delete
frmSomeForm.Req uery
End With
fDelCurrentRec = True
Exit_Section:
Exit Function

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

[Timppa]

dn*****@accurid e.com (David Neagle) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...

co***@jippii.fi (Timppa) wrote in message news:<37******* *************** ****@posting.go ogle.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.showallre cords 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.Open Recordset(strSq l)

If rstBill.EOF Or rstBill.BOF Then

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

rstBill.Delete
rstBill.MoveNex t
Loop
MsgBox ("Deleted !")
rstBill.Close
dbsCurrent.Clos e
Set dbsCurrent = Nothing
Set rstBill = Nothing
Application.Ech o 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 DeleteCurrentRe cord_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.Rec ordsetClone
.Bookmark = frmSomeForm.Boo kmark
.Delete
frmSomeForm.Req uery
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.Re query " 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