Problem with Basic Use of Requery

[BobbyGraves]

Hi, I'm just getting back into Access programming after a very long time (not that I was ever an expert). I'm using Access 2007 on a Windows 7 Pro installation.

First off, let me just say I have tried to look for solutions to this problem. I see many threads with all the right keywords, but they all seem to be complicated solutions to more complicated problems. My requirement is simple and, I would expect, common. I just can't believe a program would be allowed out the door that didn't have an elegant solution for it.

At this point, all my app does is clear out a table for which a datasheet is linked. I'm having the darndest time getting the datasheet not to show a bunch of "#Deleted" after the table is cleared and the datasheet is requeried. It seems to take a while before I can call that method and have it work properly. I've made an ugly work-around hack that sort of gets around it. It's just a wait-type function that puts execution into a loop for 5 seconds (anything less rarely works). At that point, the Requery method does its job. Here it is:

-----------------------------------------------

Option Compare Database

Public dbSpecReview As Database
Public rsTravOutQuery As DAO.Recordset

-----------------------------------------------

Private Sub Form_Load()

Set dbSpecReview = OpenDatabase("C:\MyFolder\MyDatabaseName.mdb")

'clear out the table
ClearQueryTable

'work-around hack
WaitForAccess 5

'requery datasheet (source = the table that was cleared)
Me.TravOut_Datasheet.Requery

End Sub

-----------------------------------------------

Public Sub ClearQueryTable()

Set rsTravOutQuery = dbSpecReview.OpenRecordset("MyTableName", dbOpenTable)

Do While Not rsTravOutQuery.EOF
rsTravOutQuery.Delete
rsTravOutQuery.MoveNext
Loop

End Sub

-----------------------------------------------

Public Sub WaitForAccess(iSeconds As Integer)
Dim tNow As Date
Dim tThen As Date

tNow = Time
tThen = DateAdd("s", iSeconds, Time)

Do Until tNow >= tThen
tNow = Time
Loop

End Sub

-----------------------------------------------

Pretty simple, right? Here are my questions:

1) Is there something obvious that I'm missing? By the way, I have tried resetting the source of the datasheet before the Requery call, as one post I read suggested.

2) I take it code execution does not pause while the record is being updated? Is there any event or mechanism I can use that will allow me to properly time the Requery method? My work-around hack lacks reliability unless I set it to a crazy-high wait period.

3) Would you advise me to go ADO instead of DAO? I gather it's faster. Does it provide a method, event or some other kind of mechanism to make a more elegant solution?

Thank you kindly for your consideration.
-Bobby

 

[pbaldy]

That's a horribly inefficient way to delete all the records. Try this instead:

CurrentDb.Execute "DELETE * FROM MyTableName"

 

[BobbyGraves]

Yeah, you'd think so, but with the SQL method, I have to set the wait timer to 6 seconds.

 

[BigHappyDaddy]

Pbaldy is right. Here is a procedure I use:

Public Sub EmptyTable(ByVal strTableName As String)
    If TableExists(strTableName) Then
        DoCmd.RunSQL "DELETE " & strTableName & ".* FROM " & strTableName & ";"
    End If
 
End Sub
 
Public Function TableExists(tblName As String) As Boolean
    TableExists = False
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then
        TableExists = True
    End If
 
End Function

Then you can EmptyTable ("MyTableName") in your main procedure.

 

[mdlueck]

1) Is there something obvious that I'm missing? By the way, I have tried resetting the source of the datasheet before the Requery call, as one post I read suggested.

Sounds like perhaps you have already seen this post.

Must update RecordSource in order for Refresh to actually work
http://www.access-programmers.co.uk/forums/showthread.php?p=1091968#post1091920

I do not see you calling the form Refresh event, though.

In my application records get added / refreshed in the Access FE table, and I use the described method in order to get the Form UI to realized that data base changed in the table. I find it not necessary to fire Requery as the data is already in the table, I just need the Form to realize that data has changed. This sounds similar to what you face.

Requery would be used, for example, when you bind a Form to a Query and you want to rerun the query. I believe calling Refresh in this scenario would only resync the UI with the last known state of the query's output.

2) I take it code execution does not pause while the record is being updated? Is there any event or mechanism I can use that will allow me to properly time the Requery method? My work-around hack lacks reliability unless I set it to a crazy-high wait period.

What in the world are you asking here? Think of Access as single threaded. If VBA code is updating a table, then the UI gets "paused" while the update happens. Again, call Refesh to update teh UI with the current contents of the data source as Access knows about it based on the last check.

For code which performs lots of DB actions, I sprinkle in calls to:

'Keep UI responsive
DoEvents

in order to keep the UI responsive and refreshed.

3) Would you advise me to go ADO instead of DAO? I gather it's faster. Does it provide a method, event or some other kind of mechanism to make a more elegant solution?

I prefer ADO objects when possible to use. ADO objects are good for when I need query results to end up in VBA variables. I use DAO objects when I want the results in an Access table.

The gem of ADO is as follows:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Now, if you are strictly using Access tables, then I believe you can perform SQL which will not end up with the results in VBA variables. I work predominately in Client/Server, executing Stored Procedures, executing Pass-Through queries, and other such details and with Client/Server there is a pretty firm divider that I must use DAO to have the results end up in an Access table.

 

[Pat Hartman]

I use the following code. My list is fairly short so I am not running into timing issues. If the delete query takes sometime to complete, you need to force Access to wait before executing the requery.

An alternative you might try is to change the subform's RecordSource to a query that returns no records. That will make the records disappear immediately. Then you can run the delete, make Access wait a little bit, then replace the RecordSource with the normal query. Make sure you use the hourglass to indicate that something is happening so your user doesn't start clicking madly. A visible wait message wouldn't hurt either.

Private Sub cmdEmptyList_Click()
On Error GoTo Err_cmdEmptyList_Click

    Dim stDocName As String

    DoCmd.RunMacro "mWarningsOff"
    stDocName = "qEmptySelectList"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Me.Requery

Exit_cmdEmptyList_Click:
    DoCmd.RunMacro "mWarningsOn"
    Exit Sub

Err_cmdEmptyList_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_cmdEmptyList_Click
End Sub

For those of you who follow my posts, this is the ONLY time I ever use macros in an application I create for other users. The warnings off macro displays the hourglass and the warnings on macro turns it off. This is for my own sanity since it is EXTREMELY dangerous to turn warnings off. The hourglass gives me a visual in case my code is interupted and the warnings are not properly restored.

 

[BobbyGraves]

Thank you, Michael, for your comprehensive response.

I do not see you calling the form Refresh event, though.

At one point, I believe I was using every available refresh method (refresh, repaint, requery) at both the form and datasheet level. I will try that again just to be 100% sure.

What in the world are you asking here? Think of Access as single threaded. If VBA code is updating a table, then the UI gets "paused" while the update happens. Again, call Refesh to update teh UI with the current contents of the data source as Access knows about it based on the last check.

Well, if I run Requery immediately, it doesn't work. First, the datasheet shows the old records and then once it gets focus, it changes to a bunch of "#Deleted". On the other hand, if I pause for 5 seconds, Requery works just fine. To me, this would indicate that the data sources are more or less set up right (otherwise it wouldn't work at all) and it suggests that VB does not wait while the table is being cleared. What's more puzzling is why it takes at least 5 seconds. That's a century by modern PC standards. And it's not a network thing. My database is on the local C drive.

For code which performs lots of DB actions, I sprinkle in calls to <DoEvents> in order to keep the UI responsive and refreshed.

I hadn't considered DoEvents. That's promising for a solution. I will try that and go over your linked posts about ADO (hopefully) this afternoon.

Thanks again.
-Bobby

 

[Pat Hartman]

Refresh updates records that already exist in the form's RecordSource. It will not pick up additions and it may show #deleted# for rows that were deleted. Requery reruns the query and so picks up everything.

 

[BobbyGraves]

Thanks Pat. I'll give that code a try. I might even have to re-learn about queries. Darn it, I was hoping to knock out this simple app w/o having to study too much.

I'll post back when I've had time to dig through this all. It's kind of a side-project, but I appreciate everyone's feedback.

-Bobby

 

[mdlueck]

at both the form and datasheet level.

I keep seeing you write "datasheet". What do you mean by that?

Is it a Form?

Is it opening the bare Table? (Which has no VBA event capabilities, BTW.)

From Google, I get the impression that it is a Table view component which may be placed on a Form. Is that what you are talking about?

I have not specifically placed a datasheet onto a Form I have developed. I use the Multiple Items create Form button instead, which does the work of setting up the Multiple Items form.

For multiple grids, I use Subforms which are themselves Multiple Items forms. Then load up those Multiple Items Subforms onto the master Form.

 

[BobbyGraves]

Hi again,

I keep seeing you write "datasheet". What do you mean by that?

I don't exactly recall where/when I picked up that term, but I've been using it ever since...LOL. It would probably be more accurate to call it a subform or subreport. I'm loathe to use wizards when I'm learning (or in this case, re-learning) something. I want to to know that there's nothing in there that I didn't specifically generate. Plus, it allows me to learn things as I need them. Anyway, I made it using the "subform/subreport" control button. From there, I simply set the "Source Object" to the appropriate table (from a dropdown list).

Is it opening the bare Table? (Which has no VBA event capabilities, BTW.)

I'm not sure what you mean by that. Hopefully, my description on how I set it up will answer your question. I can tell you that the only options I see for events in the VBA module for that control are Enter and Exit.

It sounds like the problem I'm having is not particularly common and no one has pointed out any catastophic problems with my code, so that leads me to believe that the problem is with how I set up the form. The question is why is it just kind of broken and not all the way broken. Preferably without using wizards, is there a better way to set it up? I'll tell you, I'm having the hardest time finding info on the most basic and fundamental concepts of Access development. My google searches lead to endless hits, but virtually nothing that touches on core concepts. Likewise, I haven't figured out a successful strategy for navigating the in-app help system. I've seen references to "Source Object" being linked to specific fields and also to other forms and reports. I've yet to see an example where it is linked to an entire table the way I am.

Thanks again for your interest. I'll keep digging, but I'd appreciate any suggestions.

 

[BigHappyDaddy]

I don't exactly recall where/when I picked up that term, but I've been using it ever since...LOL. It would probably be more accurate to call it a subform or subreport. I'm loathe to use wizards when I'm learning (or in this case, re-learning) something. I want to to know that there's nothing in there that I didn't specifically generate. Plus, it allows me to learn things as I need them. Anyway, I made it using the "subform/subreport" control button. From there, I simply set the "Source Object" to the appropriate table (from a dropdown list).

If the #Deleted is showing up on the subform then you need to execute a requery/refresh on the subform as well. This might be your issue.

 

[BobbyGraves]

If the #Deleted is showing up on the subform then you need to execute a requery/refresh on the subform as well. This might be your issue.

TravOut_Datasheet is the subform...

Me.TravOut_Datasheet.Requery

 

[BobbyGraves]

Nevermind, I worked it out. The problem wasn't with the subform's source object; it was the form's source object. I figured it was something basic and fundamental. Still, I'm not sure why a form needs a source object when the only linked control on it has one and I call on its Requery method. Furthermore, why does it suddenly start working properly after a 5 second delay?

Anyway, thanks to everyone for their efforts.

 

[mdlueck]

It would probably be more accurate to call it a subform or subreport.

MUCH better. I am tracking with you now.

I'm not sure what you mean by that.

I meant Navigation Pane \ Tables \ and open one up. Tables themselves can not have VBA code wired to events.

Subforms (multiple records type) can have events with VBA wired to them \ or VBA firing events.

 

[mdlueck]

TravOut_Datasheet is the subform...

Example of refreshing a subform on this post:

Relocate Previously Selected Record in a Multiple Items Form
http://www.access-programmers.co.uk/forums/showthread.php?p=1178436#post1178436

 

[BobbyGraves]

Well, I had it working for an afternoon. Then it stopped working yesterday. I had made some changes to the form (mostly just to clean it up), but the Source Object property is still set for both the form and the subform. So, now I don't know what to think.

Example of refreshing a subform on this post:

Relocate Previously Selected Record in a Multiple Items Form
<link which I can't republish>

Please explain. I can't make the abstraction between locating a previously selected record and getting a requery to work in a timely manner. In fact, there are no calls to any kind of refreshing method. I have tried with a call to SetFocus on the control before requerying, but this is not a case of a form within a subform. It's simply a subform within a form.

Thnx

 

[BobbyGraves]

Okay, I got it working again.

Lesson learned: The Option Group control is not a frame. If you want to use it like a frame, set Enabled to False.

 

[BobbyGraves]

This was a real puzzler. Like 5 times I made a change that seemingly fixed the problem. I would save and test it. Then I would close everything down and go home happy. But I'd come back to work the next day and the delay problem would resurface. For the last few changes, I immediately closed down Access and when I reopened, the problem was there again.

The changes I made were varied. I reset the source for the form. I disabled the Option Group control that I was using to frame the subform. I added another subform. And a couple others I can't remember. In many cases, I can't make any connection to how the change would affect my problem.

Now I finally made a change that seems to be sticking (*fingers crossed*). In code, I changed:

Set dbSpecReview = OpenDatabase(skSR_DATABASE_PATH & skSR_DATABASE_NAME & ".mdb")

...to...

Set dbSpecReview = CurrentDb

It's the same database, which is on my local C drive. So,...

1) Does it make sense that the original way I made the connection was causing my problem (needing to pause execution for 5 secs before running Requery/Refresh)? I mean, either way, it returns the same pointer, right?

2) Anyone care to take a crack at explaining the bizarre behavior I was experiencing with the first several "fixes" that didn't stick beyond the current session? It might be worth noting that I sometimes get the, "The database has been placed in a state by user “Admin” on machine X" message as I'm developing. The machine name is mine. It hasn't really been an issue. Mostly, I just get it when I try to use a wizard. Also, it's a Win7 install running Access 2007 and my account is listed under administrators.

I'd be grateful for any insight. It might help prevent problems moving forward. Plus, it will help satisfy my morbid curiosity.

Cheers and thanks again to everyone who has participated.

 

[mdlueck]

2) Anyone care to take a crack at explaining the bizarre behavior I was experiencing with the first several "fixes" that didn't stick beyond the current session?

Perhaps Access being Access.

I have one complicated form with two SubForms on it. Both SubForms are Multiple Items forms. With application use, that form will not finish firing all of the startup events until a blank spot is clicked on the form. Then the form and its subforms complete the events as they should have. I will run through my DB cleanup process:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

and the form will work properly again for some time. That is one reason I have Software Distribution code which ALWAYS refreshes the FE DB file when the application is started.