|
|
I wrote a public function in an Access module to calculate elapsed time
between two values. It works fine in Access queries and in a data access
page (DAP) in Access during the design phase. But when I try to open the DAP
outside Access from its SharePoint folder the DAP throws an error, apparently
unable to access the user-defined function.
Is there any way for DAPs to use user-defined functions?
--
Christian Bahnsen
|
|
You have to put the user-defined function in the script module of the DAP
itself so that the script can "see" and run it. You need to put it in as a
separate script procedure.
--
Ken Snell
<MS ACCESS MVP>
"Christian Bahnsen" <ChristianBahnsen[ at ]discussions.microsoft.com> wrote in
message news:3B3692A5-BF26-41E3-AEE8-4FEAB8A005D8[ at ]microsoft.com...
[Quoted Text] >I wrote a public function in an Access module to calculate elapsed time
> between two values. It works fine in Access queries and in a data access
> page (DAP) in Access during the design phase. But when I try to open the
> DAP
> outside Access from its SharePoint folder the DAP throws an error,
> apparently
> unable to access the user-defined function.
>
> Is there any way for DAPs to use user-defined functions?
>
> --
> Christian Bahnsen
|
|
Thanks for your reply. I’ve been experimenting with VBscript, reworking the
DAP.
In Access I deleted the column using my Access function “duration()†from
the underlying query, and created a new DAP. This DAP opens fine from
SharePoint, no longer throwing the “Data provider failed while executing a
provider command†and “Undefined function ‘duration’ in expression†errors.
I then added a textbox to the Header section of the DAP and set its Id to
“Elapsed_Timeâ€. I also added a label to the Caption section of the DAP, set
its Id to Elapsed_Time_Label and the InnerText to Elapsed_Time.
I saved the DAP in Access then opened it again successfully from SharePoint.
The other columns populate with data; my Elapsed_Time column appears but has
no data at this point.
I closed the DAP, went back to Access, opened the DAP in design mode, opened
the script editor, and added the following script/function at the tail end of
the HEAD section:
<SCRIPT language=vbscript>
Option Explicit
Function duration(AdmitDate,AdmitTime,DispoDate,DispTime)
Dim myAdmitHours
Dim myAdmitMinutes
Dim myDischargeHours
Dim myDischargeMinutes
Dim temp
Dim temp2
myAdmitHours = Int(Left(myAdmit, 2))
myDischargeHours = Int(Left(myDischarge, 2))
myAdmitMinutes = Int(Right(myAdmit, 2))
myDischargeMinutes = Int(Right(myDischarge, 2))
If myAdmitDate = myDischargeDate Then
temp = myDischargeHours - myAdmitHours
Else
temp = 24 + (myDischargeHours - myAdmitHours)
End If
temp2 = myDischargeMinutes - myAdmitMinutes
If temp2 < 0 Then
temp = temp - 1
temp2 = 60 + temp2
End If
duration = cstr(temp) & "hrs" & trim(cstr(temp2)) &
"mins"
End Function
</SCRIPT>
Note: AdmitDate, AdmitTime, DispoDate, and DispTime are other columns in the
DAP and the underlying query. They are also visible in the pull-down list of
the ControlSource property for textboxes. My first question is: can my
function recognize these fields/columns as arguments? (The Access function
can.)
Being methodical, I saved the DAP in Access, then opened it again from its
SharePoint document folder. No problems, but still no data in the
Elapsed_Time column.
I closed the DAP, went back to Access, and opened the DAP in design mode.
My second question is now: How do I get the Elapsed_Time textbox to use the
vbscript function and display the calculated result for each row?
For my Elapsed_Time textbox I tried entering:
=duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
property. This throws the error: The expression
“duration(AdmitDate,AdmitTime,DispoDate,DispTime)†is not valid.
duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
property. This throws the error: Invalid property value.
=duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
property. This doesn’t throw any errors; neither does it render any data
into the Elapsed_Time field when I view the DAP.
duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
property. This doesn’t throw any errors; it renders the word “duration†in
the Elapsed_Time field when I view the DAP.
either variation into the AlternateDataSource property. This doesn’t throw
any errors; neither does it render any data into the Elapsed_Time field when
I view the DAP.
++++++
Just for comparison, here is the Access version of the function, which works
fine:
Public Function duration(myAdmitDate As Date, myAdmit As String,
myDischargeDate As Date, myDischarge As String) As String
Dim myAdmitHours, myAdmitMinutes, myDischargeHours, myDischargeMinutes
As Integer
Dim temp, temp2 As Integer
'first let's separate the hours and minutes for the admit and discharge
myAdmitHours = Int(Left(myAdmit, 2))
myDischargeHours = Int(Left(myDischarge, 2))
myAdmitMinutes = Int(Right(myAdmit, 2))
myDischargeMinutes = Int(Right(myDischarge, 2))
If myAdmitDate = myDischargeDate Then
temp = myDischargeHours - myAdmitHours
Else
temp = 24 + (myDischargeHours - myAdmitHours)
End If
temp2 = myDischargeMinutes - myAdmitMinutes
If temp2 < 0 Then
'the admission time is greater than the discharge time
'that means we need to subtract an hour from temp
'and adjust the hour
temp = temp - 1
temp2 = 60 + temp2
Else
'the discharge time is greater than the discharge time
'no action necessary
End If
duration = Str(temp) & "hrs " & Trim(Str(temp2)) & "mins"
End Function
+++++
Thanks again in advance for any help.
--
Christian Bahnsen
"Ken Snell (MVP)" wrote:
[Quoted Text] > You have to put the user-defined function in the script module of the DAP
> itself so that the script can "see" and run it. You need to put it in as a
> separate script procedure.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Christian Bahnsen" <ChristianBahnsen[ at ]discussions.microsoft.com> wrote in
> message news:3B3692A5-BF26-41E3-AEE8-4FEAB8A005D8[ at ]microsoft.com...
> >I wrote a public function in an Access module to calculate elapsed time
> > between two values. It works fine in Access queries and in a data access
> > page (DAP) in Access during the design phase. But when I try to open the
> > DAP
> > outside Access from its SharePoint folder the DAP throws an error,
> > apparently
> > unable to access the user-defined function.
> >
> > Is there any way for DAPs to use user-defined functions?
> >
> > --
> > Christian Bahnsen
>
>
>
|
|
You have put the function in the correct place (in Head section). I assume
that the function starts this way:
-->
<SCRIPT language=VBScript>
Function NameOfFunction(Argument1, Argument2)
' script that does the function's work
End Function
-->
</SCRIPT>
I have not done work with using a function to fill in a textbox, so I
probably won't be able to give you the exact suggestions that you seek.
However, I would anticipate that you'll need to use some page event to run
the function and fill in the value in the textbox.
In order to use a value from a field in the page's Recordset object, I
usually assign its value to a variable and then use the variable. This is
easier for me because the full reference to a field in a page is a bit
"long-winded" (see my example below). In this example, the variable is
myvalue and the field name is sciid and SetVariable is a subroutine that is
using the variable's value:
dim myvalue
myvalue=msodsc.CurrentSection.DataPage.Recordset.Fields("sciid").value
call SetVariable("sciid",myvalue)
Also, note that DAP's do not provide "debug" error message boxes the way VBA
does. If an error occurs in the VBScript in a DAP, the page's code just
stops running -- no notice, no warning, no nothing. The only way I know to
debug VBScript "effectively" is to insert MsgBox statements after every line
so that you can 'trace' the script's progress and identify which line is
erroring, and then use MsgBox steps to show you the various values and be
sure that they are what you expect.
--
Ken Snell
<MS ACCESS MVP>
"Christian Bahnsen" <ChristianBahnsen[ at ]discussions.microsoft.com> wrote in
message news:C17E9412-76F6-49AC-9B8A-16A7E1DD75E7[ at ]microsoft.com...
[Quoted Text] > Thanks for your reply. I've been experimenting with VBscript, reworking
> the
> DAP.
>
> In Access I deleted the column using my Access function "duration()" from
> the underlying query, and created a new DAP. This DAP opens fine from
> SharePoint, no longer throwing the "Data provider failed while executing a
> provider command" and "Undefined function 'duration' in expression"
> errors.
>
> I then added a textbox to the Header section of the DAP and set its Id to
> "Elapsed_Time". I also added a label to the Caption section of the DAP,
> set
> its Id to Elapsed_Time_Label and the InnerText to Elapsed_Time.
>
> I saved the DAP in Access then opened it again successfully from
> SharePoint.
> The other columns populate with data; my Elapsed_Time column appears but
> has
> no data at this point.
>
> I closed the DAP, went back to Access, opened the DAP in design mode,
> opened
> the script editor, and added the following script/function at the tail end
> of
> the HEAD section:
>
> <SCRIPT language=vbscript>
>
> Option Explicit
> Function duration(AdmitDate,AdmitTime,DispoDate,DispTime)
>
> Dim myAdmitHours
> Dim myAdmitMinutes
> Dim myDischargeHours
> Dim myDischargeMinutes
> Dim temp
> Dim temp2
>
> myAdmitHours = Int(Left(myAdmit, 2))
> myDischargeHours = Int(Left(myDischarge, 2))
> myAdmitMinutes = Int(Right(myAdmit, 2))
> myDischargeMinutes = Int(Right(myDischarge, 2))
>
> If myAdmitDate = myDischargeDate Then
>
> temp = myDischargeHours - myAdmitHours
>
> Else
>
> temp = 24 + (myDischargeHours - myAdmitHours)
>
> End If
>
>
> temp2 = myDischargeMinutes - myAdmitMinutes
>
> If temp2 < 0 Then
>
> temp = temp - 1
> temp2 = 60 + temp2
>
> End If
>
> duration = cstr(temp) & "hrs" & trim(cstr(temp2)) &
> "mins"
>
> End Function
>
> </SCRIPT>
>
>
> Note: AdmitDate, AdmitTime, DispoDate, and DispTime are other columns in
> the
> DAP and the underlying query. They are also visible in the pull-down list
> of
> the ControlSource property for textboxes. My first question is: can my
> function recognize these fields/columns as arguments? (The Access
> function
> can.)
>
> Being methodical, I saved the DAP in Access, then opened it again from its
> SharePoint document folder. No problems, but still no data in the
> Elapsed_Time column.
>
> I closed the DAP, went back to Access, and opened the DAP in design mode.
> My second question is now: How do I get the Elapsed_Time textbox to use
> the
> vbscript function and display the calculated result for each row?
>
> For my Elapsed_Time textbox I tried entering:
>
> =duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
> property. This throws the error: The expression
> "duration(AdmitDate,AdmitTime,DispoDate,DispTime)" is not valid.
>
> duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
> property. This throws the error: Invalid property value.
>
> =duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
> property. This doesn't throw any errors; neither does it render any data
> into the Elapsed_Time field when I view the DAP.
>
> duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
> property. This doesn't throw any errors; it renders the word "duration"
> in
> the Elapsed_Time field when I view the DAP.
>
> either variation into the AlternateDataSource property. This doesn't
> throw
> any errors; neither does it render any data into the Elapsed_Time field
> when
> I view the DAP.
>
> ++++++
>
> Just for comparison, here is the Access version of the function, which
> works
> fine:
>
> Public Function duration(myAdmitDate As Date, myAdmit As String,
> myDischargeDate As Date, myDischarge As String) As String
>
> Dim myAdmitHours, myAdmitMinutes, myDischargeHours, myDischargeMinutes
> As Integer
> Dim temp, temp2 As Integer
>
> 'first let's separate the hours and minutes for the admit and discharge
> myAdmitHours = Int(Left(myAdmit, 2))
> myDischargeHours = Int(Left(myDischarge, 2))
> myAdmitMinutes = Int(Right(myAdmit, 2))
> myDischargeMinutes = Int(Right(myDischarge, 2))
>
> If myAdmitDate = myDischargeDate Then
>
> temp = myDischargeHours - myAdmitHours
>
> Else
>
> temp = 24 + (myDischargeHours - myAdmitHours)
>
> End If
>
> temp2 = myDischargeMinutes - myAdmitMinutes
> If temp2 < 0 Then
>
> 'the admission time is greater than the discharge time
> 'that means we need to subtract an hour from temp
> 'and adjust the hour
> temp = temp - 1
> temp2 = 60 + temp2
>
> Else
>
> 'the discharge time is greater than the discharge time
> 'no action necessary
>
> End If
>
> duration = Str(temp) & "hrs " & Trim(Str(temp2)) & "mins"
>
> End Function
>
> +++++
>
> Thanks again in advance for any help.
>
>
>
> --
> Christian Bahnsen
>
>
> "Ken Snell (MVP)" wrote:
>
>> You have to put the user-defined function in the script module of the DAP
>> itself so that the script can "see" and run it. You need to put it in as
>> a
>> separate script procedure.
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "Christian Bahnsen" <ChristianBahnsen[ at ]discussions.microsoft.com> wrote in
>> message news:3B3692A5-BF26-41E3-AEE8-4FEAB8A005D8[ at ]microsoft.com...
>> >I wrote a public function in an Access module to calculate elapsed time
>> > between two values. It works fine in Access queries and in a data
>> > access
>> > page (DAP) in Access during the design phase. But when I try to open
>> > the
>> > DAP
>> > outside Access from its SharePoint folder the DAP throws an error,
>> > apparently
>> > unable to access the user-defined function.
>> >
>> > Is there any way for DAPs to use user-defined functions?
>> >
>> > --
>> > Christian Bahnsen
>>
>>
>>
|
|
Ken,
Thanks for pointing me in the right direction. After digging into VBScript
(and pulling out some hair in the process) I was able to get the DAP working.
You learn something new everyday--whether you want to or not.
You'd think this stuff would get easier after years of cranking out
applications, but it doesn't seem to. Thank heaven for newsgroups like this
where you can get feedback and know that you're not alone.
--
Christian Bahnsen
"Ken Snell (MVP)" wrote:
[Quoted Text] > You have put the function in the correct place (in Head section). I assume
> that the function starts this way:
>
> -->
> <SCRIPT language=VBScript>
>
> Function NameOfFunction(Argument1, Argument2)
> ' script that does the function's work
> End Function
>
> -->
> </SCRIPT>
>
>
> I have not done work with using a function to fill in a textbox, so I
> probably won't be able to give you the exact suggestions that you seek.
> However, I would anticipate that you'll need to use some page event to run
> the function and fill in the value in the textbox.
>
> In order to use a value from a field in the page's Recordset object, I
> usually assign its value to a variable and then use the variable. This is
> easier for me because the full reference to a field in a page is a bit
> "long-winded" (see my example below). In this example, the variable is
> myvalue and the field name is sciid and SetVariable is a subroutine that is
> using the variable's value:
>
> dim myvalue
> myvalue=msodsc.CurrentSection.DataPage.Recordset.Fields("sciid").value
> call SetVariable("sciid",myvalue)
>
>
> Also, note that DAP's do not provide "debug" error message boxes the way VBA
> does. If an error occurs in the VBScript in a DAP, the page's code just
> stops running -- no notice, no warning, no nothing. The only way I know to
> debug VBScript "effectively" is to insert MsgBox statements after every line
> so that you can 'trace' the script's progress and identify which line is
> erroring, and then use MsgBox steps to show you the various values and be
> sure that they are what you expect.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Christian Bahnsen" <ChristianBahnsen[ at ]discussions.microsoft.com> wrote in
> message news:C17E9412-76F6-49AC-9B8A-16A7E1DD75E7[ at ]microsoft.com...
> > Thanks for your reply. I've been experimenting with VBscript, reworking
> > the
> > DAP.
> >
> > In Access I deleted the column using my Access function "duration()" from
> > the underlying query, and created a new DAP. This DAP opens fine from
> > SharePoint, no longer throwing the "Data provider failed while executing a
> > provider command" and "Undefined function 'duration' in expression"
> > errors.
> >
> > I then added a textbox to the Header section of the DAP and set its Id to
> > "Elapsed_Time". I also added a label to the Caption section of the DAP,
> > set
> > its Id to Elapsed_Time_Label and the InnerText to Elapsed_Time.
> >
> > I saved the DAP in Access then opened it again successfully from
> > SharePoint.
> > The other columns populate with data; my Elapsed_Time column appears but
> > has
> > no data at this point.
> >
> > I closed the DAP, went back to Access, opened the DAP in design mode,
> > opened
> > the script editor, and added the following script/function at the tail end
> > of
> > the HEAD section:
> >
> > <SCRIPT language=vbscript>
> >
> > Option Explicit
> > Function duration(AdmitDate,AdmitTime,DispoDate,DispTime)
> >
> > Dim myAdmitHours
> > Dim myAdmitMinutes
> > Dim myDischargeHours
> > Dim myDischargeMinutes
> > Dim temp
> > Dim temp2
> >
> > myAdmitHours = Int(Left(myAdmit, 2))
> > myDischargeHours = Int(Left(myDischarge, 2))
> > myAdmitMinutes = Int(Right(myAdmit, 2))
> > myDischargeMinutes = Int(Right(myDischarge, 2))
> >
> > If myAdmitDate = myDischargeDate Then
> >
> > temp = myDischargeHours - myAdmitHours
> >
> > Else
> >
> > temp = 24 + (myDischargeHours - myAdmitHours)
> >
> > End If
> >
> >
> > temp2 = myDischargeMinutes - myAdmitMinutes
> >
> > If temp2 < 0 Then
> >
> > temp = temp - 1
> > temp2 = 60 + temp2
> >
> > End If
> >
> > duration = cstr(temp) & "hrs" & trim(cstr(temp2)) &
> > "mins"
> >
> > End Function
> >
> > </SCRIPT>
> >
> >
> > Note: AdmitDate, AdmitTime, DispoDate, and DispTime are other columns in
> > the
> > DAP and the underlying query. They are also visible in the pull-down list
> > of
> > the ControlSource property for textboxes. My first question is: can my
> > function recognize these fields/columns as arguments? (The Access
> > function
> > can.)
> >
> > Being methodical, I saved the DAP in Access, then opened it again from its
> > SharePoint document folder. No problems, but still no data in the
> > Elapsed_Time column.
> >
> > I closed the DAP, went back to Access, and opened the DAP in design mode.
> > My second question is now: How do I get the Elapsed_Time textbox to use
> > the
> > vbscript function and display the calculated result for each row?
> >
> > For my Elapsed_Time textbox I tried entering:
> >
> > =duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
> > property. This throws the error: The expression
> > "duration(AdmitDate,AdmitTime,DispoDate,DispTime)" is not valid.
> >
> > duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the ControlSource
> > property. This throws the error: Invalid property value.
> >
> > =duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
> > property. This doesn't throw any errors; neither does it render any data
> > into the Elapsed_Time field when I view the DAP.
> >
> > duration(AdmitDate,AdmitTime,DispoDate,DispTime) into the DefaultValue
> > property. This doesn't throw any errors; it renders the word "duration"
> > in
> > the Elapsed_Time field when I view the DAP.
> >
> > either variation into the AlternateDataSource property. This doesn't
> > throw
> > any errors; neither does it render any data into the Elapsed_Time field
> > when
> > I view the DAP.
> >
> > ++++++
> >
> > Just for comparison, here is the Access version of the function, which
> > works
> > fine:
> >
> > Public Function duration(myAdmitDate As Date, myAdmit As String,
> > myDischargeDate As Date, myDischarge As String) As String
> >
> > Dim myAdmitHours, myAdmitMinutes, myDischargeHours, myDischargeMinutes
> > As Integer
> > Dim temp, temp2 As Integer
> >
> > 'first let's separate the hours and minutes for the admit and discharge
> > myAdmitHours = Int(Left(myAdmit, 2))
> > myDischargeHours = Int(Left(myDischarge, 2))
> > myAdmitMinutes = Int(Right(myAdmit, 2))
> > myDischargeMinutes = Int(Right(myDischarge, 2))
> >
> > If myAdmitDate = myDischargeDate Then
> >
> > temp = myDischargeHours - myAdmitHours
> >
> > Else
> >
> > temp = 24 + (myDischargeHours - myAdmitHours)
> >
> > End If
> >
> > temp2 = myDischargeMinutes - myAdmitMinutes
> > If temp2 < 0 Then
> >
> > 'the admission time is greater than the discharge time
> > 'that means we need to subtract an hour from temp
> > 'and adjust the hour
> > temp = temp - 1
> > temp2 = 60 + temp2
> >
> > Else
> >
> > 'the discharge time is greater than the discharge time
> > 'no action necessary
> >
> > End If
> >
> > duration = Str(temp) & "hrs " & Trim(Str(temp2)) & "mins"
> >
> > End Function
> >
> > +++++
> >
> > Thanks again in advance for any help.
> >
> >
> >
> > --
> > Christian Bahnsen
> >
> >
> > "Ken Snell (MVP)" wrote:
> >
> >> You have to put the user-defined function in the script module of the DAP
> >> itself so that the script can "see" and run it. You need to put it in as
> >> a
> >> separate script procedure.
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >>
> >> "Christian Bahnsen" <ChristianBahnsen[ at ]discussions.microsoft.com> wrote in
> >> message news:3B3692A5-BF26-41E3-AEE8-4FEAB8A005D8[ at ]microsoft.com...
> >> >I wrote a public function in an Access module to calculate elapsed time
> >> > between two values. It works fine in Access queries and in a data
> >> > access
> >> > page (DAP) in Access during the design phase. But when I try to open
> >> > the
> >> > DAP
> >> > outside Access from its SharePoint folder the DAP throws an error,
> >> > apparently
> >> > unable to access the user-defined function.
> >> >
> >> > Is there any way for DAPs to use user-defined functions?
> >> >
> >> > --
> >> > Christian Bahnsen
> >>
> >>
> >>
>
>
>
|
|
|