list box in report detail (97)

[AccessMan]

hi all
here is my problem: i have a subreport in the detail section of a report. the subreport updates for each ID# of the main report. there is a list box in the subreport that i would also like to update for each ID# of the main report. the list box is in the subreport, and i would assume needs criteria from the main report. i entered reports!reportname.ID# as the criteria. i thought maybe the Format event of either the subreport or the detail of the main report would be a good place to do a requery of the list box, but access keeps generating errors (general access errors - nothing too descriptive). please help

thanks in advance

[charlotte]

What is your listbox displaying and why use a listbox instead of a textbox? Reports don't have current records in the same way that forms do, so I would expect referring to a control on a report as criteria to give you an error. If the listbox is bound to a field in the subreport, it shouldn't need anything. I suspect that to make it work at all, you would need to use a callback as the rowsource for the listbox rather than a query, but that's a guess because I've never wanted to use a listbox on a subreport. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

[AccessMan]

ok, here's what i am trying to do..

the main report shows all information about key requisitions. in that report are several groups of information (subreports) that the user has the option of turning on and off (like key holder info, key info, etc.). Iin the key info section, i would like to have a text box called "openings" with a comma delimited list of all openings (doors) in which a key will open. i have a table that ties all keys (openings) to the requisition in which they were issued. i was trying to have a list box (which would be invisible) list all the openings, then use code to display that in a text box in comma delimited form. please help me figure this out or a work around... i have faith in you, charlotte! <img src=/S/help.gif border=0 alt=help width=23 height=15>

thanks again

[charlotte]

I think you're trying to do it the hard way. I would approach the problem by building the "openings" string in the query underlying the subform and simply binding a textbox to that calculated field. That would talke care of all your linkages and eliminate the report having to do anything exotic. It would be simplest to build a function to create the list of openings based on the key passed to the function. You would use the function as the expression for that column something like this: Openings: GetOpenings ([Key])

This assumes you have a field in the query called Key and you have created a function called GetOpenings that accepts a Key value and returns a concatenated list of openings as a string. Then you would bind your textbox to the Openings field in the query.

Here's some air code for the function (any errors are probably typos ... yeah, right! [innocnet]). I've assumed that Key was a string, so you would have to modify a few things if it is numeric, and you would have to make necessary changes to table and field names.

<pre>Public Function GetOpenings (Key As String) As String
Dim strTemp As String
Dim strSQL As String
Dim rst As DAO.Recordset
Dim dbs As DAO.Database

strSQL = "SELECT [Opening] FROM tblOpenings " _
& "WHERE [Key]= '" & Key & "'"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Do While Not .EOF
strTmp = strTmp & !Key
.MoveNext
If Not .EOF then
strTmp = strTmp & ", "
End If
Loop
End With

rst.Close
Set rst = Nothing
Set dbs = Nothing

GetOpenings = strTmp
End Function</pre>

[AccessMan]

hmm, thanks charlotte..
there is a problem.. i put "OpeningList: GetOpenings ([ReqID]) as the expression for the new field in the query and created a new function called "GetOpenings" with your code in it (changing instances of "Key" to "ReqID") when i run the query, it gives me the error "Undefined Function 'GetOpenings' in expression". what am i doing wrong?

thanks for your help

[charlotte]

Dunno. Where did you create the function? It has to be in a standard module before you can see it from the query.

[AccessMan]

i clicked "new" under the "modules" tab and pasted the code there. named the module "GetOpenings" as well. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

[Francois]

Change the name of the module. Never give a module the name of a function or sub

[AccessMan]

now it says "Compile Error in query expression 'GetOpenings([ReqID])' the previous error is not happening anymore.

[Francois]

In the function there is a little error.
Replace the line
Dim strTemp As String
by
Dim strTmp As String

[AccessMan]

i forgot to mention that i caught that. doesn't seem to be fixing the problem... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

[AccessMan]

i have had to change a couple things, so here is the code i am using currently

Public Function GetOpenings(ReqID As String) As String
Dim strTemp As String
Dim strSQL As String
Dim rst As DAO.Recordset
Dim dbs As DAO.Database

strSQL = "SELECT [Opening] FROM tblReqIDOpenings " _
& "WHERE [ReqID]= '" & Format([ReqID], "00000") & "'"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Do While Not .EOF
strTemp = strTemp & !ReqID
.MoveNext
If Not .EOF Then
strTemp = strTemp & ", "
End If
Loop
End With

rst.Close
Set rst = Nothing
Set dbs = Nothing

GetOpenings = strTemp
End Function

ReqID is a long integer in the format of "00000". i have tried playing with the possibility that there is a format difference in the code. anyway, now the program stops at "Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)" with the error "Data Type mismatch in criteria expression"
please help!

[Francois]

Open the module in design view.
From the menu, select Debug, Compile Loaded Modules.
You should get an error message, and when you click OK it should show you in witch line the error is.
Compare that line to Charlotte's code. When I passed the code in my access97 it compiles.

[AccessMan]

when i select "Compile Loaded Modules" it does nothing as far as i can tell. is this a good sign, or am i trying to compile incorrectly or something?

[Francois]

If it says nothing, then it compiles OK.
Can you copy and past the sql syntax of your query ?
Open the query in design mode, select the menu View, Sql view and this is the text.