Delete button dode

G

Guest

I have delete button on my form that is working well except that is seems to
be resorting the records. For example when I delete record#45 it should go
to record#44. It opens the correct record with model, part and NHL, but the
record is no longer record#44. Any ideas?

Dim strSQL As String
Dim strModel As String
Dim strPart As String
Dim strNHL As String

strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL

strSQL = _
"DELETE * FROM AllNewParts WHERE " & _
"[Model#] = '" & _
[Forms]![NewPartInputfrm]![Model] & _
"' And [Part#] = '" & _
[Forms]![NewPartInputfrm]![Part] & _
"' And [NHL] = '" & _
[Forms]![NewPartInputfrm]![NHL] & "'"

CurrentDb.Execute (strSQL), dbFailOnError

With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
' Locate record to come back to.
.Bookmark = Me.Bookmark
If Me.CurrentRecord = 1 Then
.MoveNext
Else
.MovePrevious
End If

' Save this record's key info.
strModel = ![Model#]
strPart = ![Part#]
strNHL = ![NHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End If
End With
 
G

Guest

There really is no such thing as a record number in Access. The number you
see in the record selector box of a form is the "relative" record number or
the AbsolutePosition in the current recordset.

Any time you add, delete or reorder a recordset, the numbers all change.
You should never depend on these numbers for anything
 
G

Guest

OK - but why would the order change after deleting?

Klatuu said:
There really is no such thing as a record number in Access. The number you
see in the record selector box of a form is the "relative" record number or
the AbsolutePosition in the current recordset.

Any time you add, delete or reorder a recordset, the numbers all change.
You should never depend on these numbers for anything

Alex said:
I have delete button on my form that is working well except that is seems to
be resorting the records. For example when I delete record#45 it should go
to record#44. It opens the correct record with model, part and NHL, but the
record is no longer record#44. Any ideas?

Dim strSQL As String
Dim strModel As String
Dim strPart As String
Dim strNHL As String

strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL

strSQL = _
"DELETE * FROM AllNewParts WHERE " & _
"[Model#] = '" & _
[Forms]![NewPartInputfrm]![Model] & _
"' And [Part#] = '" & _
[Forms]![NewPartInputfrm]![Part] & _
"' And [NHL] = '" & _
[Forms]![NewPartInputfrm]![NHL] & "'"

CurrentDb.Execute (strSQL), dbFailOnError

With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
' Locate record to come back to.
.Bookmark = Me.Bookmark
If Me.CurrentRecord = 1 Then
.MoveNext
Else
.MovePrevious
End If

' Save this record's key info.
strModel = ![Model#]
strPart = ![Part#]
strNHL = ![NHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End If
End With
Click to expand...
Click to expand...
 
G

Guest

I doubt the order has actually changed. If you have records showing

40, 41, 42, 43, 44, 45, 46

and you delete the record numbered 43, the order does not change (unless you
have modified your form orderby or the recordset's order), 40 - 42 will be
the same, but
what was 44 will now be 43, 45 will be 44, 46 will be 45

Alex said:
OK - but why would the order change after deleting?

Klatuu said:
There really is no such thing as a record number in Access. The number you
see in the record selector box of a form is the "relative" record number or
the AbsolutePosition in the current recordset.

Any time you add, delete or reorder a recordset, the numbers all change.
You should never depend on these numbers for anything

Alex said:
I have delete button on my form that is working well except that is seems to
be resorting the records. For example when I delete record#45 it should go
to record#44. It opens the correct record with model, part and NHL, but the
record is no longer record#44. Any ideas?

Dim strSQL As String
Dim strModel As String
Dim strPart As String
Dim strNHL As String

strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL

strSQL = _
"DELETE * FROM AllNewParts WHERE " & _
"[Model#] = '" & _
[Forms]![NewPartInputfrm]![Model] & _
"' And [Part#] = '" & _
[Forms]![NewPartInputfrm]![Part] & _
"' And [NHL] = '" & _
[Forms]![NewPartInputfrm]![NHL] & "'"

CurrentDb.Execute (strSQL), dbFailOnError

With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
' Locate record to come back to.
.Bookmark = Me.Bookmark
If Me.CurrentRecord = 1 Then
.MoveNext
Else
.MovePrevious
End If

' Save this record's key info.
strModel = ![Model#]
strPart = ![Part#]
strNHL = ![NHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End If
End With
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The order was changing. I set my Orderby property in the formopen event and
that has seemed to help. Thanks.

Klatuu said:
I doubt the order has actually changed. If you have records showing

40, 41, 42, 43, 44, 45, 46

and you delete the record numbered 43, the order does not change (unless you
have modified your form orderby or the recordset's order), 40 - 42 will be
the same, but
what was 44 will now be 43, 45 will be 44, 46 will be 45

Alex said:
OK - but why would the order change after deleting?

Klatuu said:
There really is no such thing as a record number in Access. The number you
see in the record selector box of a form is the "relative" record number or
the AbsolutePosition in the current recordset.

Any time you add, delete or reorder a recordset, the numbers all change.
You should never depend on these numbers for anything

:

I have delete button on my form that is working well except that is seems to
be resorting the records. For example when I delete record#45 it should go
to record#44. It opens the correct record with model, part and NHL, but the
record is no longer record#44. Any ideas?

Dim strSQL As String
Dim strModel As String
Dim strPart As String
Dim strNHL As String

strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL

strSQL = _
"DELETE * FROM AllNewParts WHERE " & _
"[Model#] = '" & _
[Forms]![NewPartInputfrm]![Model] & _
"' And [Part#] = '" & _
[Forms]![NewPartInputfrm]![Part] & _
"' And [NHL] = '" & _
[Forms]![NewPartInputfrm]![NHL] & "'"

CurrentDb.Execute (strSQL), dbFailOnError

With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
' Locate record to come back to.
.Bookmark = Me.Bookmark
If Me.CurrentRecord = 1 Then
.MoveNext
Else
.MovePrevious
End If

' Save this record's key info.
strModel = ![Model#]
strPart = ![Part#]
strNHL = ![NHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End If
End With
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Public Sub Help 3
Delete button and refresh 8
Search for previous or next record 2
Delete and go to previous record 1
Me.RecordsetClone 4
Do Until Loop help 1
Creating Duplicate Record button help 1
sql delete syntax 1

Top