Sylvain,
What you are looking for is probably Me.Recordset.Resync .
------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.)
------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
-----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.
----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
