Avoiding duplicate numbers

[Henley12]

I have an Equipment form that is used to list pieces of equipment. Sometimes these pieces are very similar and instead of re-entering all the information for a new piece of equipment, we just want to copy the record. I have a copy button on the form with some code that performs this task perfectly.....except when you enter a duplicate equipment number. It will go ahead and seamingly copy the record, but when you close the form, it is not there.......and it doesn't give you any indication that it didn't save. I am attaching the code used to copy the form. I need a way to make sure not to duplicate the equipment number.

Private Sub cmdCopy_Click()
Dim newID, oldKey, varRV As Variant
Dim strBookMark, newSerial, strMsg As String
txtCopy.Value = 1
oldKey = EquipKey
On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
SvKey = oldKey
EquipID.SetFocus
newID = InputBox("Enter a new Equipment ID Number", "New Equip ID")
EquipID = newID
DoCmd.RunCommand acCmdSaveRecord
Me.EqPurchaseDt = Null
Me.EqInstalledDt = Null
Me.EqInventoryDt = Null
Me.EqDepreciationDt = Null
Me.EqRetiredDt = Null
Me.EqWarrStartDt = Null
Me.EqWarrExpireDt = Null
' Copy the comments?
strMsg = "Do you want to copy the comments?"

If Dialog.Box(strMsg, vbQuestion + vbYesNo, "Copy Comments?") = vbYes Then
' Do nothing.
Else
Me.EqComments = Null
End If

newSerial = InputBox("Enter a new Serial Number", "New Serial Number")
EqSerial = newSerial
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCpyRatings"
DoCmd.OpenQuery "qryCpySchedules"
DoCmd.SetWarnings True
strBookMark = Me.Recordset.Bookmark
Me.Refresh
Me.Recordset.Bookmark = strBookMark

varRV = DLookup("[qryRatingsValue]![SumOfEqValue]", "[qryRatingsValue]", "[qryRatingsValue]![EquipKey]=Forms![frmNewEquipment]![EquipKey]")

If Not IsNull(varRV) Then
Forms!frmNewEquipment!TabCtl51.Pages(6).caption = "Ratings: " & varRV
txtRatingsValue = "Current Rating: " & varRV
Else
Forms!frmNewEquipment!TabCtl51.Pages(6).caption = "Ratings: 0"
txtRatingsValue = "Current Rating: 0"
End If
EquipID.Enabled = False
cboSearchEquip.SetFocus
SvKey = Null
End Sub

 

[Henley12]

Any ideas on this one?

 

[RuralGuy]

This line: On Error Resume Next
disables any errors. I would put some error handling in your procedure and you will then be able to see what is happening.

 

[Henley12]

I'm getting an error in the following line of code:

Private Sub cmdCopy_Click()
Dim oldKey, varRV As Variant
Dim strBookMark, newSerial, strMsg, newID, varID As String
txtCopy.Value = 1
oldKey = EquipKey
'On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
SvKey = oldKey
EquipID.Enabled = True
EquipID.Locked = False
EquipID.SetFocus
newID = InputBox("Enter a new Equipment ID Number", "New Equip ID")
'Do While varID > ""
varID = DLookup("[tblEquipment]![EquipID]", "[tblEquipment]", "[tblEquipment]![EquipID]=" & newID)
If Not IsNull(varID) Then
Dialog.Box ("Equipment ID already exists. Please enter a new Equipment ID."), vbOKOnly, "Duplicate Number", , , 0
newID = InputBox("Enter a new Equipment ID Number", "New Equip ID")
Else
EquipID = newID
End If
EquipID = newID
DoCmd.RunCommand acCmdSaveRecord
Me.EqPurchaseDt = Null
Me.EqInstalledDt = Null
Me.EqInventoryDt = Null
Me.EqDepreciationDt = Null
Me.EqRetiredDt = Null
Me.EqWarrStartDt = Null
Me.EqWarrExpireDt = Null
' Copy the comments?
strMsg = "Do you want to copy the comments?"

If Dialog.Box(strMsg, vbQuestion + vbYesNo, "Copy Comments?") = vbYes Then
' Do nothing.
Else
Me.EqComments = Null
End If

newSerial = InputBox("Enter a new Serial Number", "New Serial Number")
EqSerial = newSerial
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCpyRatings"
DoCmd.OpenQuery "qryCpySchedules"
DoCmd.SetWarnings True
strBookMark = Me.Recordset.Bookmark
Me.Refresh
Me.Recordset.Bookmark = strBookMark

varRV = DLookup("[qryRatingsValue]![SumOfEqValue]", "[qryRatingsValue]", "[qryRatingsValue]![EquipKey]=Forms![frmNewEquipment]![EquipKey]")

If Not IsNull(varRV) Then
Forms!frmNewEquipment!TabCtl51.Pages(6).caption = "Ratings: " & varRV
txtRatingsValue = "Current Rating: " & varRV
Else
Forms!frmNewEquipment!TabCtl51.Pages(6).caption = "Ratings: 0"
txtRatingsValue = "Current Rating: 0"
End If
EquipID.Enabled = False
cboSearchEquip.SetFocus
SvKey = Null
End Sub


I know this has to be easier than this. Is there a way to check the data in an inputbox against an existing table to see if that data is already there?

 

[RuralGuy]

Don't use the table qualifier:
varID = DLookup("[EquipID]", "[tblEquipment]", "[EquipID]=" & newID)

 

[Henley12]

I'm getting a "data type mismatch in criteria expression" error on that line. My EquipID field is a text field and the newID is a string data type.

 

[RuralGuy]

I was wondering about that:
varID = DLookup("[EquipID]", "[tblEquipment]", "[EquipID]= '" & newID & "'")