adding code to a report, how do i get a current field value

[thydzik]

let say i have a text box in a report that brings in function someFunction that outputs a string,

in someFunction, how do I get a fields value, of the correct relative row?
would this function be able to be used in a module, or does it need to be part of the report code? How can I design someFunction in a module?

Thanks

 

[thydzik]

okay, maybe I should simplify the question

I want to show a value on a report, only if the field exists in a table, how do I ahceive this?

 

[ajetrumpet]

functions can be located anywhere in the database. you can also call them from anywhere. They do not have to be private entities, strictly bound to the parameters of their parent objects (like reports and forms). Use the Call statement to run functions that are located in their own modules, outside of the respective objects for which they are supporting (like in this case, the function that is supporting your report).

 

[ajetrumpet]

okay, maybe I should simplify the question

I want to show a value on a report, only if the field exists in a table, how do I ahceive this?

this is quite confusing, to tell you the truth.

What do you mean, "if the field exists in the table"??? Under what condition are you checking to see if a field is present in the table??

 

[thydzik]

this is quite confusing, to tell you the truth.

What do you mean, "if the field exists in the table"??? Under what condition are you checking to see if a field is present in the table??

thanks for the reply.
To further clarify, I have a report that has its report source modified through code. The report sources (in this case queries) doesn't have the same fields, hence there are fields on the report that may be in one query but not the other.
If the field is not in the query, when opening the report you are automatically prompted to enter the value, which is not what I would like.

I have found a solution using the following code:

Public Function ifExists(param As String, tag As String, tableName As String) As String
    If fieldExists(param, tableName) Then
        Dim qd As QueryDef
        Set qd = CurrentDb.QueryDefs(tableName)
        Dim rs As Recordset
        Set rs = qd.OpenRecordset
        Do While (rs("ID") <> tag)
            rs.MoveNext
        Loop
        If Not IsNull(rs(param)) Then
            ifExists = rs(param)
        Else
            ifExists = vbNullString
        End If
        Set rs = Nothing
    Else
        ifExists = vbNullString
    End If
End Function

Function fieldExists(s As String, tableName As String) As Boolean
    On Error Resume Next
    Dim fld As Field
    fieldExists = False
    For Each fld In CurrentDb.QueryDefs(tableName).Fields
        If fld.Name = s Then
            fieldExists = True
            Exit Function
        End If
    Next fld
End Function

you would place the following in the report text boxes
=ifExists("field1", [ID], "tableName")

I am pretty sure this is not the best way, as it loops through all the fields and records, but it works for now.

Any comments, or better approaches welcome.

To rephrase my individual post, I was basically asking on a method to get the relevant record, with out the need to loop through the table/query.

 

[pbaldy]

Your issue is similar in concept to dynamic crosstab reports, where the field names can change. You might be able to adapt that type of code to your needs. I use code from the Access Developer's Handbook, which I think is better, but there's also this:

http://support.microsoft.com/kb/328320/en-us