Delete record in another table

A

AHopper

From a "Delete" command button on a
form "USPSCoilerEditOneTwoPackage" I am trying to delete
a related record in table "tblRegisteredAndUniqueLabels"
before record selected is deleted. There are two
different ways I want to look for the correct record. The
first is if the is a valid number in the text box based
on a field of the same name "RegisteredLabelOneTwoPack"
and the second is if there is
no "RegisteredLabelOneTwoPack". Below is the code I have
been trying. I have been able to add a new record using
similar code but I have not been successful at deleting.


Thanks you in advance for your help

Allan

Dim CurDb As Database
Dim RegisteredUnique As Recordset
'Look for related record with registered label'
If Len(Me.RegisteredLabelOneTwoPack) = 20 And _
IsNumeric(Me.RegisteredLabelOneTwoPack) = True Then
' the text string is 20 characters long and
' it contains only numbers
Dim RegNo As String
RegNo = Nz(Me.RegisteredLabelOneTwoPack)
Me.RegisteredLabel = CStr(Left("" & RegNo & "", 19))
Me.CheckDigit = CStr(Right("" & RegNo & "", 1))
Set CurDb = CurrentDb
Set RegisteredUnique = CurDb.OpenRecordset
("tblRegisteredAndUniqueLabels")
With RegisteredUnique
!JobNumber = [Forms]!
[USPSCoilerEditOneTwoPackage]![JobNumber]
!UsedWithJob = [Forms]!
[USPSCoilerEditOneTwoPackage]![UsedWithJob]
!RegisteredLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![RegisteredLabel]
!CheckDigit = [Forms]!
[USPSCoilerEditOneTwoPackage]![CheckDigit]
!UniqueLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![UniqueLabel]
!CartonWeight = [Forms]!
[USPSCoilerEditOneTwoPackage]![CartonWeightOneTwoPack]
!SkidNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![Skid]
!PartialCarton = [Form]!
[USPSCoilerEditOneTwoPackage]![PartialCarton]
!EmployeeNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![EmployeeNumber]
!Shift = [Form]![USPSCoilerEditOneTwoPackage]!
[Shift]
.Delete
End With
RegisteredUnique.Close
Set RegisteredUnique = Nothing
Me.RegisteredLabelOneTwoPack = Null
Me.CheckDigit = Null
End If

'Look for record no registered label is available to use.'
If IsNull(Me.RegisteredLabelOneTwoPack) Or Nz
(Me.RegisteredLabelOneTwoPack) = 0 Then
Set CurDb = CurrentDb
Set RegisteredUnique = CurDb.OpenRecordset
("tblRegisteredAndUniqueLabels")
With RegisteredUnique
!JobNumber = [Forms]!
[USPSCoilerEditOneTwoPackage]![JobNumber]
!UsedWithJob = [Forms]!
[USPSCoilerEditOneTwoPackage]![UsedWithJob]
!UniqueLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![UniqueLabel]
!CartonWeight = [Forms]!
[USPSCoilerEditOneTwoPackage]![CartonWeightOneTwoPack]
!SkidNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![Skid]
!PartialCarton = [Form]!
[USPSCoilerEditOneTwoPackage]![PartialCarton]
!EmployeeNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![EmployeeNumber]
!Shift = [Form]![USPSCoilerEditOneTwoPackage]!
[Shift]
.Delete
End With
RegisteredUnique.Close
Set RegisteredUnique = Nothing
 
B

Bill

Open the recordset and perform a find command to locate the record, then
delete it.
What you are doing below is opening the recordset (It is on record one),
changing the fields values to the forms values, then deleting it. Makes no
sense. look up find in the vba object explorer for DAO recordsets. online
help will give you the details.
HTH
AHopper said:
From a "Delete" command button on a
form "USPSCoilerEditOneTwoPackage" I am trying to delete
a related record in table "tblRegisteredAndUniqueLabels"
before record selected is deleted. There are two
different ways I want to look for the correct record. The
first is if the is a valid number in the text box based
on a field of the same name "RegisteredLabelOneTwoPack"
and the second is if there is
no "RegisteredLabelOneTwoPack". Below is the code I have
been trying. I have been able to add a new record using
similar code but I have not been successful at deleting.


Thanks you in advance for your help

Allan

Dim CurDb As Database
Dim RegisteredUnique As Recordset
'Look for related record with registered label'
If Len(Me.RegisteredLabelOneTwoPack) = 20 And _
IsNumeric(Me.RegisteredLabelOneTwoPack) = True Then
' the text string is 20 characters long and
' it contains only numbers
Dim RegNo As String
RegNo = Nz(Me.RegisteredLabelOneTwoPack)
Me.RegisteredLabel = CStr(Left("" & RegNo & "", 19))
Me.CheckDigit = CStr(Right("" & RegNo & "", 1))
Set CurDb = CurrentDb
Set RegisteredUnique = CurDb.OpenRecordset
("tblRegisteredAndUniqueLabels")
With RegisteredUnique
!JobNumber = [Forms]!
[USPSCoilerEditOneTwoPackage]![JobNumber]
!UsedWithJob = [Forms]!
[USPSCoilerEditOneTwoPackage]![UsedWithJob]
!RegisteredLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![RegisteredLabel]
!CheckDigit = [Forms]!
[USPSCoilerEditOneTwoPackage]![CheckDigit]
!UniqueLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![UniqueLabel]
!CartonWeight = [Forms]!
[USPSCoilerEditOneTwoPackage]![CartonWeightOneTwoPack]
!SkidNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![Skid]
!PartialCarton = [Form]!
[USPSCoilerEditOneTwoPackage]![PartialCarton]
!EmployeeNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![EmployeeNumber]
!Shift = [Form]![USPSCoilerEditOneTwoPackage]!
[Shift]
.Delete
End With
RegisteredUnique.Close
Set RegisteredUnique = Nothing
Me.RegisteredLabelOneTwoPack = Null
Me.CheckDigit = Null
End If

'Look for record no registered label is available to use.'
If IsNull(Me.RegisteredLabelOneTwoPack) Or Nz
(Me.RegisteredLabelOneTwoPack) = 0 Then
Set CurDb = CurrentDb
Set RegisteredUnique = CurDb.OpenRecordset
("tblRegisteredAndUniqueLabels")
With RegisteredUnique
!JobNumber = [Forms]!
[USPSCoilerEditOneTwoPackage]![JobNumber]
!UsedWithJob = [Forms]!
[USPSCoilerEditOneTwoPackage]![UsedWithJob]
!UniqueLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![UniqueLabel]
!CartonWeight = [Forms]!
[USPSCoilerEditOneTwoPackage]![CartonWeightOneTwoPack]
!SkidNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![Skid]
!PartialCarton = [Form]!
[USPSCoilerEditOneTwoPackage]![PartialCarton]
!EmployeeNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![EmployeeNumber]
!Shift = [Form]![USPSCoilerEditOneTwoPackage]!
[Shift]
.Delete
End With
RegisteredUnique.Close
Set RegisteredUnique = Nothing
Click to expand...
 
A

AHopper

Bill
Thank you for giving me a direction to look in.I am
trying to learn some new skills. I will not have time
today to research but I will try tomorrow. Excuse my
ignorance but is the vba object explorer part of Access?

Thank you again for your support and help
Allan
-----Original Message-----
Open the recordset and perform a find command to locate the record, then
delete it.
What you are doing below is opening the recordset (It is on record one),
changing the fields values to the forms values, then deleting it. Makes no
sense. look up find in the vba object explorer for DAO recordsets. online
help will give you the details.
HTH
From a "Delete" command button on a
form "USPSCoilerEditOneTwoPackage" I am trying to delete
a related record in table "tblRegisteredAndUniqueLabels"
before record selected is deleted. There are two
different ways I want to look for the correct record. The
first is if the is a valid number in the text box based
on a field of the same name "RegisteredLabelOneTwoPack"
and the second is if there is
no "RegisteredLabelOneTwoPack". Below is the code I have
been trying. I have been able to add a new record using
similar code but I have not been successful at deleting.


Thanks you in advance for your help

Allan

Dim CurDb As Database
Dim RegisteredUnique As Recordset
'Look for related record with registered label'
If Len(Me.RegisteredLabelOneTwoPack) = 20 And _
IsNumeric(Me.RegisteredLabelOneTwoPack) = True Then
' the text string is 20 characters long and
' it contains only numbers
Dim RegNo As String
RegNo = Nz(Me.RegisteredLabelOneTwoPack)
Me.RegisteredLabel = CStr(Left("" & RegNo & "", 19))
Me.CheckDigit = CStr(Right("" & RegNo & "", 1))
Set CurDb = CurrentDb
Set RegisteredUnique = CurDb.OpenRecordset
("tblRegisteredAndUniqueLabels")
With RegisteredUnique
!JobNumber = [Forms]!
[USPSCoilerEditOneTwoPackage]![JobNumber]
!UsedWithJob = [Forms]!
[USPSCoilerEditOneTwoPackage]![UsedWithJob]
!RegisteredLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![RegisteredLabel]
!CheckDigit = [Forms]!
[USPSCoilerEditOneTwoPackage]![CheckDigit]
!UniqueLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![UniqueLabel]
!CartonWeight = [Forms]!
[USPSCoilerEditOneTwoPackage]![CartonWeightOneTwoPack]
!SkidNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![Skid]
!PartialCarton = [Form]!
[USPSCoilerEditOneTwoPackage]![PartialCarton]
!EmployeeNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![EmployeeNumber]
!Shift = [Form]![USPSCoilerEditOneTwoPackage]!
[Shift]
.Delete
End With
RegisteredUnique.Close
Set RegisteredUnique = Nothing
Me.RegisteredLabelOneTwoPack = Null
Me.CheckDigit = Null
End If

'Look for record no registered label is available to use.'
If IsNull(Me.RegisteredLabelOneTwoPack) Or Nz
(Me.RegisteredLabelOneTwoPack) = 0 Then
Set CurDb = CurrentDb
Set RegisteredUnique = CurDb.OpenRecordset
("tblRegisteredAndUniqueLabels")
With RegisteredUnique
!JobNumber = [Forms]!
[USPSCoilerEditOneTwoPackage]![JobNumber]
!UsedWithJob = [Forms]!
[USPSCoilerEditOneTwoPackage]![UsedWithJob]
!UniqueLabel = [Forms]!
[USPSCoilerEditOneTwoPackage]![UniqueLabel]
!CartonWeight = [Forms]!
[USPSCoilerEditOneTwoPackage]![CartonWeightOneTwoPack]
!SkidNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![Skid]
!PartialCarton = [Form]!
[USPSCoilerEditOneTwoPackage]![PartialCarton]
!EmployeeNumber = [Form]!
[USPSCoilerEditOneTwoPackage]![EmployeeNumber]
!Shift = [Form]![USPSCoilerEditOneTwoPackage]!
[Shift]
.Delete
End With
RegisteredUnique.Close
Set RegisteredUnique = Nothing
Click to expand...


.
Click to expand...
 
Top