Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    list box in report detail (97)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list box in report detail (97)

    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>
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list box in report detail (97)

    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>

    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list box in report detail (97)

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

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

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

  9. #9
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

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

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

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

  11. #11
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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>

  12. #12
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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!

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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.
    Francois

  14. #14
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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?

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box in report detail (97)

    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.
    Francois

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •