update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have created a command button that runs an update query and marks all of
the records in a subset. This works but is very slow. I'm not familiar
with Visual Basic, but tried the following code which does not work:

Current Db.Execute ("AryUpdSelectAllRecords"),dbFailOnError

Thanks for any suggestions.
 
Current Db.Execute "AryUpdSelectAllRecords",dbFailOnError

Is the query AryUpdateSelectAllRecords or is is QryUpdateSelectAllRecords?
 
The query is QryUpSelectAllRecords. Sorry for the typo. I tried your syntax
(with correct typing) and got "Compile error, expected end of statement".
 
Speaking of typos, did you correct mine. I had a SPACE between Current and
DB. That should have been all one word.

CurrentDB().Execute "QryUpdateSelectAllRecords", dbFailOnError
 
I did have it with the space, so it works now. I still think the update runs
slow. Is there anyway I could speed up this process?
 
Perhaps. But since you did not post the query or table structures it is
difficult to say.

Do you have indexes set for the relevant fields?

I'm guessing that you are updating ALL the records in the table based on
your query title. That is an unusual thing to do and if there are a lot of
records involved this could take some time. Are you using any VBA functions
in the query? IF so, post them also.
 
What I am doing is having the user select a custom record set based on
his/her custom criteria. The records that are selected are appended to a
temporary table which becomes the record source for the form that they are
viewing. In this form the user is able to mark the records that he/she wants
included in a report. I have a field on this form called MarkRecord.
(Yes/no field that I have had indexed and not indexed). I have created a
command button SelectAll which when selected runs my update query (which was
created in the query grid--update query, updates MarkRecord to -1 in the
append table). When I click this command button, the first record in the
form is marked right away, then there is a considerable delay before the
other records are marked. This happens even when my AppendTable is holding
only 3 records. I have a delete all records button which works very fast to
empty my temporary table.
 
Ok, let me restate what you have said, so I understand it.
You have a temporary table (TblTemp) with a selection of records in it.
You have a form (FrmSelect) bound to tblTemp
You run a query to set all tblTemp.MarkRecord fields to True
After you run the query, it takes some time (seconds/minutes?) for frmSelect
to show the update to the field MarkRecord.

In the code that does this, do you requery or refresh the form to force it
show the changes immediately? I would expect to see something like the
following

Private Sub btnSELECTALL_Click()

'Your current code to set MarkRecord to true for all records

Me.Requery 'Requeries all the records in the table
'Or optionally
Me.Refresh

End Sub

If this doesn't work for you please POST your code and post the SQL
statement you are using.

If your query is a saved query
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
Back
Top