Refreshing sub Form after executing a stored Proc.

  • Thread starter Thread starter John316
  • Start date Start date
J

John316

Hello....
I have been combating this same problem for some time now.

The problem is this.
--I provide the end user access to a form that displays existing data.
--I allow them to double click a control to change the data.
--The event proc sends params to a stored proc that executes and updates the
table.

Now what I'd like is for the form to display the update.
If I use the requery, the cursor resets back to the 1st record.
It is not acceptable to keep scrolling back down to continue updating.

I've tried everything from refresh to requery and always get the same
result.

How can I refresh the record with out going back to the 1st record.


I usually wind up providing a "Refresh" button that basically executes a
form.requery....
after all the updates are done. the downside is that the user can not see
the updates
as he or she is doing them.

any help would be much appreciated.

thanks in advance,
bob mcclellan
 
What you are looking for is probably Me.Recordset.Resync . However, the
whole recordset will be reexamined, not just only the current record, so it
may take some time.

You can also store the current bookmark somewhere and go back to it after
your requery. (Use the value of the primary key for the current record
instead if the bookmark doesn't work.)

Finally, you can update the values yourself by making a direct call to the
SQL-Server and changing/updating the values for the current record.
However, it will be marked as dirty, so you may have to issue the following
command:

If (Me.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
End If

If you want a sophisticated refresh of your form, then maybe you should use
an unbound form instead and fill it with whatever you want.

S. L.
 
Thanks Sylvain..
I will explore the 3 options you've suggested.
-I was unaware of .Resync and will explore this thoroughly.
-In your suggestion of going back to a bookmark or back to the PK for the
current record....
how do you do that? What is the code to bring the cursor back to that
record?
-I will also explore the 3rd option you've suggested. The recordset is not
updateable so
it will be interesting to see if I can still change the value from code.

I will post the results but wanted to thank you now for taking the time to
reply.
I really appreciate it...

bob mcclellan.
 
Sylvain,------When trying this method I consistently get
Run-time error '-2147217885(80040e23)
Key value for this row was changed or deleted at the data store. The local
row is now deleted.
------When I use something like 'Me.Recordset.Find "JobNumber = " & j I
hit the right record sometimes and other times I land one record before ....
There is no consistency. I never use bookmarks and am unfamiliar with the
correct way to code for it. I Tried exploring it via the MSAccess Help as
well as
Google searchs but could not find anything that made it clear for me.


-----Just to test...
I added "Me.Leg = l"

to the end of the algorithm.
(The actual SQL record data is updated via the code provided after these
results)

I get :
Run-time error '2448'
You can't assign a value to this object.

----On all Forms that are Single-Form, that's exactly what I do.
.... but how would you do that on a DataSheet View...


Here is the Event Procedure code being used so that you see what I am
trying....

Private Sub Leg_ID_DblClick(Cancel As Integer)
'--bsp_Trans_TagThisLeg j,l,t
If IsNull(Forms![Battelini_Main_Deliveries].cbo_trucks.Column(0)) Then
MsgBox "Please Pick Select A Truck"
Exit Sub
End If

Dim j As Long, l As Long, t As Long
j = Me.JobNumber
l = InputBox("What Leg Should this be assigned to?", "Assign The Leg", 1)
t = Forms![Battelini_Main_Deliveries].cbo_trucks.Column(0)

If IsNumeric(j) = False Then
MsgBox "This line has no jobnumber"
Exit Sub
End If

If IsNumeric(l) = False Then
MsgBox "Please assign a valid numeric leg number "
Exit Sub
End If

If IsNumeric(t) = False Then
MsgBox "Please Select a Truck to work with...."
Exit Sub
End If


Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "bsp_Trans_TagThisLeg"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

'JobID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = j
param.Name = "j"
oCmd.Parameters.Append param

'Leg
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = l
param.Name = "l"
oCmd.Parameters.Append param

'Truck_ID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = t
param.Name = "t"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords

Me.Recordset.Resync
'--Me.Requery
'Me.Recordset.Find "JobNumber = " & j

End Sub


and
..................................................................................Here's
the sp............
Alter Procedure bsp_Trans_TagThisLeg

@j int, --jobnumber

@l int, --leg

@t int --truck_id


as

declare @c int --count of jobNumber.. does it exist in Trans_legs?

Select @c = count(Job_ID) from trans_legs where job_id = @j

if @c = 0

begin

Insert Into Trans_Legs (Job_ID, Truck_ID, Leg)

Select @j, @t, @l

end

else

begin

Update Trans_Legs set Truck_ID = @t, Leg = @l where Job_ID = @j

end
 
The message that the local row has been deleted is a real pain in the ass
under ADP.

To prevent this kind of error, it is best to define a primary key
(preferably in all of your tables but not necessarily), to set the Unique
Table property and to define Resync Command, taking the primary key field of
the Unique Table as its single argument.

For example, I have a stored procedure call qf_CalP_Edition wich take two
parameters:

CREATE PROCEDURE dbo.qf_CalP_Edition
(
@ComboIdOrganisme int,
@ComboIdLigue int
)
AS
SELECT * from Matchs inner join .....

The Matchs is the Unique table here and I have put its name in the Unique
Table property. (Often, you will not be able to set it under the GUI. In
this case, simply set this value in the OnLoad event for the form:

Private Sub Form_Load()
Me.UniqueTable = "Matchs"
End Sub
)

The resync command is defined as follow:

CREATE PROCEDURE dbo.qf_CalP_Edition_Resync
(
@IdMatch int
)
AS
SELECT Matchs.* from Matchs where Matchs.IdMatch = @IdMatch

And you set the Resync Command as follow:

qf_CalP_Edition_Resync ?

In the above case, I have made sure that the Record Source Qualifier is
always set to "dbo" (without the quote).

For the bookmarks, use something like:

Dim bkm As Variant
bkm = Me.bookmark

... Do your work here ...

Me.bookmark = bkm

If you want to make some searching, then it is better to perform on a copy
of the recordset and then use the bookmarks to synchronises these things;
something like:

With Me.RecordsetClone
.Find "[IdLigue]=" & Me!ComboIdLigue
Me.bookmark = .bookmark
End With

Of couse, you must add tests for the possibility of BOF, EOF, failure, using
the method .MoveFirst, etc.

Another bookmark that I found usefull is the predefined bookmark
LastModified. For example:

rsMatch("Jour") = AjoutJour
rsMatch("Heure") = AjoutHeure

rsMatch.Update
rsMatch.bookmark = rsMatch.LastModified
IdMatch = rsMatch!IdMatch

Hope that you will find this information usefull. Also, I cannot take the
time at this moment to take a full look at your piece of code because this
will requires that I mount a full test bed and I don't have the time for
these things at this moment.

Regards,
S. L.

John316 said:
Sylvain,------When trying this method I consistently get
Run-time error '-2147217885(80040e23)
Key value for this row was changed or deleted at the data store. The
local row is now deleted.
------When I use something like 'Me.Recordset.Find "JobNumber = " & j I
hit the right record sometimes and other times I land one record before
....
There is no consistency. I never use bookmarks and am unfamiliar with the
correct way to code for it. I Tried exploring it via the MSAccess Help as
well as
Google searchs but could not find anything that made it clear for me.


-----Just to test...
I added "Me.Leg = l"

to the end of the algorithm.
(The actual SQL record data is updated via the code provided after these
results)

I get :
Run-time error '2448'
You can't assign a value to this object.

----On all Forms that are Single-Form, that's exactly what I do.
... but how would you do that on a DataSheet View...


Here is the Event Procedure code being used so that you see what I am
trying....

Private Sub Leg_ID_DblClick(Cancel As Integer)
'--bsp_Trans_TagThisLeg j,l,t
If IsNull(Forms![Battelini_Main_Deliveries].cbo_trucks.Column(0)) Then
MsgBox "Please Pick Select A Truck"
Exit Sub
End If

Dim j As Long, l As Long, t As Long
j = Me.JobNumber
l = InputBox("What Leg Should this be assigned to?", "Assign The Leg", 1)
t = Forms![Battelini_Main_Deliveries].cbo_trucks.Column(0)

If IsNumeric(j) = False Then
MsgBox "This line has no jobnumber"
Exit Sub
End If

If IsNumeric(l) = False Then
MsgBox "Please assign a valid numeric leg number "
Exit Sub
End If

If IsNumeric(t) = False Then
MsgBox "Please Select a Truck to work with...."
Exit Sub
End If


Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "bsp_Trans_TagThisLeg"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

'JobID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = j
param.Name = "j"
oCmd.Parameters.Append param

'Leg
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = l
param.Name = "l"
oCmd.Parameters.Append param

'Truck_ID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = t
param.Name = "t"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords

Me.Recordset.Resync
'--Me.Requery
'Me.Recordset.Find "JobNumber = " & j

End Sub


and
.................................................................................Here's
the sp............
Alter Procedure bsp_Trans_TagThisLeg

@j int, --jobnumber

@l int, --leg

@t int --truck_id


as

declare @c int --count of jobNumber.. does it exist in Trans_legs?

Select @c = count(Job_ID) from trans_legs where job_id = @j

if @c = 0

begin

Insert Into Trans_Legs (Job_ID, Truck_ID, Leg)

Select @j, @t, @l

end

else

begin

Update Trans_Legs set Truck_ID = @t, Leg = @l where Job_ID = @j

end
Click to expand...
 
Thanks for the quick reply Sylvain.
I will explore and test the suggestions you've provided and let you
know how I've made out.

Thanks again,
bob mcclellan.

Sylvain Lafontaine said:
The message that the local row has been deleted is a real pain in the ass
under ADP.

To prevent this kind of error, it is best to define a primary key
(preferably in all of your tables but not necessarily), to set the Unique
Table property and to define Resync Command, taking the primary key field
of the Unique Table as its single argument.

For example, I have a stored procedure call qf_CalP_Edition wich take two
parameters:

CREATE PROCEDURE dbo.qf_CalP_Edition
(
@ComboIdOrganisme int,
@ComboIdLigue int
)
AS
SELECT * from Matchs inner join .....

The Matchs is the Unique table here and I have put its name in the Unique
Table property. (Often, you will not be able to set it under the GUI. In
this case, simply set this value in the OnLoad event for the form:

Private Sub Form_Load()
Me.UniqueTable = "Matchs"
End Sub
)

The resync command is defined as follow:

CREATE PROCEDURE dbo.qf_CalP_Edition_Resync
(
@IdMatch int
)
AS
SELECT Matchs.* from Matchs where Matchs.IdMatch = @IdMatch

And you set the Resync Command as follow:

qf_CalP_Edition_Resync ?

In the above case, I have made sure that the Record Source Qualifier is
always set to "dbo" (without the quote).

For the bookmarks, use something like:

Dim bkm As Variant
bkm = Me.bookmark

... Do your work here ...

Me.bookmark = bkm

If you want to make some searching, then it is better to perform on a copy
of the recordset and then use the bookmarks to synchronises these things;
something like:

With Me.RecordsetClone
.Find "[IdLigue]=" & Me!ComboIdLigue
Me.bookmark = .bookmark
End With

Of couse, you must add tests for the possibility of BOF, EOF, failure,
using the method .MoveFirst, etc.

Another bookmark that I found usefull is the predefined bookmark
LastModified. For example:

rsMatch("Jour") = AjoutJour
rsMatch("Heure") = AjoutHeure

rsMatch.Update
rsMatch.bookmark = rsMatch.LastModified
IdMatch = rsMatch!IdMatch

Hope that you will find this information usefull. Also, I cannot take the
time at this moment to take a full look at your piece of code because this
will requires that I mount a full test bed and I don't have the time for
these things at this moment.

Regards,
S. L.

John316 said:
Sylvain,
What you are looking for is probably Me.Recordset.Resync .
Click to expand...
------When trying this method I consistently get
Run-time error '-2147217885(80040e23)
Key value for this row was changed or deleted at the data store. The
local row is now deleted.
You can also store the current bookmark somewhere and go back to it
after your requery. (Use the value of the primary key for the current
record instead if the bookmark doesn't work.)
Click to expand...
------When I use something like 'Me.Recordset.Find "JobNumber = " & j I
hit the right record sometimes and other times I land one record before
....
There is no consistency. I never use bookmarks and am unfamiliar with
the
correct way to code for it. I Tried exploring it via the MSAccess Help
as well as
Google searchs but could not find anything that made it clear for me.

Finally, you can update the values yourself by making a direct call to
the SQL-Server and changing/updating the values for the current record.
However, it will be marked as dirty, so you may have to issue the
following command:

If (Me.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
End If
Click to expand...

-----Just to test...
I added "Me.Leg = l"

to the end of the algorithm.
(The actual SQL record data is updated via the code provided after these
results)

I get :
Run-time error '2448'
You can't assign a value to this object.

If you want a sophisticated refresh of your form, then maybe you should
use an unbound form instead and fill it with whatever you want.
Click to expand...
----On all Forms that are Single-Form, that's exactly what I do.
... but how would you do that on a DataSheet View...


Here is the Event Procedure code being used so that you see what I am
trying....

Private Sub Leg_ID_DblClick(Cancel As Integer)
'--bsp_Trans_TagThisLeg j,l,t
If IsNull(Forms![Battelini_Main_Deliveries].cbo_trucks.Column(0)) Then
MsgBox "Please Pick Select A Truck"
Exit Sub
End If

Dim j As Long, l As Long, t As Long
j = Me.JobNumber
l = InputBox("What Leg Should this be assigned to?", "Assign The Leg", 1)
t = Forms![Battelini_Main_Deliveries].cbo_trucks.Column(0)

If IsNumeric(j) = False Then
MsgBox "This line has no jobnumber"
Exit Sub
End If

If IsNumeric(l) = False Then
MsgBox "Please assign a valid numeric leg number "
Exit Sub
End If

If IsNumeric(t) = False Then
MsgBox "Please Select a Truck to work with...."
Exit Sub
End If


Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "bsp_Trans_TagThisLeg"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

'JobID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = j
param.Name = "j"
oCmd.Parameters.Append param

'Leg
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = l
param.Name = "l"
oCmd.Parameters.Append param

'Truck_ID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 4 ' 3 bytes
param.Direction = adParamInput
param.Value = t
param.Name = "t"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords

Me.Recordset.Resync
'--Me.Requery
'Me.Recordset.Find "JobNumber = " & j

End Sub


and
.................................................................................Here's
the sp............
Alter Procedure bsp_Trans_TagThisLeg

@j int, --jobnumber

@l int, --leg

@t int --truck_id


as

declare @c int --count of jobNumber.. does it exist in Trans_legs?

Select @c = count(Job_ID) from trans_legs where job_id = @j

if @c = 0

begin

Insert Into Trans_Legs (Job_ID, Truck_ID, Leg)

Select @j, @t, @l

end

else

begin

Update Trans_Legs set Truck_ID = @t, Leg = @l where Job_ID = @j

end
Click to expand...
Click to expand...
 
Sylvain....
Your example for the bookMark worked Great!
Thanks again for taking the time to help me with this.
I added this piece of code to show what was appended to the
sample code from the last post.

Now....
After I update the SQL data set,
I requery and go back to the bookMark.

Thanks for your patience....
It's truly appreciated...
bob mcclellan..


Dim bkm As Variant
With Me.RecordsetClone
.Find "[JobNumber]= " & j
bkm = .Bookmark
End With
Me.Requery
Me.Bookmark = bkm
 
Technically, you must make the search after the requery, as there are no
garantie that the bookmarks will be the same after the requery; which
recreate the whole recordset.

Also, it looks like that I have some sort of problem with .RecordsetClone on
Access 2002 after installing Sp2 for XP. No problem with Access 2003.
Access 2002 and 2003 are on the same machine, so maybe this is part of the
problem.

S. L.
 
Technically, you must make the search after the requery, as there are no
garantie that the bookmarks will be the same after the requery; which
recreate the whole recordset.
Click to expand...
---The bookmark lines up with thePK not the recordset BookMark
( .Find "[JobNumber]= " & j )

so after I requery, I then say take me back to the JobID (which is the PK)
So far it's doing what I want.
It's kind of like .ApplyFilter but instead of filtering,
you maintain the recset and jump to the rec you are working with.

I'm using Access2k with XP SP2.
As far as I can tell... all is working well.

Do you create run time apps for your end users with your Access2003?
(to nullify the need for your end users to have 2003)


Also, it looks like that I have some sort of problem with .RecordsetClone
on Access 2002 after installing Sp2 for XP. No problem with Access 2003.
Access 2002 and 2003 are on the same machine, so maybe this is part of the
problem.

S. L.

John316 said:
Sylvain....
Your example for the bookMark worked Great!
Thanks again for taking the time to help me with this.
I added this piece of code to show what was appended to the
sample code from the last post.

Now....
After I update the SQL data set,
I requery and go back to the bookMark.

Thanks for your patience....
It's truly appreciated...
bob mcclellan..


Dim bkm As Variant
With Me.RecordsetClone
.Find "[JobNumber]= " & j
bkm = .Bookmark
End With
Me.Requery
Me.Bookmark = bkm
Click to expand...
Click to expand...
 
The problem that I am having with A2002 is that after using a RecordsetClone
on some occasion, the form doesn't work well for edition; for example I must
click two times on checkbox to check it or the value -1 take place in
another field instead.

I have now another problem too: I have added a simple checkbox linked to a
bit field and the form is no longer working at all if I click on the
checkbox first: Access simply goes out with an internal error display. If I
modify other fields before using the last checkbox, no problem. Trying to
refresh/repair/compact/decompile, etc., no effect on this bug.

With Access 2003, no problem at all; so I have decided to not use A2002
anymore, at least on my machine.

As to the runtime, I always ask my clients to buy a full copy of Access, in
an attempt to have the less number of problems as possible.

S. L.

John316 said:
Technically, you must make the search after the requery, as there are no
garantie that the bookmarks will be the same after the requery; which
recreate the whole recordset.
Click to expand...
---The bookmark lines up with thePK not the recordset BookMark
( .Find "[JobNumber]= " & j )

so after I requery, I then say take me back to the JobID (which is the PK)
So far it's doing what I want.
It's kind of like .ApplyFilter but instead of filtering,
you maintain the recset and jump to the rec you are working with.

I'm using Access2k with XP SP2.
As far as I can tell... all is working well.

Do you create run time apps for your end users with your Access2003?
(to nullify the need for your end users to have 2003)


Also, it looks like that I have some sort of problem with .RecordsetClone
on Access 2002 after installing Sp2 for XP. No problem with Access 2003.
Access 2002 and 2003 are on the same machine, so maybe this is part of
the problem.

S. L.

John316 said:
Sylvain....
Your example for the bookMark worked Great!
Thanks again for taking the time to help me with this.
I added this piece of code to show what was appended to the
sample code from the last post.

Now....
After I update the SQL data set,
I requery and go back to the bookMark.

Thanks for your patience....
It's truly appreciated...
bob mcclellan..


Dim bkm As Variant
With Me.RecordsetClone
.Find "[JobNumber]= " & j
bkm = .Bookmark
End With
Me.Requery
Me.Bookmark = bkm
Click to expand...
Click to expand...
Click to expand...
 
Your problem with A2002 sounds like just enough to drive you crazy.
Is there another machine at your location that has 2002 & 2003 co existing
on the
same machine.. if so, are the results the same?

As far as marking the wrong records... I was experiencing some of that until
I changed from the row bookmark to the PK bookmark as per your suggestion.
This remedied the problem.

thanks for the update on the run time apps. also.

and ....
thanks again for all the help.
sincerely,
bob mcclellan.


Sylvain Lafontaine said:
The problem that I am having with A2002 is that after using a
RecordsetClone on some occasion, the form doesn't work well for edition;
for example I must click two times on checkbox to check it or the value -1
take place in another field instead.

I have now another problem too: I have added a simple checkbox linked to a
bit field and the form is no longer working at all if I click on the
checkbox first: Access simply goes out with an internal error display. If
I modify other fields before using the last checkbox, no problem. Trying
to refresh/repair/compact/decompile, etc., no effect on this bug.

With Access 2003, no problem at all; so I have decided to not use A2002
anymore, at least on my machine.

As to the runtime, I always ask my clients to buy a full copy of Access,
in an attempt to have the less number of problems as possible.

S. L.

John316 said:
Technically, you must make the search after the requery, as there are no
garantie that the bookmarks will be the same after the requery; which
recreate the whole recordset.
Click to expand...
---The bookmark lines up with thePK not the recordset BookMark
( .Find "[JobNumber]= " & j )

so after I requery, I then say take me back to the JobID (which is the
PK)
So far it's doing what I want.
It's kind of like .ApplyFilter but instead of filtering,
you maintain the recset and jump to the rec you are working with.

I'm using Access2k with XP SP2.
As far as I can tell... all is working well.

Do you create run time apps for your end users with your Access2003?
(to nullify the need for your end users to have 2003)


Also, it looks like that I have some sort of problem with
.RecordsetClone on Access 2002 after installing Sp2 for XP. No problem
with Access 2003. Access 2002 and 2003 are on the same machine, so maybe
this is part of the problem.

S. L.

Sylvain....
Your example for the bookMark worked Great!
Thanks again for taking the time to help me with this.
I added this piece of code to show what was appended to the
sample code from the last post.

Now....
After I update the SQL data set,
I requery and go back to the bookMark.

Thanks for your patience....
It's truly appreciated...
bob mcclellan..


Dim bkm As Variant
With Me.RecordsetClone
.Find "[JobNumber]= " & j
bkm = .Bookmark
End With
Me.Requery
Me.Bookmark = bkm
Click to expand...
Click to expand...
Click to expand...
 
The acCmdSaveRecord works great!!! But what about when my code on the
subform updates some data on the Parent form??
It only saves the subform record. Maybe I'll try the Resync on the Parent
recordset...
Thanks for your help!
 
Back
Top