Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery (Win ME/Access 97)

    I have a form with a combo box to look up the customer number and place it in the CustNum control. When I have a new customer, I open another form to fill in all of the required data. Then I close the form and return to the original form. The new customer is not available. I have tried all sorts of events to put in a requery action or method - macros, event triggered VBA , buttons on the form, but I can't seem to make it work. Occasionally the Shift-F9 with the cursor on the CustNum control works, but not always. Sometimes the customer name shows up in the combo box, but it doesn't place the customer number in the control. Any ideas of what I am doing wrong?

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Are you using the NotInList event to open the second form?

    If so, open the second form in Dialog mode, that will halt the NotInList code, until the form closes.

    After the form closes add a line:

    Response = acDataErrAdded

    That will requrey the combo for you and select the NewData item.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    No, I am not using the NotInList event. The operator decides that the customer is not in the list (she may move around looking at similar names before taking the decision that the customer is not available). Then she clicks a command button that opens the New Customer data entry form with all of the stuff like telephones, address, tax number and all of that. When she finishes filling that in, she closes the form and ends up back in the original form with the combo box.

    Your suggestion seems to be a better way. Please clarify exactly when the NotInList event occurs. The Help says "The NotInList event occurs for combo boxes whose LimitToList property is set to Yes, after you enter a value that isn't in the list and attempt to move to another control or save the record." Does this mean that the operator can move around in the combo box list but the event does not occur until she tries to move out of the box?

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    George, that is correct. The NotInList event only fires when the user tries to leave the combo or save the record.

    If you want to maintain the status quo, from the command button, open the form in dialog mode, and in the code, right after the from gets opened in dialog mode, add the following line:

    cboMyCombo.Requery.

    The Dialog mode will halt the calling code until the dialog form is closed or hidden and the requery will requery the combo's recordsource and update the values listed.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Trying to maintain the status quo, as you say - the combo box is updated, but the NumCliente field is not.

    The After Update event of the combo box runs the following:

    Me.RecordsetClone.FindFirst "[NumCliente] = '" & Me![Combo19] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    frmFacturasSubForm.SetFocus

    This should set the NumCliente field to the value in the combo box and then set the focus on the sub-form.

    We are getting close.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Is there an alternative to the dialog mode? I am having a conflict with another window that I open to assign a new customer number. More of a bother than a problem, but it is awkward to try to move screens around in order to see the customer number window, rather than having it on top as it was before.

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

    Re: Requery (Win ME/Access 97)

    There's no alternative to a dialog if you want to use the NotInList event to call the other form. When you open the other form as a dialog, the code currently running in the NotInList event simply pauses until the dialog is closed. If you don't open it as a dialog, the code continues while the other form is open and you wind up stepping all over yourself and triggering "not in list" errors because the new item hasn't been entered yet.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Without testing, does the AfterUpdate event of the combo fire when you update the combo's value from code? I don't remeber, off the top of my head.

    You may want to set a breakpoint in the AfterUpdate event code of the combo and see if it runs. If it doesn't you can call the AfterUpdate code directly, after you requery the combo:

    Call cboMyCombo_AfterUpdate

    That will call the procedure, which should update your NumCliente field.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> It just occured to me, are you actually setting the value of the combo to the new data you just entered into the combo's recordsource?

    If not, that's one possibility of where the problem is.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    No, there isn't any alternative to the Dialog mode if you want to update the combo and field by using a separate form.

    You will end up, as Charlotte said, "stepping all over yourself" and not getting the results you want.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    First, thanks for all of the attention. I am going to feel bad when we discover some stupid mistake that i have made. Anyway---

    Let's review. The combo box is being updated. The new record is there, but the NumCliente field is not getting updated. When I select one of the old records in the combo box, the NumCliente field is updated. Then I try the new record, and it does not update my field.

    The row source of the combo is "SELECT DISTINCTROW CustomerMaster.NumCliente, CustomerMaster.Nombre FROM CustomerMaster ORDER BY CustomerMaster.Nombre;". I tried running the SQL statement from the design view of the form, but the problem continues, even though the new record is visible in the results! If I close down the form and reopen it, the new entry functions correctly. That seems strange. It looks like the requery is running the SQL statement as it should, but something is not "taking". What is different when I close and then reopen the form?

  12. #12
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Unless I've missed something you appear to be requerying the combo OK but the form is not showing the new record.
    Are you requerying the form?
    I notice you are using the recordsetclone to find records. Adding a new record to the recordset will invalidate the cloned recordset as well I think (FWIW)
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  13. #13
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    I'm not sure.

    Can you post a stripped down version, just the forms in question and maybe a couple of non-identifiable rows of data?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  14. #14
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Yes, I am sure that would help. How do I go about doing that? Do I send the .mdb file? That is too large, I would think.

  15. #15
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery (Win ME/Access 97)

    Create a new blank DB.

    Import, from your original, the forms in question, any queries/tables/code that is needed to make the forms functional.

    You can strip out most of the data (leave a few rows so we can see what it's supposed to look like but change any confidential data). Compact it and then zip it. You will need to make sure that the zip is less than 100k to be able to post it to the Lounge.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Page 1 of 3 123 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
  •