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:
> 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@[EMAIL PROTECTED]
> wrote
in
> message news:C17E9412-76F6-49AC-9B8A-16A7E1DD75E7@[EMAIL PROTECTED]
> > 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 do***ent 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@[EMAIL PROTECTED]
>
wrote in
> >> message news:3B3692A5-BF26-41E3-AEE8-4FEAB8A005D8@[EMAIL PROTECTED]
> >> >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
> >>
> >>
> >>
>
>
>
|
