Delete record from a different form

  • Thread starter Thread starter Nicholas
  • Start date Start date
N

Nicholas

When I create a new customer(Record) in my customer
form 'frmCustomers', I have some code (1) which checks
the 'FirstName' and 'LastName' field in my
table 'tblCustomers'. If there is another record with the
same data in both fields, it opens a pop-up
form 'frmCheckName' which is based on a query which shows
all of the customers with the same name (plus some other
details such as address). If the customer is not in the
list (ie the name is the same but the address is
different) the database user has the choice of going back
to the form, 'frmCustomers' to the new record they were
creating or going to the existing customer that was
previously created using this code (2). The actual
problem is that if the user decides to go to one of the
old customers with the same name the new record that they
were creating is still there. So I need some code to
delete the new customer, but only if the user decides to
go to an old customer from the from 'frmCheckCustomer'
If anyone can help thanks in advance

I think what I need is some code to delete the current
record in the form 'frmCustomers' that is attached to the
on click event of the 'SelectNameButton' on the pop-up
form 'frmCheckCustomer', but I'm not sure how to do it

(1) Here is the code in the 'frmCustmers' form which
checks the table for duplicate customers

Private Sub LastName_AfterUpdate()
If DCount("*", "tblCustomers", "LastName='" & [LastName]
& _
"' And [FirstName]='" & [FirstName] & "'") > 0
Then
MsgBox "NOTE: THERE IS ALREADY A PERSON WITH THE
SAME FIRST AND LAST NAMES"
DoCmd.OpenForm "frmCheckName"
End If
End Sub

(2) Here is the code to take the user back to
the 'frmCustomers' form to the record they selected from
the query list using the customer ID

Private Sub SelectNameButton_Click()
On Error GoTo Err_SelectNameButton_Click

DoCmd.OpenForm "frmCustomers", , , "[CustomerID] =
Forms![frmCheckName]![subCheckName].form![CustomerID]"
DoCmd.Close acForm, "frmCheckName", acSaveYes


Exit_SelectNameButton_Click:
Exit Sub

Err_SelectNameButton_Click:
MsgBox Err.Description
Resume Exit_SelectNameButton_Click

End Sub
 
The best approach would be to cancel the addition of the duplicate record
before it gets into the table.

To do that, cancel the BeforeUpdate event of the *form*, and undo the
record. Adapt to suit, but the basic idea is this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If DCount("*", "tblCustomers", "LastName='" & [LastName] & _
"' And [FirstName]='" & [FirstName] & "'") > 0 Then

strMsg = "NOTE: THERE IS ALREADY A PERSON WITH THE SAME FIRST AND
LAST NAMES" & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2) <> vbYes Then
Cancel = True
Me.Undo
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nicholas said:
When I create a new customer(Record) in my customer
form 'frmCustomers', I have some code (1) which checks
the 'FirstName' and 'LastName' field in my
table 'tblCustomers'. If there is another record with the
same data in both fields, it opens a pop-up
form 'frmCheckName' which is based on a query which shows
all of the customers with the same name (plus some other
details such as address). If the customer is not in the
list (ie the name is the same but the address is
different) the database user has the choice of going back
to the form, 'frmCustomers' to the new record they were
creating or going to the existing customer that was
previously created using this code (2). The actual
problem is that if the user decides to go to one of the
old customers with the same name the new record that they
were creating is still there. So I need some code to
delete the new customer, but only if the user decides to
go to an old customer from the from 'frmCheckCustomer'
If anyone can help thanks in advance

I think what I need is some code to delete the current
record in the form 'frmCustomers' that is attached to the
on click event of the 'SelectNameButton' on the pop-up
form 'frmCheckCustomer', but I'm not sure how to do it

(1) Here is the code in the 'frmCustmers' form which
checks the table for duplicate customers

Private Sub LastName_AfterUpdate()
If DCount("*", "tblCustomers", "LastName='" & [LastName]
& _
"' And [FirstName]='" & [FirstName] & "'") > 0
Then
MsgBox "NOTE: THERE IS ALREADY A PERSON WITH THE
SAME FIRST AND LAST NAMES"
DoCmd.OpenForm "frmCheckName"
End If
End Sub

(2) Here is the code to take the user back to
the 'frmCustomers' form to the record they selected from
the query list using the customer ID

Private Sub SelectNameButton_Click()
On Error GoTo Err_SelectNameButton_Click

DoCmd.OpenForm "frmCustomers", , , "[CustomerID] =
Forms![frmCheckName]![subCheckName].form![CustomerID]"
DoCmd.Close acForm, "frmCheckName", acSaveYes


Exit_SelectNameButton_Click:
Exit Sub

Err_SelectNameButton_Click:
MsgBox Err.Description
Resume Exit_SelectNameButton_Click

End Sub
Click to expand...
 
Back
Top