Error 3058 after DataEntry change

[bcooler]

Hello all-

I am having a weird problem and have done a lot of Googling before posting, so here goes:

I have a form that I want to use as both new record entry and existing record review. On entering the form, the default setup is to look at the existing records. When the operator presses the "New Record" button, the event gives a combobox the correct rowsource, controlsource, and sets the DataEntry to True.

The operator can then pick a new part from the combobox. Once the choice is made, I have an error handling code that says:

Private Sub cboPartNo_BeforeUpdate(Cancel As Integer)
    If Me.cboPartNo.ControlSource = "PartNo" Then 'only verifying no duplicates if entering a new record
        Call NoDuplicateSerial(Me, Cancel, "tblInfo") '"ME" sends the form to the module for duplication verification
    End If
End Sub

which calls a module that I often use:

Sub NoDuplicateSerial(FormName1 As Form, Cancel, TableName)
    Dim Answer As Variant
    Answer = DLookup(FormName1.ActiveControl.ControlSource, TableName, FormName1.ActiveControl.ControlSource & " = '" & FormName1.ActiveControl & "'")
    If Not IsNull(Answer) Then
        MsgBox "Part number has already been entered in the database."
        Cancel = True
        FormName1.ActiveControl.Undo
    End If
End Sub

Now, this works well, but I am having a small problem. After the error handling is complete, I have to press "ESC" in the combobox before I can go back to looking at existing records. Otherwise, I get a error 3058.

I understand that this means the record needs a primary key, but I thought the error handling code removed any entry? Why do I have to manually press "ESC" to stop this error? How do I get around this?

Thanks for the help!

 

[boblarson]

I don't see any error handler at all here.

Also, you are using a SUB for the check when you need a FUNCTION so that you can return a value for Cancel.

Function NoDuplicateSerial(FormName1 As Form, TableName) As Integer
    Dim Answer As Variant
    Answer = DLookup(FormName1.ActiveControl.ControlSource, TableName, FormName1.ActiveControl.ControlSource & " = '" & FormName1.ActiveControl & "'")
    If Not IsNull(Answer) Then
        MsgBox "Part number has already been entered in the database."
        Cancel = True
        FormName1.ActiveControl.Undo
    End If
End Sub

And then you call it like this:

Private Sub cboPartNo_BeforeUpdate(Cancel As Integer)
    If Me.cboPartNo.ControlSource = "PartNo" Then 'only verifying no duplicates if entering a new record
        Cancel =  NoDuplicateSerial(Me, "tblInfo") '"ME" sends the form to the module for duplication verification
    End If
End Sub

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

Oops, you also have to move the UNDO back to the before update event. I missed that. If you don't it will cause a problem.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[PHV]

Bob, in your function I'd replace this:
Cancel = True
with this:
NoDuplicateSerial = True

 

[boblarson]

PHV:

Yeah, I missed that also. Brain fart. LOL

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[bcooler]

ok, I did what you asked, but with the same results. Here's How I interpreted what you wrote (with corrections)

Private Sub cboPartNo_BeforeUpdate(Cancel As Integer)
    If Me.cboPartNo.ControlSource = "PartNo" Then 'only verifying no duplicates if entering a new record
        Cancel = NoDuplicateSerial(Me, "tblInfo")  '"ME" sends the form to the module for duplication verification
        Me.ActiveControl.Undo
    End If
End Sub

and this one

Function NoDuplicateSerial(FormName1 As Form, TableName) As Integer
    Dim Answer As Variant
    Answer = DLookup(FormName1.ActiveControl.ControlSource, TableName, FormName1.ActiveControl.ControlSource & " = '" & FormName1.ActiveControl & "'")
    If Not IsNull(Answer) Then
        MsgBox "Part number has already been entered in the database."
        NoDuplicateSerial = True
    End If
End Function

Sorry about calling this an error handling routine. More just a way to prevent the user from making a mistake.

As I said before, I can press "Esc" and the problem goes away. It seems the error stops at the code for the Click event that allows the user to look at existing records:

Private Sub cmdRecords_Click()
    Me.cboPartNo.RowSource = "SELECT DISTINCT qryInfo.PartNo, [INV-On Hand Wash-Bake].[Item Description] FROM [INV-On Hand Wash-Bake] RIGHT JOIN qryInfo ON [INV-On Hand Wash-Bake].[Inventory Item Num]=qryInfo.PartNo;"
    Me.cboPartNo.ControlSource = ""
    Me.DataEntry = False
    Me.Requery
    DoCmd.GoToRecord , "", acFirst
End Sub

and stops at the "Me.DataEntry = False" portion.

Does that help?

Thanks again for your help!