Recordset Error? (1 Viewer)

[eason]

I'm developing a report and have this as a problem, can anyone help me??? I have 3 differant tables that I am using. One called test_def then sam_log and sam_res. I have a query called qryPC_IncompleteTest that has sam_res in it and primary key is sam_id. In another query2 called qryPC_IncompleteTest Name I have sam_log and test_def built in a relationship 1 on a field called test_name. What I am tring to do is get a field called usrlbl1 in qryPC_IncompleteTest Name to appear on the report that qryPC_IncompleteTest runs. This is the suggestion that I was give but I'm having a hard time getting it to work.

Private Function Getusrlbl1 (id As String)
On Error Resume Next
Dim rst As Recordset
Dim stSQL As String

stSQL = "Select usrlbl1 from qurPCimcompleteTest wher sam_id=" & sam_id
Set rst = currentdb.OpenRecordset(stSQL)
rst.MoveFirst
If Err =0 Then
Do While Not rst.EOF
Getusrlbl1 = Getusrlbl1 & rst.Fields("usrlbl1") &","
rst.MoveNext
Getusrlbl1 = Left(Getusrlbl1, Len(Getusrlbl1)-2) 'Removes the last ","
Else
Getusrlbl1 = Empty 'No usrlbl1 exixt
End If

End Function

I have a text box on my report that has a control source =Getusrlbl1([sam_id]). When I run the report, the text box has #Error?. If I change the text box name to from usrlbl1 to Getusrlbl1 I get a #Name?. Can anyone help??

 

[Travis]

First try changing this stSQL to the one following:
stSQL = "Select usrlbl1 from qurPCimcompleteTest where sam_id=" & sam_id

stSQL = "Select usrlbl1 from qurPCimcompleteTest where sam_id=" & id 'id is what the value of sam_id passed to the Function

An other thing to ask and to be sure of is the field [sam_id] on the report. It does not have to be visible but it does have to be on the report in order for the function to pick up the value.

Hope this helps.

 

[Pat Hartman]

You are doing it the hard way. Include the table which contains the column you want in the Report's query. Join the two tables on sam_id.