Events Conflict

[D Willett]

Hi.
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

This works ok kind of, checks the part and prompts the user of the duplicate.
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

[HansV]

What happens if you remove or comment out the line

SendKeys "{backspace}"

[D Willett]

No difference there Hans, I still get the error.
It's annoying when you can't trap the error.

[HansV]

I'd have to see a copy of the database...

[D Willett]

Hi Hans.
I've attached a stripped down copy. When frmEstimateDetails loads, enter a duplicate record for the fault to occur.

Thanks

[HansV]

I don't get an error, whether SendKeys "{backspace}" is commented out or not. Do you get the error in this copy of the database?

[D Willett]

I get the error:
If I enter as an Example "FB" the duplicate occurs and warns me, after selecting OK I gate the "No record" error.
On close inspection it looks like an empty record exists afet the events.
I've uploaded a short Youtube vid showing the fault:

http://www.youtube.com/watch?v=KiaNvn799iw" onclick="window.open(this.href);return false;

Thanks again.

[HansV]

Thanks for the video. I still can't reproduce the error.
Does it help if you change the SendKeys "{backspace}" line to

Me.cboCode.Undo

[D Willett]

No difference I cant understand why you don't get the error? I tried FB manually and selected from the dropdown also, it doesn't make sense !!

[HansV]

Very strange - I'm not doing anything special.

[D Willett]

So what happens after you have selected a duplicate part? You get the part check and then nothing?

Odd........

[HansV]

I get the "This Part Already Exists For This Estimate" message box, then the combo box is cleared, and I can enter or select another value, or click or tab my way out of the combo box.

[D Willett]

Hmm. Could this be the culprit if we are using different versions of Jet or SQL?
I'll have a look through the example I sent, but appreciate if you get chance to dissect it.

Cheers

[HansV]

I'm using Access 2010 SP2 on Windows 7 Home Premium.

S0499.png

[HansV]

I do have a problem if I try to delete one of the Estimate Details records - I get prompted to fill in tblEstimateDetails.JobID. I'll see if I can find out what causes this after dinner.

[D Willett]

Ok cheers, much appreciated.

[HansV]

Just found that: in Form_Delete, the SQL string should be assembled as follows:

Code: Select all

        strSQL = "DELETE * FROM tblParts WHERE JobID=" & Me.JobID & " AND Code='" & Me.Code & "'"

[D Willett]

Nearly fooled it ...

Code: Select all

Private Sub cboCode_BeforeUpdate(Cancel As Integer)
Dim PartCheck As Integer
Dim strWhere As String
Dim strSQL 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
Me.cboCode.Undo

        strSQL = "DELETE * FROM tblEstimateDetails WHERE JobID=" & Me.JobID & " AND Code='" & Me.cboCode & "'"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
End If
End Sub

This shows #Deleted after exiting the row, close but no cigar..

[HansV]

Do you really want to delete the entire record if the user selects a duplicate code? That seems rather drastic to me - it doesn't leave any room for correcting the record...

[HansV]

I have extensively tested entering/selecting a code and I still can't reproduce the "No current record" error...

For what it's worth, I have attached the version I tested with.

tmp.zip