Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicates revisited (WIN2000-Acc97)

    Here is the code I am using in the Before_Update event:

    Dim x As Variant

    x = DLookup("[magazine_name]", "tblMagazineNames", "[magazine_name]= '" _
    & Forms!frmmagazinenames!txtMAGAZINE_NAME & "'")

    On Error GoTo CustID_Err

    If Not IsNull(x) Then
    Beep
    MsgBox x & " IS ALREADY LISTED IN THE MAGAZINE TABLE", vbOKOnly, "Duplicate Value"
    Cancel = True
    End If

    CustID_Exit:
    Exit Sub

    CustID_Err:
    MsgBox Error$
    Resume CustID_Exit
    End Sub

    This works fine - when user exits the textbox, the message pops up etc.

    I also want this code when the user closes the form. When I place the code in the Form_Close event, I obviously have to remove the Cancel = True line. I replaced it with Exit Sub.
    Here's the problem - when the user tries to close the form, the message box correctly pops up, but then the Windows message "You can't save this record at this time...etc pops up and the form closes.
    How do I have it that:
    1 - The Windows message doesn't pop up
    2- The form doesn't close

    Thank you,

    Michael Abrams

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    Don't see why you need to put it in the close event. I would just use the before update event. if you keep it in the close event it will run the check every time, whether they've edited the record or not. seems like a little overkill, unless there's some special situation you have there.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    Hello Paul,

    Thanks for looking at my post. Maybe I am not going about this the right way, so here's the full scenario.

    I have a form with one textbox on it. "MagazineName".

    The user enters a name in the textbox. There are no other controls on the form other than the navigation button.
    Therefore, after the user types a name in, she only has two options. Click the navigation button to go to a new record, or click the "X" close button to close the form.If they click the navigation button, my Before_Update event works fine.
    If they click the "X", my message pops up, then the Windows message pops up, then the form closes.
    What I would like to happen if the user clicks the "X" ,and the entry is a duplicate, is to run the code and NOT close the form.

    Whew!!!!

    Can this be done??

    Again, thanks for your reply.

    Michael Abrams

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    I'm not certain Michael. What happens if you remove it from the close event and the user clicks the x?

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    Arrrghhhh!!! Of course, the same thing happens. I took the code out of the Close event and just left it in the Before_Update event. When I click the "X" it pops up my message, the Windows "You can't save...." message, then closes.

    Is there a way to code this that the form cannot close if there is a duplicate value?

    Thanks Paul - I hate unnecessary code - hopefully this can be resolved. I appreciate your help.

    Michael Abrams

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    I found this in the online help. I hope it proves useful.

    Note If a form's OnUnload event property specifies a macro that carries out a CancelEvent action, you won't be able to close the form. You must either correct the condition that caused the CancelEvent action to be carried out or open the macro and delete the CancelEvent action. If the form is a modal form, you won't be able to open the macro.

    To carry out the CancelEvent action in Visual Basic, use the CancelEvent method of the DoCmd object.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    Private Sub Form_Unload(Cancel As Integer)
    Dim x As Variant
    x = DLookup("[magazine_name]", "tblMagazineNames", "[magazine_name]= '" _
    & Forms!frmmagazinenames!txtMAGAZINE_NAME & "'")

    On Error GoTo MagazineName_Err

    If Not IsNull(x) Then
    Beep
    MsgBox x & " IS ALREADY LISTED IN THE MAGAZINE TABLE", vbOKOnly, "Duplicate Value"
    DoCmd.CancelEvent
    End If

    MagazineName_Exit:
    Exit Sub

    MagazineName_Err:
    MsgBox Error$
    Resume MagazineName_Exit
    End Sub

    Same result. My message pops up, Windows message"You can't save..." the form closes.

    ????????????????????????

    I am going home to think about this. Hopefully, this can be figured out. I have to believe I am not the only person who has this dilemna !!

    Thanks again Paul !!!

    Michael

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    Is this Before_Update event you mention the Control's Before_Update event or the form's Before_Update event.

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    I have tried it in both places. I have also tried it in the Form_Close event.

    I would have thought it would be simple and I have been working on it for 2 days !!

    One form. One control. Validation rule. Do not allow form to be closed until validatoin is met.
    Pop up a message box warning user that he has entered a duplicate.

    Here is the code again:
    Private Sub txtMAGAZINE_NAME_BeforeUpdate(Cancel As Integer)
    Dim x As Variant

    x = DLookup("[magazine_name]", "tblMagazineNames", "[magazine_name]= '" _
    & Forms!frmmagazinenames!txtMAGAZINE_NAME & "'")

    On Error GoTo MagazineName_Err

    If Not IsNull(x) Then
    Beep
    MsgBox x & " IS ALREADY LISTED IN THE MAGAZINE TABLE", vbOKOnly, "Duplicate Value"
    Cancel = True
    End If

    MagazineName_Exit:
    Exit Sub

    MagazineName_Err:
    MsgBox Error$
    Resume MagazineName_Exit
    End Sub

    This code works just as it is supposed to if the user clicks on the navigation button to go to a new record.
    The message pops up - says You can't have a duplicate. The cursor goes back to the control and the
    user has to put a legit entry. Then he can go to the next record. This works GREAT!!!!

    It's when they click the "X" that the code does not work. The form closes no matter what!

    I need some rest - I'm at home and I am trying not to think about this. (But knowing how I am
    I will probably at least check here one more time tonight.
    So if you can see what I cannot see, please show me the light!!!!

    Thank you patt ,


    Michael Abrams

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    I am sorry for jumping into the middle here, but a thought occurred to me ... Why not place the code on the form's OnClose event, but check the Dirty property first? Then the code will run when the form is closed only if Me!Dirty is True.

    Is the sole purpose of this form to enter a new Magazine Name?

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    I would have put the code in the Form's Before_Update event NOT the control's Before_Update event.
    By putting it in the control's event it forces the message then it tries to write the record before exitting & up comes the message about not saving it before it exits from the form.
    By having it in the Forms event it will firstly not let you write a duplicate. At this point you need to hit the escape key to get rid of the edit status (dirty status) then the exit will happen normally.
    Or am I talking a whole of horsesh...?
    Pat

  12. #12
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    OK-here's the scoop.

    1 - The form is solely a data entry form. The form opens with a textbox to enter a magazine name.
    2- The only other parts to the form are the navigation buttons (to add another name) and the "X"
    3- The code works exactly how I want it to in the control Before_Update (This covers the user clicking
    the navigation button. ( I can leave the code in the Before_Update of the control to maintain this)
    4 - The 'dirty' part, I believe is actually covered with the line:
    If Not IsNull(x) Then......etc
    If it is null, I don't care - let the form close - no foul/no harm
    5 - It is when the user types in a duplicate, and then says OK I'm done. Clicks the "X".
    The code does run (in the Before_Update of the form) but then the Windows message comes up
    and the form doesl close.

    I am starting to have a feeling that:
    1) This cannot be done as I'd like it. (Just a couple of lines of code in the Before_Update of the form)
    2) If I have to, I can put 2 buttons on the form. One "ADD RECORD" and one "CLOSE".
    I can remove the navigation buttons and the "X".

    (But I don't wanna do the second one - but will if I hafta.)

    Thanks again everyone AND GOOD NIGHT FROM SACRAMENTO!!

    Michael Abrams

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicates revisited (WIN2000-Acc97)

    Hi Michael,

    I'm in Sacramento too, so maybe I can help. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I'm attaching a sample database with one table and one form that does what I think you're trying to do. It uses the BeforeUpdate event of the control and the Unload event of the form and does not allow the entry of an item that already exists in the table.
    Attached Files Attached Files
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates revisited (WIN2000-Acc97)

    Hello fellow Sacramentan!!

    At least I don't feel as crazy as I did last night.
    Charlotte, your code is obviously more sophisticated than mine, but the thing is - yours does the same as mine !!!

    If you open your form and go to record 5 (blank record) and type in Mad, then try to click the navigation button, the proper message box pops up (like mine), user clicks OK and the field is blank and the cursor is there waiting for the user to type.
    So far so good. Mine works fine using the code I posted above.

    Now - try going to record 5. Type in Mad. Then click the "X". The custom message pops up (like mine does). Click OK. Then the Microsoft Message pops up saying "You can't save this record at this time" (Like it does with my code)
    The $64,000 question. Can we get rid of this warning, so that when the user clicks OK on "OUR" messagebox, it responds like it does when the user clicked OK using the navigation button??

    I hope I am being clear 'cause sometimes I tend to go on with it

    Thanks again for everyone's help. I think this can be done.
    Michael Abrams <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicates revisited (WIN2000-Acc97)

    Ah, I see. I didn't know how to duplicate the problem you were having. The BeforeUpdate event of the form happens before the UnLoad or Close event, and the events are stepping on each other. It might be simpler to just remove the close button (X) from the form and put a command button on there. You can put code behind the command button to do any testing or undoing, and then issue a DoCmd.Close acForm, Me.Name to close the current form. That way, they can't shortcircuit your code.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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