Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Canceling or Stopping something (Access 97)

    Hi There,

    I need to do a check in my database to make sure a duplicate value does not exist and force the user back to that field to re-enter if it does. I have a mainform and a subform and this is my code:

    Private Sub PROJNUM_BeforeUpdate(Cancel As Integer)

    If (DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]" = Forms!frmRegistrationForm!PROJNUM) Is Not Null) Then
    Beep
    MsgBox "The Bizman Number you entered already exists. Enter a unique Bizman Number", vbInformation, "Duplicate Bizman"
    Me.PROJNUM.SetFocus

    End If

    End Sub

    In testing when I enter a duplicate ID, the message box shows but the code completely ignores my setfocus command and moves the cursor to the next field anyway AND (which is worse) populates my subform with all the data relating to that duplicate ID.

    How can I stop this? If a duplicate is detected I want the whole bus-load to stop and force the user to enter a unique ID. I've been playing with this for a few days now and am at my wits end. Any help would be greatly appreciated.

    TIA.


    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canceling or Stopping something (Access 97)

    Replace
    Me.PROJNUM.SetFocus
    by
    Cancel = True
    Francois

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    You know what ALL of my code is being ignored. Stupidly I did not realise that my error message line was the same as my msgbox. So now when I just ask for the error message to read "Error Exists", that is the message box I see. What am I doing wrong?? Help! TIA.

    Private Sub PROJNUM_BeforeUpdate(Cancel As Integer)
    On Error GoTo PROJNUM_Err

    If (DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]" = Forms!frmRegistrationForm!PROJNUM) Is Not Null)
    Then
    MsgBox "The Bizman Number you entered already exists. Enter a unique Bizman Number", vbInformation, "Duplicate Bizman"
    Me.PROJNUM = Null
    Me.DESC.SetFocus
    Me.PROJNUM.SetFocus
    DoEvents
    End If

    PROJNUM_Exit:
    Exit Sub

    PROJNUM_Err:
    MsgBox "Error Exists"
    Resume PROJNUM_Exit

    End Sub

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Hi,
    Try replacing:
    DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]" = Forms!frmRegistrationForm!PROJNUM) Is Not Null
    with
    DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]=" & Forms!frmRegistrationForm!PROJNUM) Is Not Null
    and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Have you tried setting Cancel to true, as Francois suggested? That will also remove the need to mess around with SetFocus.

  6. #6
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Rory:

    With my Dlookup statement the error message I get is "Object Required".
    With your Dlookup statement I get "Data type mismatch in criteria expression". But the form is based on a query based on the table in the lookup statement, so I know I am comparing apples to apples. Just that darn DLookup statement.

    I can smell the answer we are that close - any more suggestions will be gobbled up enthusiastically! TIA.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Hi Donna,
    Is your project number a text field by any chance? If so, try:
    DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]=<font color=red>'</font color=red>" & Forms!frmRegistrationForm!PROJNUM & "<font color=red>'</font color=red>") Is Not Null
    to put single quotes around the project number.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Rory : Ever get so frustrated you just want to go out and buy a big sack of flour so you can kick the stuffing out of it?

    Anyway - my head is splitting right now. Yes my Projnum field is a text field. I copied in the code you suggested and I get the old favourite "Object Required" as my error. Went back in and put square brackets around the Projnum field, but still no luck.

    It works if I copy in the customers.validate macro from Northwind. And I'd use that but that gives me two messages : my own plus the "system generate" you have duplicate values etc, etc".

    Any other suggestions?

    ????

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Try this :

    <pre>Private Sub ProjNum_BeforeUpdate(Cancel As Integer)
    Dim strCriteria As String
    strCriteria = "[ProjNum] = '" & Me!ProjNum & "'"
    If Not IsNull(DLookup("[ProjNum]", "tblProjectHeader", strCriteria)) Then
    MsgBox "The Bizman Number you entered already exists. " _
    & "Enter a unique Bizman Number", _
    vbInformation, "Duplicate Bizman"
    Cancel = True
    End If

    End Sub
    </pre>

    Edited to eliminate horizontal scrolling--Charlotte
    Francois

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Have you tried Francois' code? It's pretty neat and compact and should work nicely if ProjNum is a text field.
    Let us know if it still doesn't work.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Rory / Francois ---- will you marry me? Thank you! Thank you. Thank you.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does not work at all actually!!!!!!! Help!

    Donna,

    Sorry, already taken (happyly married with an <img src=/S/angel.gif border=0 alt=angel width=15 height=21>)
    Francois

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Does not work at all actually!!!!!!! Help!

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>My Other Half already objects to me spending so much time in here - if she sees things like that, I'll never be allowed back! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Anyway, it was Francois' code that did it......
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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