Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Record source (A2K (MDE))

    I have a form on which there are several combo boxes. The operator can select various options from these combo boxes, the options selected are used in an SQL statement which is then used for the record source of a sub form within this form.

    I also use the same SQL statement as the row source for a report so that I can print out the sub form information easily.

    However I now want to distribute the database in the MDE format. And I get the following error:

    you can't set the record source property after the printing has started

    I understand this is caused because changes cannot be made to reports in an M. D. E. database.

    I believe the solution is to use the SQL statement to create a query, and then used that query as the row source for the report. I have done this before, but before I make this modification, are there any other ways that I have overlooked?

    Any comment, advice gratefully received.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report Record source (A2K (MDE))

    >>I believe the solution is to use the SQL statement to create a query, and then used that query as the row source for the report. <<
    Can you make a change to a query in an MDE?
    If you cannot, I would build up a criteria for the OpenReport statement.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Report Record source (A2K (MDE))

    Yes, you can change queries in an MDE. You can change table designs too and macros. It's only the code containers, which include Forms and Reports that are locked down in an MDE.
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report Record source (A2K (MDE))

    Thanks Charlotte, changing or creating queries will grow the database, so I would tend to go with the Criteria in the OpenReport statement.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Report Record source (A2K (MDE))

    I think something else may be going on here.

    <<you can't set the record source property after the printing has started>>
    Are you printing the subform at this point? You are correct that you cannot make design changes to forms or reports (or code), but you should be able to set the record source for a form or subform in code - we do it pretty regularly in library databases which we distribute as MDEs. Your error message suggests that you are trying to change the record source after you have started printing - do you have code running on the OnFormat of a report that uses the subform?
    Wendell

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

    Re: Report Record source (A2K (MDE))

    An MDE doesn't bloat anything like an MDB does, and compacting it reclaims the space.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Record source (A2K (MDE))

    Hi, i know this post was a while ago, but has been VERY helpful in getting round the smae problem for me.

    I have put my code in the report's open event

    Private Sub Report_Open(Cancel As Integer)
    DoCmd.Maximize
    Dim strRecSource As String, strP As String
    strRecSource = Forms!frm_updateall.sbfrm_Protocols.Form.RecordSou rce
    Me.RecordSource = strRecSource
    End Sub

    the report checks the related subform's record source.

    How would you incorporate SQL into this? Say if i wanted to put a filter on certain record sources. On a certain record source i want to filter on field [Dep].
    would i do:

    If strRecSource = "test" Then
    <sql code for filter here>
    End If

    cheers
    Thanks,

    pmatz

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Record source (A2K (MDE))

    Can you explain in more detail what you want to do with "filter on field [Dep]"?

  9. #9
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Record source (A2K (MDE))

    Ok, sorry.

    i have a form which displays different departments, and each of these departments have thier own records. The subform is connected to this, so it displays the records by department. I want to be able to set a filter on the report to reflect this, but with any of the records sources (which query the data on set criteria - such as within dates or fecords etc.)

    i need the report to check what the main form's [DEP] field is, and then do a filter on this. Sometimes the main form's [DEP] field is not relevant, for instance when the record source of the subform is set to a query which displays all departments.

    does that make sense?
    Thanks,

    pmatz

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Record source (A2K (MDE))

    I'm not sure I understand, but perhaps you can use something like

    strRecSource = Forms!frm_updateall.sbfrm_Protocols.Form.RecordSou rce
    Me.RecordSource = "SELECT * FROM " & strRecSource & " WHERE DEP = [Forms]![frmupdateall]![DEP]"

  11. #11
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Record source (A2K (MDE))

    Well, thats done it! I had to do some conditional stuff, but apart from that it works fine! Thanks Hans. I will have to show you this dB when its fully finished [img]/forums/images/smilies/smile.gif[/img]
    Thanks,

    pmatz

Posting Permissions

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