Me.Refresh

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

Guest

I have a form based on complex query that has up to 20,000 records.
In this form i have a function that need to do refresh before it dose
something.
The Me.Refresh is very slow.
I need to refresh only the current record instade of al the recordset that
the form base on.
What can i do?
 
First, thanks for replay.
Is it mean that after i set it to false the Database will save the record
immediatly?
 
Allen, i have another quation:
I have a form base on this big table i talked about.
from this form i call a vba function that change (by "db.execute" sql
statement) one of the fields in the current record that shown in the form.
to reflect the changes i use: forms![frm1].refresh and as i said it is very
very slow.
I can't use here ".dirty" because the changes made by the function and not
by the form.
Do you have another way to reflect the changes only to this specific record
in the form?
 
Why write directly to the table instead of changing the value in the control
on the form?

Perhaps this query is not updatable, so you have no alternative. The example
below is aircode so you will need to debug it. It assumes a numeric primary
key named ID. It saves the value into a variant. After executing your update
query, it finds that record again.

Dim varID as Variant
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
varID = Me.[ID].Value 'Save the primary key value, to find later.
'your execute query here.
Me.Requery 'Requery the form.
With Me.RecordsetClone
If .RecordCount > 0 Then
If IsNull(varID) Then 'Must have been a new record.
RunCommand acCmdRecordsGotoNew
Else 'Find the key value we had before.
.FindFirst "ID = " & varID
If .NoMatch Then
MsgBox "Disappeared."
Else
Me.Bookmark = .Bookmark
End If
End If
End If
End With

Note that if the form is based on a non-updatable recordset and there are no
records, the detail section of yor form will go completely blank, and the
attempt to assign the value of the non-existent text box (line 5) will fail.
 
i use a function instad of updating in the form because of two reasons:

1. capsulation. i m doing a lot of database manipulations that needed also
from other forms so i separat the actions from a specific form to a module .

2. i needed the "commit"/"roolback" functionality.

Your example dosn't help me because my situation is different.
The form is reservation form and it's show the money balance.
The user can make invoice from this form. when he press "Invoice" i open a
modal form and he can write the amount and press "OK".
after he press the OK i calculate different thins in the database and
sumerize all the invoices and write it to the field in the reservation and
then i close the modal Invoice form but i need to refresh the reservation
form to reflect the new sum of invoices.
Long story ha? :-) now i need to find faster way than "form.refresh" to
refresh only this reservation record.

Allen Browne said:
Why write directly to the table instead of changing the value in the control
on the form?

Perhaps this query is not updatable, so you have no alternative. The example
below is aircode so you will need to debug it. It assumes a numeric primary
key named ID. It saves the value into a variant. After executing your update
query, it finds that record again.

Dim varID as Variant
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
varID = Me.[ID].Value 'Save the primary key value, to find later.
'your execute query here.
Me.Requery 'Requery the form.
With Me.RecordsetClone
If .RecordCount > 0 Then
If IsNull(varID) Then 'Must have been a new record.
RunCommand acCmdRecordsGotoNew
Else 'Find the key value we had before.
.FindFirst "ID = " & varID
If .NoMatch Then
MsgBox "Disappeared."
Else
Me.Bookmark = .Bookmark
End If
End If
End If
End With

Note that if the form is based on a non-updatable recordset and there are no
records, the detail section of yor form will go completely blank, and the
attempt to assign the value of the non-existent text box (line 5) will fail.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

yaya said:
Allen, i have another quation:
I have a form base on this big table i talked about.
from this form i call a vba function that change (by "db.execute" sql
statement) one of the fields in the current record that shown in the form.
to reflect the changes i use: forms![frm1].refresh and as i said it is
very
very slow.
I can't use here ".dirty" because the changes made by the function and not
by the form.
Do you have another way to reflect the changes only to this specific
record
in the form?
Click to expand...
Click to expand...
 
If you are serious about performance with the approach you are taking, you
will have to set the RecordSource of the form so it loads only one record.
Then when you Requery the form, it has only one record to reload, and no
find is needed.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

yaya said:
i use a function instad of updating in the form because of two reasons:

1. capsulation. i m doing a lot of database manipulations that needed
also
from other forms so i separat the actions from a specific form to a module
.

2. i needed the "commit"/"roolback" functionality.

Your example dosn't help me because my situation is different.
The form is reservation form and it's show the money balance.
The user can make invoice from this form. when he press "Invoice" i open a
modal form and he can write the amount and press "OK".
after he press the OK i calculate different thins in the database and
sumerize all the invoices and write it to the field in the reservation and
then i close the modal Invoice form but i need to refresh the reservation
form to reflect the new sum of invoices.
Long story ha? :-) now i need to find faster way than "form.refresh" to
refresh only this reservation record.

Allen Browne said:
Why write directly to the table instead of changing the value in the
control
on the form?

Perhaps this query is not updatable, so you have no alternative. The
example
below is aircode so you will need to debug it. It assumes a numeric
primary
key named ID. It saves the value into a variant. After executing your
update
query, it finds that record again.

Dim varID as Variant
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
varID = Me.[ID].Value 'Save the primary key value, to find later.
'your execute query here.
Me.Requery 'Requery the form.
With Me.RecordsetClone
If .RecordCount > 0 Then
If IsNull(varID) Then 'Must have been a new record.
RunCommand acCmdRecordsGotoNew
Else 'Find the key value we had before.
.FindFirst "ID = " & varID
If .NoMatch Then
MsgBox "Disappeared."
Else
Me.Bookmark = .Bookmark
End If
End If
End If
End With

Note that if the form is based on a non-updatable recordset and there are
no
records, the detail section of yor form will go completely blank, and the
attempt to assign the value of the non-existent text box (line 5) will
fail.

yaya said:
Allen, i have another quation:
I have a form base on this big table i talked about.
from this form i call a vba function that change (by "db.execute" sql
statement) one of the fields in the current record that shown in the
form.
to reflect the changes i use: forms![frm1].refresh and as i said it is
very
very slow.
I can't use here ".dirty" because the changes made by the function and
not
by the form.
Do you have another way to reflect the changes only to this specific
record
in the form?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top