Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delayed response (A2K)

    In A2K,

    I have a table of personnel from which I want to make "named groups". I
    have a form with two list boxes and a combobox. One list box displays
    all personnel. The other listbox displays the members of the group.
    The combobox shows the name of the current group and is bound to the
    table of group names.

    When I want to create a new group, I click the 'New' button which nulls
    the combobox and clears the second listbox. I add members to the group
    by double-clicking a name in the personnel list or by highlighting it
    and clicking the "Add to List" button. If I click "New" and start
    adding to the group, the names immediately show up in the second
    listbox.

    However, if I click 'New', then enter a group name in the combobox,
    *then* start picking names in the personnel list, the first name
    selected won't display. When a second name is selected, the first two
    display and every name thereafter shows up as it is selected.

    Everyone here stands around shaking their heads saying, "That's weird"
    but they have no suggestions.

    TIA
    Donald

  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: Delayed response (A2K)

    How do you "null the 2nd listbox"? Does merely setting combo box to null do it? And how/where are you getting 2nd listbox to redisplay?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delayed response (A2K)

    The second listbox is actually bound to a temporary table; I do a Form.Refresh to display the current contents.

    The combobox is bound to a table of groupnames. I do a cboGroupName.Value = Null to clear it.

    Either double-clicking an entry in the first listbox or selecting an entry in the listbox and clicking an 'Add' button calls an "AddToGroup" subroutine that copies four fields from the first listbox to the temporary table and then does a Form.Refresh.

    The strangness is that the Refresh won't work for the first Add after putting something in the combobox, but works thereafter.

    Thanks,
    Donald

  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: Delayed response (A2K)

    I would do a "Listbox2.Requery" rather an a form refresh. I'm not sure that a refresh does a requery of all combo and list boxes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delayed response (A2K)

    Nope. Been there. Done that.

    The behavior with the ListBox2.Requery is even worse. It won't display until the third addition and thereafter it will display two at a time after two have been added. At least with the Form.Refresh I only miss the very first one.

    I *have* found out that it has something to do with the combobox itself. I unbounded it and the problem still occurs. But if I put in a text box everything seems to work. Apparently if I don't give focus to a combobox in this sequence there isn't a problem. So I guess one work around would be to overlay a textbox on a combobox and only use the combobox to select previously named groups, shuffling values back and forth between the textbox and the combobox.

    Unless of course you come up with some other brilliant solution . . .

    Thanks,
    Donald

  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: Delayed response (A2K)

    Donald,

    Okay, there is something not right here. What you've described could not be caused by anything you've mentioned. My guess is that the requery of listbox2 is being done before the Temp table is being updated. You didn't really say how you were adding to this temp table. Is it an SQL statement, or are you opening a recordset and using .Addnew, etc.? You might try putting a DoEvents prior to the requery of the Listbox.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delayed response (A2K)

    The tables are in a BE (currently located on the same hard drive, in a subdirectory from where the FE is located). When the system is started, ADO connections are established to the BEs.

    Additions to the work table are done with the following sequence:

    Set rst = New ADODB.Recordset
    rst.Open [arguments]
    rst.AddNew
    rst.Fields(i) = value(i)
    rst.Update
    Set rst = Nothing
    Form.Refresh <- or lstBox.Requery which hasn't behaved well.

    This all seems to work *except* for the first add after exiting the combobox.

    The reason that I am opening and closing the recordset each time is that if the user tries to add the same one twice (not allowed by table index) the error handler traps on the .Update, but even if I issue an Err.Clear, the next .AddNew goes to the error handler because the error is still there. The only way I found to clear an error was to kill the recordset.

    I put in a DoEvents before the ListBox.Requery and the behavior was worse than the Requery alone.

    I hope you can come up with more things to try.

    Thanks,
    Donald

  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: Delayed response (A2K)

    For one thing, I'd insert an "rst.Close" in your code. But other than that, I don't see why you are having problems. Is the Temp table in frontend or backend? I still believe this has to do with timing. The only reason I can explain why doing a form.refresh works is that it takes awhile to do; because all it does is refresh the recordsource behind the form.

    Try this, place a message box immediately before the listbox2.requery; then merely press OK. Does this make a difference?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delayed response (A2K)

    I put in the rst.Close right after the .Update.
    The temp table is in the BE.

    I put in the MsgBox and played around counting to 3, 4 or 5 seconds. It seems that waiting about 4 seconds before doing the .Refresh will allow the first one to display. Then I can add more as fast as I want and they display.

    However, if I use the LstBox.Requery I have to wait before clicking the message box all the time or else subsequent ones won't display; the Form.Refresh still works better than the LstBox.Requery.

    I should also mention that this delay problem has been plaguing me for months in other places as well. I have been here before talking to Charlotte and trying various things. There are several places in my system where it seems to take 3 to 5 seconds for data to be posted to my BEs. I have tried all manner of Database.Idle and DoEvents and Refresh and Requery. At those points where these delays occur, the *only* reliable way of making things work properly is for the user to just stop and wait for 5 seconds or so. This behavior exists on a couple computers I have available, one a 500MHz with W98SE, the other a 800Mhz with W2K. All of this is with the BEs on the same harddrive. In one abbreviated test where I put the BEs out on a network server, things got uglier.

    Charlotte warned me about mixing DAO and ADO. I'm wondering if that is behind it. I am opening ADO connections when the app starts and leaving them open until I exit. I know that I am using some DAO procedures along the way. Do you think I need to go back and restructure everything so that I only connect and open recordsets when they are needed?

    I wanted to build this thing with ADO so that it would be easier to change BE engines in the future, but I had trouble along the way getting the functionality I wanted without dipping into DAO.

    Thanks,
    Donald

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

    Re: Delayed response (A2K)

    >>I should also mention that this delay problem has been plaguing me for months in other places as well. <<

    That might have been helpful to know. Knowing it is a timing issue is at least half the problem. Unfortunately, I really don't have the experience and/or knowledge to determine how your mixing of ADO and DAO will affect things. I'm assuming this is Access2000, so the only thing I can suggest is check the MSKB for any A2000 performance issues.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Delayed response (A2K)

    That wasn't what my warning was about. You can use both DAO and ADO in a database, you just don't want to mix them in the same routine.

    You're using ADO to update a recordset but I didn't understand this remark
    <hr>When the system is started, ADO connections are established to the BEs<hr>
    Is this an mdb? Are you using linked tables? If so, then independent ADO connections don't really buy you much and with Jet, they tend to be slow. I don't see anything in your code that sets the activeconnection property of the recordset, but I assume you're setting it to an existing connection object. Using the CurrentProject.Connection would be the fastest connection for linked tables, but I can't tell if that's what you're doing.

    I've run into timing issues when updating recordsets like this. apparently because the *form* is using a DAO recordset, and it plays by different rules. Refresh will not serve the purpose. It refreshes the form's records, but it doesn't requery the underlying source.

    The problem you're having with errors is caused by ADO errors, which belong to the connection, and not to Jet. You have to trap ADO errors separately, and you test for an error by checking the count of the connection object's errors collection. Then you loop through them to see what you have and then issue a connection.errors.clear to clear them, because there's no automatic clearance of ADO errors.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delayed response (A2K)

    Woof!!

    You've given me a *LOT* to study in this reply.

    Briefly, I have an FE with 3 BEs. The FE is being tested as an mdb but will be deployed as an mde. The BEs will remain password protected mdbs. I have all the BE tables linked in the FE but was also opening an ADO connection to each BE at startup and leaving them open until final exit. Then in the code in question here I ended up using *another* connection, this time CurrentProject.Connection.

    Also, I did not have a *clue* that ADO errors had to be handled separately from the regular "On Error Goto ..." stuff.

    Ah, the messes created when you teach yourself as you build . . .

    Thanks for your help. I guess I better go back and do some house cleaning.

    Donald

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

    Re: Delayed response (A2K)

    No, no. The On Error Goto still works, but you have to test for the ADO errors as well as the Jet/System errors because you can get an ADO error that won't trigger a Jet/System error value in the err object.

    Actually, I've taught myself to program about 4 programming languages (including VBA) over the years and I can read, understand and pick apart several more. The devil is in the details. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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