Results 1 to 11 of 11

Thread: VBA Help (2000)

  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Help (2000)

    I have the following code:
    Private Sub cboCCMechSelectVendor_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmVendor", , , , acFormAdd, acWindowNormal
    Me.cboCCMechSelectVendor.Requery
    End Sub
    When I double click the form opens and I can type in my new vendor, but when I close the form it tells me I have to save record before I can requery. I'm not sure why I'm getting this error.
    Thanks,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: VBA Help (2000)

    Has the record in the form containing cboCCMechSelectVendor been modified? If so, insert

    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    before requerying.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Ok, I added this:
    Private Sub cboCCMechSelectVendor_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmVendor", , , , acFormAdd, acWindowNormal
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    Me.cboCCMechSelectVendor.Requery
    and it wouldn't requery, so i moved the requery before the if me.dirty and it's still not requerying the combo box.
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: VBA Help (2000)

    Are you trying to add a new item to the combo box by opening the other form in add mode?

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Yes.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: VBA Help (2000)

    In that case, you should set the Limit To List property of the combo box to Yes. The idea is that the user enters the new value in the combo box itself, causing the On Not In List event to fire. There are many threads dealing with NotInList, see for example <post#=314477>post 314477</post#>.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    It already is.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: VBA Help (2000)

    But do you have a NotInList event procedure? If so, what is the code you posted for?

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    The code is on the double click event. They double click on the combo box, it opens the vendor form in add, the user adds in the new vendor, they close the vendor form and the combo box updates so the user can complete the order by chosing the vendor. does that help.
    Deb

  10. #10
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA Help (2000)

    The post Hans pointed you to seems to address your situation, with the main difference being that it envisions the user will type the new name in the combo box initially and then hit Enter (or try to tab to the next control), in which case the vendor form will automatically open (with the new vendor name that the user typed in the combo box already filled in in the appropriate textbox in the vendor form). So take a look at that post if that will work for you.

    If you definitely want a double-click to be the trigger (rather than the user attempting to enter the new vendor in the combo box), take the code you've already got (with Hans's additions) and change the way you open the vendor form from acWindowNormal to acDialog. That way the rest of the event procedure won't run until the vendor form has closed (and the new vendor has been added to the database).

    If none of that helps, I expect Hans will be back with you shortly...

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Finally able to get back to this. Thank you. I tried both methods and was able to get both to work.
    Deb

Posting Permissions

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