Timing problem (A2K)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Why the mix of ADO and DAO?

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.

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

Thanks,
Donald

Reply | Quote

Do you feel better now? 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! 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. 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 , 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.

Reply | Quote

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

Reply | Quote

There’s an additional set of disks that come with the Developer Edition. It contains Developer Tools, plus SQL Server (not MSDE), HTML Help workshop, and others I can’t recall at the moment. You can actually buy the developer tools after the fact to upgrade your Office to developer edition. Anyhow, Code Librarian is an add-in that comes with it.

Reply | Quote

Is there a Developers Edition of Access or are you referring to the “Microsoft Office 2000 Developer Tools”? My company has a full blown MSDN subscription but we can’t figure out which disks you are referring to.

Can you give me a reference on MSDN or a part number or something?

Thanks.
Donald

Reply | Quote

The Developers Tools are part of Office Developer. If you have an MSDN Universal subscription, they should be included and you should be able to find them through whatever indexes they have. If you have any *other* MSDN subscription, the developer tools aren’t included.

I don’t have the Universal subscription yet, so I can’t be much help with their disks. The disks I was referring to are the disks that come with MOD. One disk is actually labelled “Microsoft Office 2000 Developer” and usually comes in its own jewel case.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote