Public Sub Help

G

Guest

I have the following public sub in a button on my 'NewPartInputfrm' that
works great to delete a record as long as the button is clicked. I'm also
calling this same sub from another form, which first opens the
NewPartInputfrm to the last record, which was just created, then calls the
sub to delete that last record. It works fine except that after the record
is deleted, "#deleted" is in every field on the form and the after_update
event on one of my fields is running which won't allow duplicate values in
two of the fields so I get an error message.

Why would the code run differently when the button is clicked vs when it's
called from another form? Thanks.

The event on the other form that calls the sub is:

DoCmd.OpenForm "NewPartInputfrm", , , , acFormEdit
DoCmd.GoToRecord , , acLast
Forms![newpartinputfrm]![model#] = "1234"
Call Forms("NewPartInputfrm").DeleteRecordbtn_Click'

**********************************************

Public Sub DeleteRecordbtn_Click()

On Error GoTo Err_DeleteRecordbtn_Click

If Me.Model_ = "1234" Then GoTo StartHere


Dim strMsg As String, strInput As String
strMsg = "Are you sure you want to delete this record?"

If MsgBox(strMsg, vbYesNo, "Warning!") = _
vbNo Then
Cancel = True
Exit Sub

Else

StartHere:
'**************************************
If Len(Me.Part & "") = 0 _
Or Len(Me.NHL & "") = 0 _
Then


Me.Model_ = "Test"
Me.New_Part_NHL = "Test"
Me.New_Part_ = "10101"
Me.New_Part_Qty = "1000001"
Me.Dirty = False

End If




strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL
'strID = Me.IDctr

If Me.Dirty Then Me.Dirty = False


' 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

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]

'strID = ![ID]

' 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 & "'"
'Me.Recordset.FindFirst "ID = " & strID
End If
End With

End If
Exit_DeleteRecordbtn_Click:
Exit Sub

Err_DeleteRecordbtn_Click:
MsgBox Err.Description
Resume Exit_DeleteRecordbtn_Click

End Sub
 
D

Douglas J. Steele

Have you tried issuing a Me.Requery after running the sub?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex said:
I have the following public sub in a button on my 'NewPartInputfrm' that
works great to delete a record as long as the button is clicked. I'm also
calling this same sub from another form, which first opens the
NewPartInputfrm to the last record, which was just created, then calls the
sub to delete that last record. It works fine except that after the
record
is deleted, "#deleted" is in every field on the form and the after_update
event on one of my fields is running which won't allow duplicate values in
two of the fields so I get an error message.

Why would the code run differently when the button is clicked vs when it's
called from another form? Thanks.

The event on the other form that calls the sub is:

DoCmd.OpenForm "NewPartInputfrm", , , , acFormEdit
DoCmd.GoToRecord , , acLast
Forms![newpartinputfrm]![model#] = "1234"
Call Forms("NewPartInputfrm").DeleteRecordbtn_Click'

**********************************************

Public Sub DeleteRecordbtn_Click()

On Error GoTo Err_DeleteRecordbtn_Click

If Me.Model_ = "1234" Then GoTo StartHere


Dim strMsg As String, strInput As String
strMsg = "Are you sure you want to delete this record?"

If MsgBox(strMsg, vbYesNo, "Warning!") = _
vbNo Then
Cancel = True
Exit Sub

Else

StartHere:
'**************************************
If Len(Me.Part & "") = 0 _
Or Len(Me.NHL & "") = 0 _
Then


Me.Model_ = "Test"
Me.New_Part_NHL = "Test"
Me.New_Part_ = "10101"
Me.New_Part_Qty = "1000001"
Me.Dirty = False

End If




strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL
'strID = Me.IDctr

If Me.Dirty Then Me.Dirty = False


' 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

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]

'strID = ![ID]

' 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 & "'"
'Me.Recordset.FindFirst "ID = " & strID
End If
End With

End If
Exit_DeleteRecordbtn_Click:
Exit Sub

Err_DeleteRecordbtn_Click:
MsgBox Err.Description
Resume Exit_DeleteRecordbtn_Click

End Sub
Click to expand...
 
G

Guest

Doug - There's a me.refresh at the bottom of the DeleteRecordbtn_click sub.
Where should I add another one? If I add a me.requery to the form that's
calling the DeleteRecordbtn_click sub, won't that just refresh the other
form? Thanks.

Douglas J. Steele said:
Have you tried issuing a Me.Requery after running the sub?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex said:
I have the following public sub in a button on my 'NewPartInputfrm' that
works great to delete a record as long as the button is clicked. I'm also
calling this same sub from another form, which first opens the
NewPartInputfrm to the last record, which was just created, then calls the
sub to delete that last record. It works fine except that after the
record
is deleted, "#deleted" is in every field on the form and the after_update
event on one of my fields is running which won't allow duplicate values in
two of the fields so I get an error message.

Why would the code run differently when the button is clicked vs when it's
called from another form? Thanks.

The event on the other form that calls the sub is:

DoCmd.OpenForm "NewPartInputfrm", , , , acFormEdit
DoCmd.GoToRecord , , acLast
Forms![newpartinputfrm]![model#] = "1234"
Call Forms("NewPartInputfrm").DeleteRecordbtn_Click'

**********************************************

Public Sub DeleteRecordbtn_Click()

On Error GoTo Err_DeleteRecordbtn_Click

If Me.Model_ = "1234" Then GoTo StartHere


Dim strMsg As String, strInput As String
strMsg = "Are you sure you want to delete this record?"

If MsgBox(strMsg, vbYesNo, "Warning!") = _
vbNo Then
Cancel = True
Exit Sub

Else

StartHere:
'**************************************
If Len(Me.Part & "") = 0 _
Or Len(Me.NHL & "") = 0 _
Then


Me.Model_ = "Test"
Me.New_Part_NHL = "Test"
Me.New_Part_ = "10101"
Me.New_Part_Qty = "1000001"
Me.Dirty = False

End If




strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL
'strID = Me.IDctr

If Me.Dirty Then Me.Dirty = False


' 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

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]

'strID = ![ID]

' 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 & "'"
'Me.Recordset.FindFirst "ID = " & strID
End If
End With

End If
Exit_DeleteRecordbtn_Click:
Exit Sub

Err_DeleteRecordbtn_Click:
MsgBox Err.Description
Resume Exit_DeleteRecordbtn_Click

End Sub
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry: you're right. I missed it. I'm afraid nothing else comes to mind.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex said:
Doug - There's a me.refresh at the bottom of the DeleteRecordbtn_click
sub.
Where should I add another one? If I add a me.requery to the form that's
calling the DeleteRecordbtn_click sub, won't that just refresh the other
form? Thanks.

Douglas J. Steele said:
Have you tried issuing a Me.Requery after running the sub?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex said:
I have the following public sub in a button on my 'NewPartInputfrm' that
works great to delete a record as long as the button is clicked. I'm
also
calling this same sub from another form, which first opens the
NewPartInputfrm to the last record, which was just created, then calls
the
sub to delete that last record. It works fine except that after the
record
is deleted, "#deleted" is in every field on the form and the
after_update
event on one of my fields is running which won't allow duplicate values
in
two of the fields so I get an error message.

Why would the code run differently when the button is clicked vs when
it's
called from another form? Thanks.

The event on the other form that calls the sub is:

DoCmd.OpenForm "NewPartInputfrm", , , , acFormEdit
DoCmd.GoToRecord , , acLast
Forms![newpartinputfrm]![model#] = "1234"
Call Forms("NewPartInputfrm").DeleteRecordbtn_Click'

**********************************************

Public Sub DeleteRecordbtn_Click()

On Error GoTo Err_DeleteRecordbtn_Click

If Me.Model_ = "1234" Then GoTo StartHere


Dim strMsg As String, strInput As String
strMsg = "Are you sure you want to delete this record?"

If MsgBox(strMsg, vbYesNo, "Warning!") = _
vbNo Then
Cancel = True
Exit Sub

Else

StartHere:
'**************************************
If Len(Me.Part & "") = 0 _
Or Len(Me.NHL & "") = 0 _
Then


Me.Model_ = "Test"
Me.New_Part_NHL = "Test"
Me.New_Part_ = "10101"
Me.New_Part_Qty = "1000001"
Me.Dirty = False

End If




strModel = Me.Model
strPart = Me.Part
strNHL = Me.NHL
'strID = Me.IDctr

If Me.Dirty Then Me.Dirty = False


' 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

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]

'strID = ![ID]

' 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 & "'"
'Me.Recordset.FindFirst "ID = " & strID
End If
End With

End If
Exit_DeleteRecordbtn_Click:
Exit Sub

Err_DeleteRecordbtn_Click:
MsgBox Err.Description
Resume Exit_DeleteRecordbtn_Click

End Sub
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

Delete button dode 4
Creating Duplicate Record button help 1
Me.RecordsetClone 4
Delete button and refresh 8
Search for previous or next record 2
Delete and go to previous record 1
Using wild cards in VBA 7
Copy one field to new record 4

Top