Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Catch error before form loads?

    Hello,

    I have a form with two listboxes and one button. I select a row from each listbox and click the button to open up another form.

    If the primary keys which were selected create duplicate values on the new form I get the standard message "The changes you made were not successful......you cannont save at this time....". So, I have added my own message on the forms "On Error" event -
    Code:
    If DataErr = 3022 Then
    Response = acDataErrContinue
    MsgBox "This User is already a member of this group! Please modify or delete your entry."
    End If
    This works fine, but what I would really like is to stop the form from loading completely, but still have the message appearing after the button is pressed. Can it be done?

  2. #2
    Join Date
    Aug 2004
    Posts
    364
    Any ideas or suggestions?

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I recommend that you put code in the click event of the button that opens the second form, that does a search of your data source for the values in the two list boxes. If a match is found then you do not open the form but only display your message about duplication not being allowed.

    If you need an example of searching for a duplicate, I have code in my recent post "Edit a Field from A Form".
    Last edited by JerryDal; 04-19-10 at 15:31. Reason: make sentence clearer "but only display..."

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Thankyou for your reply.

    I have put my original error code into the on-click event of the button, and it does't execute my error message, Access just does produced its standard "The changes you made were not successful..." message.

    Do I need different code in the buttons on-click event?

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    My opinion is that you should not get to the stage where there is an error when there is a way to catch the error before it occurs.
    Before moving to form #2, you should search the table that has the fields that could possibly generate the duplication error in form #2, and see if values in your form #1 list boxes will create a duplicate error.
    When you find that a error in duplication will occur with the opening of the #2 form, you then do not open that form. You display your own message.
    Good luck.

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by JerryDal View Post
    My opinion is that you should not get to the stage where there is an error when there is a way to catch the error before it occurs.
    Before moving to form #2, you should search the table that has the fields that could possibly generate the duplication error in form #2, and see if values in your form #1 list boxes will create a duplicate error.
    When you find that a error in duplication will occur with the opening of the #2 form, you then do not open that form. You display your own message.
    Good luck.
    You have just re-worded the title of my thread!

    Joking aside and I do appreciate you taking the time to respond (no-one else is!), I will have a look at the code you suggested in your other post and try and work something out. My VB skills are not very good, so I was hoping for a little more help but I will keep cracking at it.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can write a function (save it in a module) which returns true or false if a matching record exists (again, you would do this in a button before opening the form if you want to stop the form from opening).

    ex (ADO coding):

    Function IsDupRec(varPrimaryKey as variant) as boolean
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where MyPrimaryKeyField = '" & varPrimaryKey & "'" <- assuming MyPrimarykeyField is a text type
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    IsDupRec = false
    else
    IsDupRec = true
    end if
    rs.close
    set rs = nothing
    end function

    Then you'd call the function as such (passing the value)

    If IsDupRec(me!MyPrimaryKeyField) = true then <- or If IsDupRec(SomeValue) = true then...
    msgbox "it is a dup."
    else
    msgbox "no dup."
    end if

    I used MyPrimaryKeyField as an example but you'd pass whatever field value you want to check for a duplicate on, modifying strSQL in the function as needed.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by pkstormy View Post
    You can write a function (save it in a module) which returns true or false if a matching record exists (again, you would do this in a button before opening the form if you want to stop the form from opening).

    ex (ADO coding):

    Function IsDupRec(varPrimaryKey as variant) as boolean
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where MyPrimaryKeyField = '" & varPrimaryKey & "'" <- assuming MyPrimarykeyField is a text type
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    IsDupRec = false
    else
    IsDupRec = true
    end if
    rs.close
    set rs = nothing
    end function

    Then you'd call the function as such (passing the value)

    If IsDupRec(me!MyPrimaryKeyField) = true then <- or If IsDupRec(SomeValue) = true then...
    msgbox "it is a dup."
    else
    msgbox "no dup."
    end if

    I used MyPrimaryKeyField as an example but you'd pass whatever field value you want to check for a duplicate on, modifying strSQL in the function as needed.
    Hello, I havent used modules yet, I put the Function code insode a moulde, save it as something meaningful, then the code to call the function - does that go in the on-click event of my button on the form?

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    I have put this code into a module -
    Code:
    Function IsDupRec(varPrimaryKey As Variant) As Boolean
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from beneficiaries_qry where beneficiaryID = " & varPrimaryKey & "" '<- assuming MyPrimarykeyField is a text type
    
    
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    IsDupRec = False
    Else
    IsDupRec = True
    End If
    rs.Close
    Set rs = Nothing
    End Function
    And put this code into my button on-click event -
    Code:
    If IsDupRec(beneficiaryID) = True Then '<- or If IsDupRec(SomeValue) = true then...
    MsgBox "it is a dup."
    Else
    MsgBox "no dup."
    End If
    But I am getting a type mismatch error message, and am not sure why. I removed the ' quotes from the strSQL line as my primary key field is an Auto Number. Was I right to remove them? I have also tried altering the line Dim strSQL as String to Dim strSQL as Integer, to no success. Any ideas where Im going wrong?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    If DCount("beneficiaryID", "beneficiaries_qry", "beneficiaryID = " & beneficiaryID) = 0 then
        MsgBox "no dup."
    Else
        MsgBox "it is a dup."
    End If
    Have a nice day!

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    Try:
    Code:
    If DCount("beneficiaryID", "beneficiaries_qry", "beneficiaryID = " & beneficiaryID) = 0 then
        MsgBox "no dup."
    Else
        MsgBox "it is a dup."
    End If
    Tried it, but now Im getting this error -
    Syntax Error (missing operator) in query expression .BeneficiaryID = '.
    Help!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Whats the value of the variable beneficiaryID used in the DCount function and (possibly) whats the code of the query?
    Have a nice day!

  13. #13
    Join Date
    Aug 2004
    Posts
    364
    The query is called beneficiary_qry and the sql is -
    Code:
    SELECT beneficiary.beneficiaryID, beneficiary.beneficiaryName, beneficiary.beneficiaryAddress
    FROM beneficiary;
    The value of the beneficiaryID in the Dcount is an auto number taken from the primary key field of the beneficiary table. I am selecting a row in a listbox and the row source of the list box is beneficiary query with bound colum set to "1" which is the beneficiaryID.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then you have to use the value of the selected row in the listbox:
    Code:
    If DCount("beneficiaryID", "beneficiaries_qry", "beneficiaryID = " & Me.<ListBox>.Value) = 0 then
    Where <ListBox> is the name of the listbox on the form.
    Have a nice day!

  15. #15
    Join Date
    Aug 2004
    Posts
    364
    Ok using this code makes the "its a dup" message appear. But, it appears all the time, regardless of wether there actually is a duplicate or not.
    Code:
    If DCount("beneficiaryID", "beneficiaries_qry", "beneficiaryID = " & Me.lst_beneficiaries.Value) = 0 Then
        MsgBox "no dup."   
    Else
        MsgBox "it is a dup."
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •