Trouble setting unbound field on report.

[I'm a Trampoline]

A report of mine is experiencing very erratic behavior. If I filter the
report, I can set an unbound field on it afterwards (it's marked in the
code sample below) but if I don't filter the report (I set the FilterOn
property to False in case the report is already on screen and filtered),
this field turns out empty. I have no idea why, my 'FilterTextString'
exists under both circumstances, whether there's a filter for the report
of not. I can confirm this because I can set that field to "Bananas" and
it behaves the same way, it works when filtered, and doesn't when not
filtered.

Thanks in advance.


Note: the use of the recordset is only for giving the user an error if
there's no records.


Function ShowAndFilterReport()
If CombinedFilterString <> "" Then
Set rs = CurrentDb().OpenRecordset("SELECT * FROM
UnitsAndIssuesQuery WHERE " & CombinedFilterString)
Else
Set rs = CurrentDb().OpenRecordset("SELECT * FROM
UnitsAndIssuesQuery")
End If
If rs.BOF Then
MsgBox "No records were found.", , "Report Query"
Else
DoCmd.OpenReport "IssuesReport", acPreview
If CombinedFilterString <> "" Then
Reports!IssuesReport.Filter = CombinedFilterString
Reports!IssuesReport.FilterOn = True
Else
Reports!IssuesReport.FilterOn = False
End If

'******* The text box in question below *******
'(Even if this is set to "Bananas" it still doesn't work.
Reports!IssuesReport.FilterText.Value = FilterTextString

End If
rs.Close
End Function

 

[Albert D. Kallal]

In place of the filter command, you likely should use the "where" clause.

In you case you go:

load up reocrdset
check for reocrds
load up form (data loads into form).
set filter on form (again data is now filtered).

I count 3 loads of the data above.

That is waste of processing. I going to suggest you use the following:

on Error resume next
DoCmd.OpenReport "IssuesReport", acPreview,,CombinedFilterString

You see, the "where" clause is designed exactly to do what you want. Note
how you actually build your sql with the "where" clause. So, use the above,
and it actually DOES ALL of your code for you.

Now, the only other thing to add is your message box. That msgbox code
simply goes in the reports on-no data event. you can use:

MsgBox "No records were found.", , "Report Query"
Cancel = True

The advanatgaes of the above:

Way less code
Does not load the data 3 times.

So, the above report should also load faster.

Reports!IssuesReport.FilterText.Value = FilterTextString

I would try setting the reports FilterText control in the forms correct
on-format event. So, if FilterText is in the reports header, I would use the
following in the on-format (you as general rule HAVE to use reports format
section to get at the controls, as they are not always active). So, in the
reports on-format , you can go:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Me.FilterText = Forms!MyCoolReprotForm!FilterText

End Sub

The above does mean that you have to reference your filter text somehow. The
above example assume a control. I think in your case, you used a variable,
so you thus could make the variable of the previous form public, and that
would work. In that case, you need to use the dot notation

Me.FilterText = Forms!MyCoolReprotForm.strFilterText

So, you do have to dim strFilterText as public in the forms module...

 

[I'm a Trampoline]

In place of the filter command, you likely should use the "where" clause.

In you case you go:

load up reocrdset
check for reocrds
load up form (data loads into form).
set filter on form (again data is now filtered).

I count 3 loads of the data above.

That is waste of processing. I going to suggest you use the following:

on Error resume next
DoCmd.OpenReport "IssuesReport", acPreview,,CombinedFilterString

You see, the "where" clause is designed exactly to do what you want. Note
how you actually build your sql with the "where" clause. So, use the above,
and it actually DOES ALL of your code for you.

DoCmd's are antiqued aren't they? If so, does anyone know of a website
that details everything I should be doing instead? I hate the help in
Access (it's too techinical for beginner developers), I find it easier
to search the archives of the newsgroups for answers to questions.

Now, the only other thing to add is your message box. That msgbox code
simply goes in the reports on-no data event. you can use:

MsgBox "No records were found.", , "Report Query"
Cancel = True

I was having trouble with the OnNoData event for some reason last time
(it was happening inside another function running elsewhere, which
should't happen this time), which is why I resorted to using a recordset
to get the recordcount (reports don't have them do they?). I did think
at the time it was a pretty ugly thing to be doing, and fully expected
my code to be dissected on principle alone (even if unrelated to the
other issue).

It does take a loooooong time to open the report, but everything takes a
long time on our network so I didn't have much of a frame of reference.
Our desktops to run directly off the server at work, so I can't even get
my front end running from the hard drive (locked out) unless I can get a
special exemption organised for all the required computers.

The advanatgaes of the above:

Way less code
Does not load the data 3 times.

So, the above report should also load faster.





I would try setting the reports FilterText control in the forms correct
on-format event. So, if FilterText is in the reports header, I would use the
following in the on-format (you as general rule HAVE to use reports format
section to get at the controls, as they are not always active). So, in the
reports on-format , you can go:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Me.FilterText = Forms!MyCoolReprotForm!FilterText

End Sub

The above does mean that you have to reference your filter text somehow. The
above example assume a control. I think in your case, you used a variable,
so you thus could make the variable of the previous form public, and that
would work. In that case, you need to use the dot notation

The variable was public, in a module.

Thanks for your help.

 

[Albert D. Kallal]

DoCmd's are antiqued aren't they?

Well, they are very old, but they are the STANDARD way to open forms, and
open reports. (so, no, they are no some old commands kept for compatibility
reasons..but are "THE" way all developers open their reports and forms (so,
your use of the docmd is just fine).

Our desktops to run directly off the server at work, so I can't even get
my front end running from the hard drive (locked out) unless I can get a
special exemption organised for all the required computers.

In fact, the above sounds like Citrix (windows terminal services). Even in
the above case, you still need to run a split front end/back end setup, and
EACH USER STILL GETS THEIR OWN COPY of the front end. (right..is anything
un-clear about the English language I used here?).

Of course, then all your application is on the server, but each user that
*logs* onto that server gets their own desktop, and STILL GETS their own
copy of the front end! (you must ensure that multiple users do NOT share the
same front end. (back end..of course!).

 

[I'm a Trampoline]

Well, they are very old, but they are the STANDARD way to open forms, and
open reports. (so, no, they are no some old commands kept for compatibility
reasons..but are "THE" way all developers open their reports and forms (so,
your use of the docmd is just fine).





In fact, the above sounds like Citrix (windows terminal services).

Whatever it is, it means my IE shortcut bar takes about 4 seconds to be
drawn for each new window I open. I know our desktops used to be local,
and updated to the server upon loggin off, but they changed it after a
bulk ghosting session a while back. Another database's front end I had
was noticably slower afterwards, probably about half the speed when it
came to drawing windows, menubar/toolbar manipulation etc.

Even in
the above case, you still need to run a split front end/back end setup, and
EACH USER STILL GETS THEIR OWN COPY of the front end. (right..is anything
un-clear about the English language I used here?).

Don't worry, each user does get their own front end. Everything's
working sweet with it now, I changed the report to use the where
argument on the DoCmd (and ditched everything else), and used the Format
event for that field. The report is still as slow to open, but I think
that's just out network.