Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timing problem (A2K)

    In my A2K FE/BE system I am having a timing problem.

    I display some records on a subform in datasheet view with only one field available for change, that being a checkbox. The user clicks the records that should be processed. After all the required records are checked a commandbutton is clicked to "Process" the selected records.

    The problem is that there is a (to me) undetermined time lag between the records being checked on the sub form and the underlying table in the BE being updated. For example, if I click one record's checkbox and then click the "Process" button, the 'process' routine will report that there are no records to process. If I click the checkbox and wait for a few seconds before clicking "Process" the record will be found.

    How can I teach my "Process" routine to wait for the right amount of time? That is, how can I tell when all the changes made to the BE tables are complete?

    I posted this question a while back and received a suggestion of putting in a DoEvents, but that didn't work.

    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: Timing problem (A2K)

    I find this odd. Given that marking the checkbox should make that record "dirty", when is it being written? Are you doing a RunCommand acCmdSaveRecord in your Process code?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Timing problem (A2K)

    Are you using ADO or DAO code in your Process routine? If you're using DAO, are you using CurrentDb or DBEngine(0)(0)? If it's the latter, then it's a refresh issue and can probably be cured (or largely cured, it depends on your actual code) by switching to the CurrentDb reference.
    Charlotte

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

    Re: Timing problem (A2K)

    Well, I'm not really sure when it's being written.

    The parent form has several optional fields that are read programmatically and an SQL statement is created. The SQL is fed to an ADO connection and the resulting recordset is fed to the subform recordset. When you click on a checkbox on the subform, the change "appears" in the table in the backend, but there is a delay.

    Right now, the backends are in a subdirectory of the directory that holds the mdb, but I can see a time when they will be on a LAN and shared. I assume the timing problems will amplify then.

    The Process code is going out to look for records with checkbox=true; the problem is that it is getting there before the checkboxes do.

    Donald

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

    Re: Timing problem (A2K)

    Hi, Charlotte,

    The problem is not in the Process routine. The problem is that it takes some amount of time for the check on the subform to get into the backend and I can launch the Process routine before they get there.

    I am using ADO in all this.

    I guess I need to detect that a checkbox has been changed and "rush" that to the backend, but I don't know how to do that at this point.

    Donald

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

    Re: Timing problem (A2K)

    In that case, post the code involved. It's nearly impossible to troubleshoot ADO code without seeing it because there are so many different ways to do the same thing.
    Charlotte

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

    Re: Timing problem (A2K)

    My question is, when you click on the checkbox for a specific record, when is this record being written? That is, if the record selectors are active, you will see the little pencil mark indicating the record has been changed. At what point are you telling Access to write this info back to the table?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Timing problem (A2K)

    That is precisely the question. I do not actively command that the record be written back. It just happens. Access is helping me here but I'm not sure how or when.

    If I check a record the little pencil appears on that line. If I check the next record, the little pencil disappears on the first line and appears on the second. But if I quickly hit my Process button, I can still get a report that nothing has been checked in the BE table. If I wait 3 to 5 seconds and then hit Process, at least one of the records will be found (I haven't checked if they're going over one by one.)

    In my testing, it seems that the AfterUpdate event doesn't fire until focus leaves the row, so I think the Checkbox_Click may be where I need to force the write. But that leads to the next problem: what is the command to force the write? As I said elsewhere, I build an SQL string, create an ADO recordset, and set that recordset to the subform.recordset. As I understand it, this is "Unbound" territory and a Requery won't work . . . or will it? What object do I requery?

    So many questions . . .

    Thanks for your help.
    Donald

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

    Re: Timing problem (A2K)

    Quite frankly, I'm at a lose to explain this. I'm not aware of any delays in writing information back to the table, only in getting refreshed information from the table into a recordset.

    My only suggest is to put this code in the beginning of your Process button code:
    If Me.Dirty = True then
    RunCommand acCmdSaveRecord
    DoEvents
    End if
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Timing problem (A2K)

    Access forms bound to ADO recordsets are not updateable in Access 2000 unless there's a major hot patch out that I haven't heard about, so you aren't using an ADO recordset for this.

    The fact that you see the edit icon (pencil) when you're in the record says that this is a DAO recordset. In that case, take Mark's advice and use the RunCommand acCmdSaveRecord in the Click event of the button to save the current record before you try to run your Process event. That will force a write.
    Charlotte

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

    Re: Timing problem (A2K)

    Still having the timing problem. Here are some code snips.

    The subform is built like this:

    ---- start code

    Set qry = CurrentDb.CreateQueryDef("qryLogComplete", strSQL)
    Set rst = qry.OpenRecordset

    With subfrmLogComplete
    Set .Form.Recordset = rst
    .Form.Visible = True
    End With

    ---- end code

    [Note to Charlotte: Now I understand that this is DAO and not ADO as I earlier stated.]


    When the Process button is clicked the code is this:


    ---- start code

    Private Sub cmdProcess_Click()

    Me.Refresh

    If CompletesExist Then
    ProcessCompletedEvents
    Else
    MsgBox "No records to process.", vbOKOnly, "Process Click"
    End If

    End Sub

    ---- end code

    The 'CompletesExist' function starts like this:

    ---- start code

    RunCommand acCmdSaveRecord
    DoEvents

    OpenEventsRequiredCompleteRecordset
    If rstEvntRqdCmplt.RecordCount > 0 Then
    CompletesExist = True
    Else
    CompletesExist = False
    End If

    ---- end code

    And finally, here is the OpenEventsRequiredCompleteRecordset code (with Error handlers stripped out):


    ---- start code

    Public Sub OpenEventsRequiredCompleteRecordset()
    Dim strSource As String

    strSource = "Select * from " & strEvntsRqrdTable & " where ysnCompleted = True"

    Set rstEvntRqdCmplt = New ADODB.Recordset
    rstEvntRqdCmplt.CursorLocation = adUseServer

    With rstEvntRqdCmplt
    .Open Source:=strSource, _
    ActiveConnection:=cnnEvntMngrDB, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockOptimistic, _
    options:=adCmdText
    End With

    End Sub 'OpenEventsRequiredCompleteRecordset

    ---- end code

    [Note: the connection is opened when the program is started and held open until the final exit.]

    Final comments:

    Even adding the RunCommand and DoEvents code does not solve the problem. If a subform is opened, one checkbox clicked, and the 'Process' button clicked, it will return saying that there are no records to process.

    If I wait a few seconds between clicking the checkbox and clicking 'Process', the record will be found.

    TIA
    Donald

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

    Re: Timing problem (A2K)

    Sorry, but I can't see why you're using the ADO routine at all. And a word of warning: mixing ADO and DAO routines can unexpectedly dump you all the way out of Access without so much as an error message, so be VERY careful when you call an ADO routine from a DAO routine or vice versa.

    ADO recordsets don't work quite the same way that DAO recordsets do, and if I remember correctly, they're asynchronous by default. Why don't you just open a DAO recordsetclone and do a findfirst to verify that the record exists? Alternatively, try using a client-side cursor and see if that makes a difference.
    Charlotte

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

    Re: Timing problem (A2K)

    Why the mix of ADO and DAO?

    <Rant mode=on>
    Because Access is such a cobbled together mess that when I go out trying to figure out how to do something and come across some code that looks right, I plug it in and go on. I have, as you can tell from this thread, no real idea of the separation between ADO and DAO. The d*** MSDN documentation glops everything together so that if you look there on a given topic you have to sort through Access1, Access2, Access97, Access2000, ADO, ADP, and DAO. And that's just if you remember keep it set to "Office Developer Documentation"; if you don't there's even more. The Access Help system is marginally useful because there is no table of contents that allows an organized way to search; all you can try to do is guess the word that describes what you want to do and sort through all the hits. Then if you don't find it, try to guess another word to search on. I've got nearly 30 years of programming experience but less than 2 with Access. At this point, I know the logic to do anything and just have to figure out the semantics of the particular language. Access' problem is that it speaks about 4 lanugages at the same time.
    <rant mode=off>

    I'll go back and revise my routines and try to stay in one method of connecting.

    Thanks,
    Donald

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

    Try the Code Librarian

    Do you feel better now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I agree that Office HTML Help is not ready for prime time, but it's nearly all we have so we're stuck with it. I've got about 25 years of programming, but 9 of that is in Access, so I have an unfair advantage--I've learned the language as it evolved! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Don't feel bad, though. ADO is a tough assignment for anyone because there are so many possible ways to do things, and none of them are really wrong. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> You just have to learn to compartmentalize all the lingo: ADO, DAO, SQL and Access UI. If you can keep them straight, you're not really <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>, just a mental gymnast.

    If you have the developer edition, the Code Librarian is a good place to go and search for code relating to a particular task. It willallow a text search, although its search engine is rudimentary; but it contains the code examples you would find in Help (if you could find them), so it can save you a lot of frustration. It also allows you to create your *own* Code Librarian library, so you can post just the code you want in that under whatever folders make sense to you. That's what I did, reserving the original for looking up stuff that I may or may not want to use and stuffing my custom library with my own code and code from other sources that is applicable to the development I do.

    It's OK to use both DAO and ADO, and I do because it's still necessary. Just be careful. I generally put my ADO code into one module, my DAO code into others, and have another module that holds nothing but stuff that is object model neutral. In class modules, I try to stick with one or the other. That way, I don't get too confused. Plus, I did learn the hard way that mixing the two can be dangerous. So I test everything and try not to mix calls in the same routine.

    Wherever possible, I have parallel routines in DAO and ADO that do the same thing so that I can call the version that is appropriate to the calling routines. Some things just can't be done in ADO, however, so DAO will be around for a while, at least.
    Charlotte

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

    Re: Try the Code Librarian

    Thanks for your sympathy and kind words. I *do* feel better now -- a good night's sleep helped too.

    The good news is that the last thing I did yesterday was to change the module that looks for checked records to DAO and initial tests indicated that the timing problem is gone. I went home with that moral victory so that I wouldn't dwell on the frustrating aspects of the situation. More testing today. And I guess I better comb through my code looking for other mixed usages.

    I don't believe that I have the Developer Edition or Code Librarian. How do I determine my edition and how do you invoke the code librarian?

    Thanks so much for your help.
    Donald

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
  •