Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combobox requery (A2K)

    Mark Kindly answered part of this question at the begining of August but I had to leave my office for 3 weeks and return to the Uk so am just trying to solve my problem, any help appreciated.

    I have a form that has some details of my Customers, in the 'notonlist' event I have added one of the self contained Access macros to open my Customers form if there is no record. This works fine but when I return to the original form it does not update (if I close and then re-open the form it is there).

    Mark suggested that a do a requery of the combo box using an event procedure me.Customers.requery.

    Only ever having used built in functions I cannot get it in my mind how to write the open form part, update new record in another form, return to original form and have it requery the Combo box.

    I have no experience whatsoever in making macros, VBA or event procedures but in my feeble mind I Imagine it should go something like

    If no matching record then
    open frmCustomers
    me.Customers.requery

    Sounds easy in an ideal world doesn't it, help please

    TIA

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combobox requery (A2K)

    Well, there is no time like the present to start learning VBA.

    Instead of the macro, you need to select the Event Procedure for the Not In List Event. You need a line of code to open the customer form, then another line of code to requery. Something like this:

    DoCmd.OpenForm "frmCustomer", , , , , acDialog
    Me.cboCustomer.requery

    Using the acDialog opens the customer form while suspending the rest of your code. Without it, the requery would take place immediately. In this way, the requery won't take place until you close frmCustomer. You can check Help on OpenForm to see other options that you might want to use.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (A2K)

    Thanks once again Mark but this has now given me another problem, the dreaded run time error

    Run-time error '2118':
    You must save the current field before you run the requery action

    On my Customers form I added a save button thinking this would do the trick but it does not. Any help apreciated.

    TIA

    Steve

    How did that song go? "Mama told me there'd be days like this"
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combobox requery (A2K)

    Oh yeah, I forgot about that. I usually don't put this code in NotInList event as it is too easy to create a new record (I make it a separate button). You need a "mycombobox.undo" remove the user's entry, prior to the requery. Then you need a way of getting the new entry into the field. For example, if this is a Customer combobo and the customerID is a sequential autonumber or customer counter, I just go out and use DMax to get highest customer#.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (A2K)

    Thanks for the quick response Mark but my level of Access is so low it didn't really mean a thing.

    If you have a moment could you explain it a little more detail, if you cannot thanks for the help you have given so far.

    Best regards

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combobox requery (A2K)

    Are you trying to do this thru a macro or code?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (A2K)

    Hi Mark

    I built an event procedure as you showed me in your reply of 22.08.01 this opens my Customer tables but still requires something to save it before the requery can take place on the original form.

    Once again my thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combobox requery (A2K)

    Your DoCmd.OpenForm of your Customer form should be done using acDialog as the Windows Mode option in OpenForm. This suspends execution of the rest of the code in your NotInList event until the customer form is closed. Immediately upon return, you next lines of code should be:

    cboCustomerID.undo 'or whatever your controlname is
    cboCustomerID.requery

    At this point, you don't need any more code, and you can just let user reselect from the list.

    If you want, you can guess at which customer was just added. This assumes that the CustomerID is an autonumber or other custom counter, such that the customer with the highest ID is the last one entered. So, add this line after the requery above:

    cboCustomerID = DMax("customerid","tblCustomers")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (A2K)

    A 1000 thanks for your assistance Mark, I am rushing around packing to leave the UK for Singapore tonight so I will not be able to try this until Monday, if you read a report about a psyco smashing computers in that neck of the woods you know I have failed.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (A2K)

    Hi Mark,

    I am really sorry if I am driving you barking mad with this problem, I have several books and have looked at the MS help but none of it narrows it down to what I am trying to do. I have followed your instructions and now get a run time error that says object required (please see below). As we are at present a 2 man band it doesn't matter that any user can enter a new Customer as we will always identify them by name not the autonumber. I tried with and without the DMax part but I get the same error.

    Option Compare Database

    Private Sub CustomerId_NotInList(NewData As String, Response As Integer)
    DoCmd.OpenForm "frmCustomer", , , , , acDialog
    cboCustomerId.Undo
    cboCustomerId.Requery
    End Sub

    Private Sub del01_Click()
    On Error GoTo Err_del01_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Exit_del01_Click:
    Exit Sub

    Err_del01_Click:
    MsgBox Err.Description
    Resume Exit_del01_Click

    End Sub
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combobox requery (A2K)

    Your combobox is apparently named [CustomerID]. I didn't know it's name, so I just used [cboCustomerID] in my example. You must change it to match your combobox name.

    BTW, it is generally a good idea to rename your bound controls to something other than the underlying fieldname. That's why I used [cboCustomerID].
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (A2K)

    Eureka, the idiot finally got it

    Thanks Mark, I assumed that Access always called a combo box cbo on its own, obviously I need to read more on naming conventions. It works a treat now.

    As I can't get you a beer I will have a nice cold Tiger beer in a sleazy bar in your honour tonight.

    If you ever get stuck and need help shipping infectious research samples anywhere, I'm your man!!

    Thanks again

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Combobox requery (A2K)

    >>I will have a nice cold Tiger beer <<

    Sounds good, but I'm not familiar with the brand. A local brew?

    And I usually don't have too many infectious research samples to ship, but it's always good to know a name in the business!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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