RE: SQL stored procedure executing twice

Yes, stepping through the code is how I determined that
stReturnMssg = DLookup("[Message]", "qsptDampMoveConsult")
caused the stored procedure to execute twice. That along with the tempCount
table.

"Duane Hookom" wrote:

The only thing that I can think of is that you are double-clicking the cmd
button rather than single-clicking? Have you tried setting a Break Point and
stepping through the code?
--
Duane Hookom
Microsoft Access MVP


"mlwallin" wrote:

I hate to bog you down with the pass thru code when I know it does not
trigger the stored procedure. The only thing I see is that I'm using DAO in
the pass-thru and ADO in the record display. Could that be an issue?

But if you need the whole picture, here it is:

The purpose of the Access form having the problem is to move a child record
from one parent record to another. (The two parent records are chosen in a
prior form.) The form in question is unbound, and contains two subforms, one
for each parent record. Each of those two subforms contain their own subform
to display the child records for each respective parent. The user may either
select a child from the left side and click a button to move it to the parent
on the right, or they select the child from the right side and click a
different button to move it to the left side.

My VBA code for moving the child from the left to the right:

Private Sub cmdPt1toPt2_Click()
On Error GoTo ErrHere

Dim stDocName As String
Dim stSQL As String
Dim stReturnMssg As String

stSQL = "DampMoveConsult " &
Me!fsubPt1!fsubPt1Consults.Form.[txtEncounterID1] & "," &
Me!fsubPt1.Form.[txtPtID1] & "," & Me!fsubPt2.Form.[txtPtID2]
stDocName = "qsptDampMoveConsult"
Call PassThroughFixup(stDocName, stSQL)

stReturnMssg = DLookup("[Message]", "qsptDampMoveConsult")
MsgBox stReturnMssg, vbInformation, "FYI"

Set rst2 = New ADODB.Recordset
With rst2
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = "select [Encounter ID], Clinic, WhoAdded, WhenAdded from
tblConsults where [Patient ID] = " & iPt1
.Open
End With
Set Me!fsubPt1!fsubPt1Consults.Form.Recordset = rst2

Set rst4 = New ADODB.Recordset
With rst4
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = "select [Encounter ID], Clinic, WhoAdded, WhenAdded from
tblConsults where [Patient ID] = " & iPt2
.Open
End With
Set Me!fsubPt2!fsubPt2Consults.Form.Recordset = rst4

ExitHere:
Exit Sub

ErrHere:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere

End Sub

Pass-thru routine

Public Sub PassThroughFixup(ByVal strQdfName As String, strSQL As String,
Optional varConnect As Variant, Optional fRetRecords As Boolean = True)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strConnect As String

Set db = CurrentDb
Set qdf = db.QueryDefs(strQdfName)
If IsMissing(varConnect) Then
strConnect = qdf.Connect
Else
strConnect = CStr(varConnect)
End If

qdf.Connect = strConnect
qdf.ReturnsRecords = fRetRecords
qdf.SQL = strSQL

End Sub


My pass-thru query properties

ODBC;Description=Ptreg test;DRIVER=SQL
Server;SERVER=ISDEVSQL;UID=mlwallin;DATABASE=PTREG;Network=DBMSSOCN;Trusted_Connection=Yes

My pass-thru query syntax going to stored procedure

DampMoveConsult 143024,106422,150020

Here's the SQL stored procedure

ALTER procedure DampMoveConsult

@encid integer,
@currptid integer,
@newptid integer

as

--debug to see when stored procedure executes

insert tempCount
select getdate()

--moves child record from one parent record to another

declare @numc as integer
declare @nump as integer

set @numc = (select count([Patient ID]) from tblConsults where [Encounter
ID] = @encid and [Patient ID] = @currptid)
set @nump = (select count([Patient ID]) from tblPatients where [Patient ID]
= @newptid)

if @numc = 1 and @nump = 1
begin --updates child record and returns confirmation message
update tblConsults
set [Patient ID] = @newptid
where [Encounter ID] = @encid and [Patient ID] = @currptid
select 'Moving consult' as Message
end
else
begin --returns error message
select 'Error' as Message
end

The table tempCount does not change until I execute the VBA code line:
stReturnMssg = DLookup("[Message]", "qsptDampMoveConsult")
then it shows with two entries. The stored procedure works as expected the
first time through. The move from one parent record to another does occur,
and the Access form displays the change properly. However I never get the
opportunity to display the confirmation message "Consult moved" because the
stored procedure immediately runs again and displays "Error".

Executing the stored procedure directly in Query Analyzer (not from Access)
works OK, it reutrns "Consult moved", and causes only one entry to be added
to tempCount.

There - that was probably more than you bargained for!!

"Duane Hookom" wrote:

It's a bit difficult to tell without seeing your code, your pass-through sql,
or the debugging of stepping through your code.

--
Duane Hookom
Microsoft Access MVP


"mlwallin" wrote:

PassThroughFixup() does not run the query. I created a table where I insert
the date and time each time the stored procedure executes. It shows having
run twice after the command where dlookup() values the string.

"Duane Hookom" wrote:

If PassThroughFixup() runs the query it should/can return some value. The
DLookup() will run the query again.

--
Duane Hookom
Microsoft Access MVP


"mlwallin" wrote:

For some reason, the following VBA code causes a SQL stored procedure
qsptDampMoveConsult to execute twice. It happens on the line below where
stReturnMssg is valued from the DLookup. I've tested the stored procedure
from Query Analyzer, and I know the SQL part works OK. Is the Dlookup
causing it? How else can I execute the pass-thru query and get the return
message confirming that it worked?

Private Sub cmdPt1toPt2_Click()
On Error GoTo ErrHere

Dim stDocName As String
Dim stSQL As String
Dim stReturnMssg As String

stSQL = "DampMoveConsult " &
Me!fsubPt1!fsubPt1Consults.Form.[txtEncounterID1] & "," &
Me!fsubPt1.Form.[txtPtID1] & "," & Me!fsubPt2.Form.[txtPtID2]
stDocName = "qsptDampMoveConsult"
Call PassThroughFixup(stDocName, stSQL)

stReturnMssg = DLookup("[Message]", "qsptDampMoveConsult")
MsgBox stReturnMssg, vbInformation, "FYI"

.