Access table values from function call within a report.

  • Thread starter Thread starter box2003
  • Start date Start date
B

box2003

I know how to access table data on forms with use of recordsets for delete,
update, and add records to a table that is not bound to a form.

I think I am in a similar situation where I have a report that has a couple
function calls to a module and within each respective module function, a
series of case statements exists. Each case selection statement has hard
coded decimal values used for the report textbox object calculation. I have
taken the decimal values that are contained in the case selection statements
and put them into a table with a unique rowid, where the unique rowid is a
userid. How can I access these table values from the report function call
that was originally used to obtain the decimal value in each of the case
selection statement?

The reason for wanting to do this, each time a calculation value changes, I
have to get into the module, make the hard code changes, then redistribute
the application front end. In my new method, the decimal calculation values
are maintained in a table and the user can access the table to change a
desired value. When the report fires, the decimal value is obtained from
the table by way of the function call and unique rowid, which represents the
userid.

The report has a query as a main datasource. Within the report detail area,
two textboxes exists that have calls to the described functions.

Any hint of a direction would be of value here so I could at least begin to
experiment how this is accomplished.

Thank you and if this is not clear of what I ask, I can try to explain in
more detail.
 
Box,

It is difficult to give explicit comments without more specific details
and examples of what you are doing.

It sounds like a good idea to maintain these values in a table. It
sounds like you could make a query that returned the value from the
table that you need in your report at any given time... is that right?
If so, I would simply make such a query, and include it into the query
that the report is based on, and then you can use it directly from your
report's code, or within calculated controls on the report or whatever.

Another avenue may be to use a DLookup function in your code to return
the required decimal value.
 
On my report the below code is executed from one of the textbox controls:

Function Called from report textbox: getPremium

=IIf(IsNull([Rewrite Old Power Number]),getPremium([Bond
Amount],[UnderWriter],[is this bond void?]),IIf([Bond Amount]<=[Rewrite Bond
Amount],0,getPremium([Bond Amount],[UnderWriter],[is this bond
void?])-getPremium([Rewrite Bond Amount],[UnderWriter],[is this bond
void?])))


Function:

Public Function getPremium(ByVal BondAmount As Double, _
ByVal Underwriter As Variant, ByVal Void As Variant)

On Error GoTo HandleError

Dim returnValue As Double

'Check to see if Bond is Voided. If it is, return 0
If (Void = True) Then
returnValue = 0
GoTo theEnd
End If

Select Case loginVal

Case "jcgabb" 'John Craven General Agency
Select Case Underwriter
Case "BSC" 'South Carolina
If (BondAmount < 201) Then
returnValue = 2.5
ElseIf (201 <= BondAmount) And (BondAmount < 401) Then
returnValue = (BondAmount * 0.01) + 1
ElseIf (401 <= BondAmount) And (BondAmount < 501) Then
returnValue = 4.75
ElseIf (BondAmount >= 501) Then
returnValue = BondAmount * 0.0095
End If
Case "BNC" 'North Carolina
returnValue = (BondAmount * 0.15 * 0.063331)
Case "BTN" 'Tennesee
returnValue = (BondAmount * 0.0095)
Case "ACIC" 'American Contractors Indemnity Company
If (BondAmount <= 590) Then
returnValue = 5
Else
returnValue = (BondAmount * 0.0085)
End If
Case Else
'do nothing

*******************************
Code snipped here for brevity in group posting
*******************************

End Select
End Select

Question Continued:

My intention is to aquire the numerical values listed in the code from a
table I have created. Rather than have the hard coded values in the code
segment, I would rather call into this function these values from the table.
For another example, (BondAmount * 0.0095), I would like to aquire the value
0.0095 from a table for use in this function and do away with having 0.0095
hard coded into this function.

Thanks again for your assistance, if I need to further clarify, I will
attempt to do so.
 
Box,

Thanks for the further clarification. However, as regards "call into
this function these values from the table", the key information here is
which table these values are in, and how does this table relate with the
data that is already in the query that the report is based on. Sorry,
can you please also try to clarify this. It may even be an idea to post
back with the SQL of the report's underlying query, and also the
relevant fields from the table that the decimal values are in.

As I mentioned before, I think the two options are to incorporate the
table with the decimal values into the report's underlying query, or
else to use DLookup function to return them into your function.

--
Steve Schapel, Microsoft Access MVP

On my report the below code is executed from one of the textbox controls:

Function Called from report textbox: getPremium

=IIf(IsNull([Rewrite Old Power Number]),getPremium([Bond
Amount],[UnderWriter],[is this bond void?]),IIf([Bond Amount]<=[Rewrite Bond
Amount],0,getPremium([Bond Amount],[UnderWriter],[is this bond
void?])-getPremium([Rewrite Bond Amount],[UnderWriter],[is this bond
void?])))


Function:

Public Function getPremium(ByVal BondAmount As Double, _
ByVal Underwriter As Variant, ByVal Void As Variant)

On Error GoTo HandleError

Dim returnValue As Double

'Check to see if Bond is Voided. If it is, return 0
If (Void = True) Then
returnValue = 0
GoTo theEnd
End If

Select Case loginVal

Case "jcgabb" 'John Craven General Agency
Select Case Underwriter
Case "BSC" 'South Carolina
If (BondAmount < 201) Then
returnValue = 2.5
ElseIf (201 <= BondAmount) And (BondAmount < 401) Then
returnValue = (BondAmount * 0.01) + 1
ElseIf (401 <= BondAmount) And (BondAmount < 501) Then
returnValue = 4.75
ElseIf (BondAmount >= 501) Then
returnValue = BondAmount * 0.0095
End If
Case "BNC" 'North Carolina
returnValue = (BondAmount * 0.15 * 0.063331)
Case "BTN" 'Tennesee
returnValue = (BondAmount * 0.0095)
Case "ACIC" 'American Contractors Indemnity Company
If (BondAmount <= 590) Then
returnValue = 5
Else
returnValue = (BondAmount * 0.0085)
End If
Case Else
'do nothing

*******************************
Code snipped here for brevity in group posting
*******************************

End Select
End Select

Question Continued:

My intention is to aquire the numerical values listed in the code from a
table I have created. Rather than have the hard coded values in the code
segment, I would rather call into this function these values from the table.
For another example, (BondAmount * 0.0095), I would like to aquire the value
0.0095 from a table for use in this function and do away with having 0.0095
hard coded into this function.

Thanks again for your assistance, if I need to further clarify, I will
attempt to do so.
Click to expand...
 
Back
Top