Delete button and refresh

G

Guest

I have the following code in a button. I have to use a delete button because
my query that populates my form has five table with left joins and if I do a
'normal' delete record, records from all my joined tables are deleted and I
only want the record from the AllNewParts table deleted. My problem is that
if there is more than one record when the button is clicked, I need the
record to delete and the form to refresh and open to the previous record or
the next record if the record being deleted is the first record. Any ideas
how I can accomplish this?

Private Sub Command121_Click()

Dim strSQL As String

If Me.Part = "" And Me.NHL = "" Then
Me.Undo
Else

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

CurrentDb.Execute (strSQL), dbFailOnError

End If
With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
 
K

krocheck

Just looking at your SQL I do see one problem. You do not need the '*'
in a DELETE FROM query. Try:

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

Keith
 
G

Guest

Thanks, but that didn't solve my problem.

krocheck said:
Just looking at your SQL I do see one problem. You do not need the '*'
in a DELETE FROM query. Try:

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

Keith
I have the following code in a button. I have to use a delete button because
my query that populates my form has five table with left joins and if I do a
'normal' delete record, records from all my joined tables are deleted and I
only want the record from the AllNewParts table deleted. My problem is that
if there is more than one record when the button is clicked, I need the
record to delete and the form to refresh and open to the previous record or
the next record if the record being deleted is the first record. Any ideas
how I can accomplish this?

Private Sub Command121_Click()

Dim strSQL As String

If Me.Part = "" And Me.NHL = "" Then
Me.Undo
Else

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

CurrentDb.Execute (strSQL), dbFailOnError

End If
With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Alex said:
I have the following code in a button. I have to use a delete button
because my query that populates my form has five table with left
joins and if I do a 'normal' delete record, records from all my
joined tables are deleted and I only want the record from the
AllNewParts table deleted. My problem is that if there is more than
one record when the button is clicked, I need the record to delete
and the form to refresh and open to the previous record or the next
record if the record being deleted is the first record. Any ideas
how I can accomplish this?

Private Sub Command121_Click()

Dim strSQL As String

If Me.Part = "" And Me.NHL = "" Then
Me.Undo
Else

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

CurrentDb.Execute (strSQL), dbFailOnError

End If
With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
Click to expand...

Is your delete button deleting the current record? In order to move to
the next or previous record afer requerying the form, you're going to
need to be able to identify the record by the values of one or more key
fields. What field or fields uniquely identify the records on the form?

In principle, you would do something like the following (but I'm just
guessing at your field names):

'----- start of example code -----
Private Sub Command121_Click()

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

If Len(Me.Part & "") = 0 _
And Len(Me.NHL & "") = 0 _
Then
Me.Undo
Else
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

End If
'----- end of example code -----
 
G

Guest

Thanks Dirk. It works until Me.Requery. Then I get a runtime error "record
is deleted" which, when I click debug, is stopping at Me.Requery.

Dirk Goldgar said:
Alex said:
I have the following code in a button. I have to use a delete button
because my query that populates my form has five table with left
joins and if I do a 'normal' delete record, records from all my
joined tables are deleted and I only want the record from the
AllNewParts table deleted. My problem is that if there is more than
one record when the button is clicked, I need the record to delete
and the form to refresh and open to the previous record or the next
record if the record being deleted is the first record. Any ideas
how I can accomplish this?

Private Sub Command121_Click()

Dim strSQL As String

If Me.Part = "" And Me.NHL = "" Then
Me.Undo
Else

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

CurrentDb.Execute (strSQL), dbFailOnError

End If
With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
Click to expand...

Is your delete button deleting the current record? In order to move to
the next or previous record afer requerying the form, you're going to
need to be able to identify the record by the values of one or more key
fields. What field or fields uniquely identify the records on the form?

In principle, you would do something like the following (but I'm just
guessing at your field names):

'----- start of example code -----
Private Sub Command121_Click()

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

If Len(Me.Part & "") = 0 _
And Len(Me.NHL & "") = 0 _
Then
Me.Undo
Else
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

End If
'----- end of example code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
G

Guest

I remarked the Me.Requery, which opened the form to the previous record,
which is great except that it filters the form to only include the records
that were prior to the one I deleted. For example, I deleted the fifth
record, then after the record was deleted only the first 4 records were
included in the filter.

Dirk Goldgar said:
Alex said:
I have the following code in a button. I have to use a delete button
because my query that populates my form has five table with left
joins and if I do a 'normal' delete record, records from all my
joined tables are deleted and I only want the record from the
AllNewParts table deleted. My problem is that if there is more than
one record when the button is clicked, I need the record to delete
and the form to refresh and open to the previous record or the next
record if the record being deleted is the first record. Any ideas
how I can accomplish this?

Private Sub Command121_Click()

Dim strSQL As String

If Me.Part = "" And Me.NHL = "" Then
Me.Undo
Else

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

CurrentDb.Execute (strSQL), dbFailOnError

End If
With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else
Click to expand...

Is your delete button deleting the current record? In order to move to
the next or previous record afer requerying the form, you're going to
need to be able to identify the record by the values of one or more key
fields. What field or fields uniquely identify the records on the form?

In principle, you would do something like the following (but I'm just
guessing at your field names):

'----- start of example code -----
Private Sub Command121_Click()

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

If Len(Me.Part & "") = 0 _
And Len(Me.NHL & "") = 0 _
Then
Me.Undo
Else
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

End If
'----- end of example code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Alex said:
Thanks Dirk. It works until Me.Requery. Then I get a runtime error
"record is deleted" which, when I click debug, is stopping at
Me.Requery.
Click to expand...

Could it be that the current record, the one that you are deleting, is
dirty? That is, has its data been modified but not saved? If that's
the case, then I can understand the error message, since your form is
holding a record that needs to be saved before the form can be
requeried, but that record has already been deleted by an external
mechanism: your code.

Does it help to modify the code slightly by undoing any changes to the
record that you're about to delete? Something like this code snippet:

If Len(Me.Part & "") = 0 _
And Len(Me.NHL & "") = 0 _
Then
Me.Undo
Else
strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL

If Me.Dirty Then Me.Undo

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

CurrentDb.Execute (strSQL), dbFailOnError
 
D

Dirk Goldgar

Alex said:
I remarked the Me.Requery, which opened the form to the previous
record, which is great except that it filters the form to only
include the records that were prior to the one I deleted. For
example, I deleted the fifth record, then after the record was
deleted only the first 4 records were included in the filter.
Click to expand...

I'm not following you about the filtering, but I think you need the
Requery, or the record you deleted will still be in the form's
recordset, but with #DELETED in its fields. At least, that's what I
expect.
 
G

Guest

I can't thank you enough Dirk. I must have some code in On Current that's
updating the records. As soon as changed the code like you suggested the
record deletes perfectly and all records are still showing except the one
deleted. Thanks again.
 

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

Delete button dode 4
Delete and go to previous record 1
Search for previous or next record 2
Public Sub Help 3
Creating Duplicate Record button help 1
Refresh record while open in a form 1
Help with Delete Button 6
Run Code 3

Top