Report Record source (A2K (MDE))

[Rupert]

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.

[patt]

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

[charlotte]

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.

[patt]

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>

[WendellB]

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?

[charlotte]

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

[Matz]

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

[HansV]

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

[Matz]

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?

[HansV]

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]"

[Matz]

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]