Check for duplicate records

[EssKayKay]

Ellis,

How unobservant of me – I love it (similar to my EssKayKay but much better).

After keying in any label (duplicate or not) and clicking Update, I receive an error:

“Compile error: variable not defined”
on “Public Function dynExecuteSQL(ByVal pstrSQL As String) As Boolean”

I believe I have removed all my error handling.


Following is what/how I modified the code:

sUpdate = sUpdate + " where MSLink = " + lblMSLinkID.Caption
Set obj1 = New Class1
    
    'Here's where the MicroStation Duplicate Error will occur (between MsgBox 1 & 2)
    'MsgBox "1"
'obj1.dynExecute = sUpdate
    'MsgBox "2"

'--- added per vbforms 02-18-2010 ----
If obj1.dynExecuteSQL(sUpdate) Then
    ' Error adding to table (probably a duplicate Label)
    Set obj1 = Nothing
    Exit Sub
End If
'-------------------------------------

Set obj1 = Nothing

Where Error occurs:

Public Function dynExecuteSQL(ByVal pstrSQL As String) As Boolean
'added per suggestion on vbforms.net 02-02-2010

On Error GoTo dynExecuteSQLErr
    Dim cmd1 As New ADODB.Command

    Set cmd1.ActiveConnection = cn1
    cmd1.CommandText = sSQL
    cmd1.Execute

dynExecuteSQLExit:
    Set cmd1 = Nothing
    Exit Function
    
dynExecuteSQLErr:
    dynExecuteSQL = True
    Select Case Err.Number
        Case -2147467259: MsgBox "Duplicate Label exits - please re-enter..."
        Case Else: MsgBox Err.Description
    End Select
    Resume dynExecuteSQLExit
End Function

Thanks,
SKK

[Ellis Dee]

Oops, sorry, my bad. Another typo. Change the highlighted line as shown:

Public Function dynExecuteSQL(ByVal pstrSQL As String) As Boolean
'added per suggestion on vbforms.net 02-02-2010

On Error GoTo dynExecuteSQLErr
    Dim cmd1 As New ADODB.Command

    Set cmd1.ActiveConnection = cn1
    cmd1.CommandText = pstrSQL
    cmd1.Execute

dynExecuteSQLExit:
    Set cmd1 = Nothing
    Exit Function
    
dynExecuteSQLErr:
    dynExecuteSQL = True
    Select Case Err.Number
        Case -2147467259: MsgBox "Duplicate Label exits - please re-enter..."
        Case Else: MsgBox Err.Description
    End Select
    Resume dynExecuteSQLExit
End Function

[EssKayKay]

Got it - no errors, BUT if I keyin a duplicate label, after clicking OK on the Duplicate Error message box, it proceeds to place the manhole info box with the duplicate label. I was hoping it would simple exit the procedure.

SKK

[Ellis Dee]

Depending on the definition of simple, it is. It's easier than what we've already done, for example.

Note that you said you didn't want it to just do nothing. That's why the CheckForDuplicates() method was rejected; there was some other logic you wanted to have happen. Now is probably a good time for you to explain in detail what that other logic is. If you aren't sure what I mean, we can identify it through trial and error.

In the meantime, change the UpdateSanMan() Sub to a Function by changing this line...

Private Sub UpdateSanMan()

...to this...

Private Function UpdateSanMan() As Boolean

Then inside UpdateSanMan in the same place as before add the highlighted line:

'--- added per vbforms 02-18-2010 ----
If obj1.dynExecuteSQL(sUpdate) Then
    ' Error adding to table (probably a duplicate Label)
    Set obj1 = Nothing
    UpdateSanMan = True
    Exit Function
End If
'-------------------------------------

Once that's in place we can begin the trial and error as necessary. (The "Exit Sub" line should change itself to "Exit Function" automatically.)

[Ellis Dee]

The first trial will be the simplest, just exiting on an error. After implemeting the changes from the previous post, everything else will be handled in cmdUpdate_Click(). Make the following change and see how it suits your needs:

Private Sub cmdUpdate_Click()
cmdDone.Visible = False
cmdCancel.Visible = True

If cmdUpdate.Tag = "Edit" Then
    UnLockForm_SanMan
    cmdUpdate.Caption = "Update"
    cmdUpdate.Tag = "Update"
Else
    CheckForZero
    If UpdateSanMan() Then Exit Sub
    ScanDrawingRemoveSANMANText
    CommandState.StartPrimitive New clsPlaceSanTextNode
    LockForm_SanMan
    cmdUpdate.Tag = "Edit"
    cmdUpdate.Caption = "Edit"
    cmdDone.Visible = True
    cmdCancel.Visible = False
    Unload frmSanMH
    frmSanitary.SHOW
End If
End Sub

[EssKayKay]

I guess I shouldn’t have used the word “simply” as I realize nothing’s that easy.

I’m very sorry if I said I didn’t want it to do nothing (most definitely a misstatement). If it encounters a duplicate label, after displaying the Duplicate Record message, that’s exactly what I want it to do – nothing (other than possibly closing the input form).

I changed Private Sub UpdateSanMan() to
Private Function UpdateSanMan() As Boolean
and added the line UpdateSanMan = True

But it’s still placing a manhole info box with duplicate label data after clicking OK on the Duplicate Error message box.

SKK

[Spoo]

SKK

Did you also make the change to Private Sub cmdUpdate_Click()
that Ellis Dee suggested in Post #45?

I'm sure he'll call you on this too (!!), but my guess is that
the change he suggested will cause you to exit that sub
prior to "adding the manhole" to your database.

Spoo

[EssKayKay]

Spoo - it looks like that did it (I need be much more observant) - thanks

Ellis - I think you have it. I'm going to play with this more tomorrow but it works so far. The real test will be when I try to replicate this for storm sewer manholes. The routine is very similar to sanitary so hopefully all goes well. I’ll keep you posted.

Thank you very much for all your time and effort on assisting me with this – I am very appreciative. As I’m sure you noticed how clueless I am with this (and my lack correspondence skills). This will be most helpful with maintaining our data integrity.

Again, thanks for everything,
Steve Kipping
City of Manitowoc
Engineering

[Ellis Dee]

I'm happy to help.

I can tell you right now there's a decent chance that adding duplicate storm sewers will probably not reflect our new logic. The good news is that we've already done the heavy lifting by creating the necessary method in Class1 (dynExecuteSQL) and changed UpdateSanMan from a sub to a function. In all likelihood the only thing you'll need to do is identify which command button click event is firing and alter it as in post 45. In other words, change one line and that's it. (Probably; I offer no guarantees! heh.)

[Ellis Dee]

I’m very sorry if I said I didn’t want it to do nothing (most definitely a misstatement). If it encounters a duplicate label, after displaying the Duplicate Record message, that’s exactly what I want it to do – nothing (other than possibly closing the input form).

That's what the original change did, the one where we added CheckForDuplicates(). In response to that you said:

When I open the form, the cmdUpdate button is captioned EDIT. When I click Edit, the button’s caption should change to UPDATE. However, at the time, the “Duplicate Label found” message box appears and the routine bypasses the update. Maybe if we could test for duplicate record after the UPDATE button is clicked (in other words, prior to committing the record), we’d be OKAY.

I never really understood this, but in fairness to you I recognize that the dual-functionality of the Update button adds extra complexity to the user interface logic. I probably should have pressed this point further, but hindsight is 20/20. There is nothing inherently wrong about the approach we ended up going with.

Be sure to test it out and verify the user interface is reacting correctly to all scenarios. eg: When it's a dupe it does what you want, when it's not a dupe it does what you want, and both hold true for when it's an update button and when it's an edit button.

I wouldn't be surprised if there were some minor user interface tweaking you still want/need to do. I'm happy to stick with it until you get everything squared away.

[EssKayKay]

Well Ellis, I updated the storm manhole routine to function similar to the sanitary and all appears fine. I threw every scenario at it I could think of and it worked great. The real test though will be when I install it on other user’s machines (they work with this stuff much more than I). Hopefully all goes well.

So, again, thank you very much for all your time and effort. Like I said, it truly is appreciated.

Goodbye for now and please take care,
SKK

[AsmIscool]

Unfortunately Ellis I cannot give you any rep at the moment but this thread is exactly why I visit this forum. You have inspired me.

BTW I never picked up on the name either. Guess you have to spell it out to some people but it's a good one.