Delete button dode

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...
 
Back
Top