Using a combo "cboCode", when a duplicate part is selected, the before update code checks to see if the part selected exists, if so the user is prompted as below:
Code: Select all
Private Sub cboCode_BeforeUpdate(Cancel As Integer)
Dim PartCheck As Integer
Dim strWhere As String
' No need to check for duplicates if code is "UN"
If Me.cboCode = "UN" Then Exit Sub
' Check for duplicates
strWhere = "JobID = Forms!frmEstimateDetails!JobID"
PartCheck = DCount("*", "tblEstimateDetails", "Code=" & Chr(34) & Me!cboCode & Chr(34) & " And " & strWhere)
If PartCheck > 0 Then
MsgBox "This Part Already Exists For This Estimate", vbCritical, "Duplicate Part"
Cancel = True
SendKeys "{backspace}"
End If
End Sub
However, something conflicts with the result. When the user is warned of the duplicate, a msgbox appears and the user presses the OK button.
I then get a warning of "No Current Record". I've stepped through but cannot catch where the problem is.
The rest of the code for the form or related control is below.
Can one of you guys make a guess as to why this happens?
Code: Select all
Private Sub cboCode_AfterUpdate()
'If cboCode = "UN" Then
' txtItem.Locked = False
' txtItem = Null
'Else
' txtItem.Locked = True
' txtItem = cboCode.Column(1)
'End If
Select Case cboCode
Case Is = "UN"
txtItem.Locked = False
txtItem = Null
Case Is = "DF"
txtItem = cboCode.Column(1)
txtItem.SetFocus
txtItem.SelStart = txtItem.SelLength - 2
txtItem.Locked = False
Case Is = "RH"
txtItem = cboCode.Column(1)
txtItem.SetFocus
txtItem.SelStart = txtItem.SelLength - 2
txtItem.Locked = False
Case Is = "SPS"
txtItem = cboCode.Column(1)
txtItem.SetFocus
txtItem.SelStart = txtItem.SelLength - 2
txtItem.Locked = False
Case Else
txtItem.Locked = True
txtItem = cboCode.Column(1)
End Select
Exit Sub
End Sub
Code: Select all
Private Sub cboCode_NotInList(NewData As String, response As Integer)
MsgBox "Invalid Item !!" & " " & "You Must Use An Item From The List Or Use The Code UN", vbOKOnly, "!!"
response = acDataErrContinue
End Sub
Code: Select all
Private Sub Form_BeforeUpdate(Cancel As Integer)
If cboCode = "" And Not IsNull(txtItem) Then
MsgBox "You Cannot Create A Description Without A Code", vbOKOnly, "!!"
cboCode.SetFocus
Cancel = True
End If
If cboCode = "UN" And IsNull(txtItem) Then
MsgBox "You MUST Enter An Item.", vbCritical + vbOKOnly, "!!"
txtItem.SetFocus
Cancel = True
End If
End Sub
Private Sub Form_Current()
If cboCode = "UN" Then
txtItem.Locked = False
Else
txtItem.Locked = True
End If
End Sub