Problem Passing Value to Stored Procedure (ADP)

[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!

 

[Robert Morley]

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

 

[James_Hudson]

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

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

[quoted text clipped - 19 lines]

Can
someone please point me in the right direction? Thanks very much!

 

[Norman Yuan]

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.

 

[aaron.kempf]

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

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.

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!

 

[James_Hudson via AccessMonster.com]

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

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

[quoted text clipped - 8 lines]