Problem Passing Value to Stored Procedure (ADP)

  • Thread starter Thread starter James_Hudson
  • Start date Start date
J

James_Hudson

I'm new to SQL/ADP and have been having a hard time getting an UPDATE sproc
to work. Here's the scenario: I have a form (frm_EncounterCreation) that a
nurse fills out with patient info. There's a command button at the end that
produces a fax (using WinFax) for the referring doctor's office. When the
command button is pressed, I would like to UPDATE a field called
FirstRecordsReqDate with the current date, so the nurse doesn't have to
capture that value manually.

Here's the VBA behind the button for this action:

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
Set strSQL = "UPDATE dbo.tbl_MainData Set FirstRecordsReqDate = GETDATE()
WHERE @EntryID =" & Forms![frm_EncounterCreation]![EntryID]
Set qdf = dbs.CreateQueryDef("update", strSQL)

When I click the button, however, I get a message stating "Compile Error:
Object Required", and highlighting the I[EntryID] of my WHERE statement. Can
someone please point me in the right direction? Thanks very much!
 
The problem is with "Set strSQL = ". "Set" is only required for objects,
not for native data types, so you just need to remove that (so it's "strSQL
= ") and everything should be fine.


Rob
 
Thanks for the quick reply! Sadly, I already tried that... and the result
was an error message cropping up: Object variable or With block variable not
set. Any idea why? It looks to me like the variables are all set...


--James

Robert said:
The problem is with "Set strSQL = ". "Set" is only required for objects,
not for native data types, so you just need to remove that (so it's "strSQL
= ") and everything should be fine.

Rob
I'm new to SQL/ADP and have been having a hard time getting an UPDATE
sproc
Click to expand...
[quoted text clipped - 19 lines]
Can
someone please point me in the right direction? Thanks very much!
Click to expand...
Click to expand...
 
If you are talking about ADP, then your VBA code will not be working:
Database, QueryDef... are objects from DAO, which is not used by ADP. ADP
uses ADO.
 
and you should just do something like


strSql = "ALTER mySproc AS "
strSql = strSql & vbcrlf & "Select 'This Is Where The new Sql Goes'"

Docmd.Runsql strSql



Norman said:
If you are talking about ADP, then your VBA code will not be working:
Database, QueryDef... are objects from DAO, which is not used by ADP. ADP
uses ADO.

James_Hudson said:
I'm new to SQL/ADP and have been having a hard time getting an UPDATE
sproc
to work. Here's the scenario: I have a form (frm_EncounterCreation) that
a
nurse fills out with patient info. There's a command button at the end
that
produces a fax (using WinFax) for the referring doctor's office. When the
command button is pressed, I would like to UPDATE a field called
FirstRecordsReqDate with the current date, so the nurse doesn't have to
capture that value manually.

Here's the VBA behind the button for this action:

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
Set strSQL = "UPDATE dbo.tbl_MainData Set FirstRecordsReqDate = GETDATE()
WHERE @EntryID =" & Forms![frm_EncounterCreation]![EntryID]
Set qdf = dbs.CreateQueryDef("update", strSQL)

When I click the button, however, I get a message stating "Compile Error:
Object Required", and highlighting the I[EntryID] of my WHERE statement.
Can
someone please point me in the right direction? Thanks very much!
Click to expand...
Click to expand...
 
OK, I finally figured this out - thanks to all who responded! For future
reference for anyone else, I entered this as the code for my command button:

Dim SQL As String

SQL = "UPDATE dbo.tbl_MainData " & "SET FirstRecordsRequestDate = GETDATE
()" & "WHERE EntryID = " & Forms![frm_EncounterCreation]![EntryID]

DoCmd.RunSQL SQL

Thanks again!


--James

James_Hudson said:
Aaron (and everyone else!) - Thanks very much for the replies! Just to make
sure I understand - are you saying that the lines below are all I need to
code to get the result? The SQL certainly looks that way... but when I enter
the code, I get an error on the DoCmd line. Here's the code specifically -
did I misunderstand something you wrote?

strSQL = "ALTER qry_UpdateReferringProviderFirstRecordsRequest AS "
strSQL = strSQL & Forms![frm_EncounterCreation]![FirstRecordsReqDate] &
"SELECT GETDATE()"

DoCmd.RunSQL strSQL

Again, thank you very much for your assistance!

--James
and you should just do something like
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
Back
Top