Update Stored Procedure in Access adp

[Cathi]

I'm looking for an adp expert to shed some light on this problem for me.

I have an Order form (based on tblOrder) with a field OrderNo I have an
Order Detail subform (based on tblOrderDetail) where staff are selected (a
dropdown and the asID field is bound = AgencyStaffID) and a date is entered
and a shift is selected from a dropdown (ie day or night) then a button is
clicked to run the stored procedure spUpdateRosterOrders. This stored
procedure finds the matching asID, Date and Shift in the table
tblAgencyStaffRoster and updates these three fields to match the asID, Date
and Shift selected in the Order Detail subform.

My dilemma is it works sometimes ie Catherine Larsen has asID 1 and it works
and but Donna Young asID 179 does not? Is anyone able to indicate why this
stored procedure would work for some asID's and not others?

The stored procedure is run using the VBA statement:
DoCmd.OpenStoredProcedure "dbo.spUpdateRosterOrders"

The stored procedure is:

UPDATE dbo.tblAgencyStaffRoster
SET rOrderNo =
(SELECT tblOrder.ordOrderNo
FROM tblOrder INNER JOIN
tblOrderDetail ON
tblOrder.ordOrderNo = tblOrderDetail.ordID
WHERE (rDate =
tblOrderDetail.odDateRequired) AND (rShift = tblOrderDetail.odShiftRequired)
AND (asID = tblOrderDetail.odAgencyStaffID)),
rActualStdHours =
(SELECT tblOrderDetail.odActualStdHours
FROM tblOrderDetail
WHERE (rDate =
tblOrderDetail.odDateRequired) AND (rShift = tblOrderDetail.odShiftRequired)
AND (asID = tblOrderDetail.odAgencyStaffID)),
rActualOTHours =
(SELECT tblOrderDetail.odActualOTHours
FROM tblOrderDetail
WHERE (rDate =
tblOrderDetail.odDateRequired) AND (rShift = tblOrderDetail.odShiftRequired)
AND (asID = tblOrderDetail.odAgencyStaffID)),
rActualTotHours =
(SELECT tblOrderDetail.odActualTotHours
FROM tblOrderDetail
WHERE (rDate =
tblOrderDetail.odDateRequired) AND (rShift = tblOrderDetail.odShiftRequired)
AND (asID = tblOrderDetail.odAgencyStaffID))

I have checked the data types as follows:

tblOrder
ordOrderNo=int 4, identity, therefore do not allow nulls

tblOrderDetail
odAgencyStaffID=int 4, allow nulls
odDateRequired=Date/time
odShiftRequired=varchar, 10, do not allow nulls

tblAgencyStaffRoster
asID=int, 4 no identify, do not allow nulls
rDate=date/time, do not allow nulls
rShift=varchar 10, do not allow nulls
rOrderNo=varchar 50, allow nulls

If it didn't run at all that would make sense, but the fact it works
sometimes and not other times is odd? Any help would be much appreciated.
Thanks in advance Cathi

 

[Graham R Seach]

Cathi,

You might want to show us the entire stored procedure, including arguments
and declarations, because all I see here is an action query that gets its
filter values from several select statements. I can see no way of providing
values for asID, Date or Shift from the subform.

Also, where's the button? On the main form, or the subform?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Cathi]

Thanks Graham
I have gone a little further, but I really am a Jet girl not a SQL girl.
The button is on the main form.
It now runs the following code to pick up the OrderNo from the MainForm (ie
Order table) then narrows down matching records in the Order Detail table by
OrderNo there could be several, so it grabs the first Nurse, date, shift and
using the SQL statement updates the Roster table and then loops through and
grabs each Nurse, date, shift and updates the Roster table.

My problem now is I get the error "Object Variable or With Block Variable
Not Set" when I step through the VBA code and reach the Set myRS statement.
The myDB is Nothing and the myRS is Nothing. If I could just get it to open
the recordset mySource I know I would be away.

Any help would be very appreciated. Thanks in advance Cathi :-D


Dim myDB As Database, myRS As Variant, mySource As String, mySQL As String
Dim myNurse As String, myDate As String, myShift As String, myOrderNo As
String

myOrderNo = CStr([Forms]![frmOrder]![ordOrderNo])
mySource = "SELECT ordID, odDateRequired, odShiftRequired, " & _
"odActualStdHours, odActualOTHours, odActualTotHours, odAgencyStaffID "
& _
"FROM dbo.tblOrderDetail " & _
"Where (ordID = " & myOrderNo & ")"

Set myDB = CurrentDb()
Set myRS = myDB.OpenRecordset(mySource)
If Me.ordOrderStatus = "Assigned" Then
With myRS
.MoveFirst
Do Until .EOF
myNurse = ![odAgencyStaffID]
myDate = ![odDateRequired]
myShift = ![odShiftRequired]
mySQL = "Update dbo.tblAgencyStaffRoster " & _
"SET rOrderNo = " & myOrderNo & ", rElement='ORD' " & _
"WHERE (asID= " & myNurse & ") AND " & _
"(rDate= CONVERT(DATETIME,'" & Format(myDate,
"yyyy-mm-dd hh:mm:ss") & "', 102)" & ") " & _
"AND (rShift='" & myShift & "')"
DoCmd.RunSQL mySQL
.MoveNext
Loop
End With
End If

Set myDB = Nothing
Set myRS = Nothing

 

[Cathi]

I researched the error "Object Variable or With Block Variable Not Set" and
no luck.
Surely it must be easy to open a recordset? My code is now:

Dim myDB As DAO.Database, myRS As DAO.Recordset, mySource As String, mySQL
As String
Dim myNurse As String, myDate As String, myShift As String, myOrderNo As
String

myOrderNo = CStr([Forms]![frmOrder]![ordOrderNo])
mySource = "SELECT ordID, odDateRequired, odShiftRequired, " & _
"odActualStdHours, odActualOTHours, odActualTotHours, odAgencyStaffID "
& _
"FROM dbo.tblOrderDetail " & _
"Where (ordID = " & myOrderNo & ")"

Set myDB = CurrentDb()
Set myRS = CurrentDb.OpenRecordset(mySource)
If Me.ordOrderStatus = "Assigned" Then
If Not myRS.BOF And Not myRS.EOF Then
myRS.MoveFirst
Do Until Not myRS.EOF
myNurse = myRS![odAgencyStaffID]
myDate = myRS![odDateRequired]
myShift = myRS![odShiftRequired]
mySQL = "Update dbo.tblAgencyStaffRoster " & _
"SET rOrderNo = " & myOrderNo & ", rElement='ORD' " & _
"WHERE (asID= " & myNurse & ") AND " & _
"(rDate= CONVERT(DATETIME,'" & Format(myDate,
"yyyy-mm-dd hh:mm:ss") & "', 102)" & ") " & _
"AND (rShift='" & myShift & "')"
DoCmd.RunSQL mySQL
myRS.MoveNext
Loop
End If
End If

Set myDB = Nothing
myRS.Close
Set myRS = Nothing