Public variables not working...

[Matthew Wells]

Good Morning All!!

I have used a practice for years that is suddenly not working. If I need a
parameter for a report, I use an input box to get the data and put it in a
public module level variable in the report. The report's record source uses
that public variable in its criteria. For example (stripped of validations,
etc)

Public sYear as string
_______________________________
private sub Report_OnOpen
sYear = inputbox("Enter Year", "Year")
end sub

The recordsource query is
Select ClientName, ClientDOB
From Clients
Where CStr(Year([ClientDOB])) = Reports!MyReport.sYear

Note the . instead of !

This has worked for me for years. Does anyone know why this should suddenly
stop working?

Using Access 2003 front and back end with Access 2007 also installed but
never used with Windows 7 Ultimate.

Thank you.

--
Matthew Wells
matthe...@firstbyte.net

[Rich P]

Here is a method that should work consistently:

Option Compare Database
Option Explicit

Dim rs As Recordset, sYear As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not rs.EOF Then
txtbox1 = rs!ClientName
txtbox2 = rs!ClientDOB
rs.MoveNext
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
strSql = "Select ClientName, ClientDOB From Clients " _
& "Where CStr(Year([ClientDOB])) = '" & sYear & "'" Me.RecordSource =
strSql
Set rs = CurrentDb.OpenRecordset(strSql)
End Sub

--Report_Open and Detail_Format are events in the Report object like
Form_Load in the Form object.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Need to clean this up a bit -- in the Report code module you can add
code as follows -- you will have added two textboxes in the report
Detail section -- txtbox1 and txtbox2 in this example:

Option Compare Database
Option Explicit

Dim rs As Recordset, sYear As String

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

If Not rs.EOF Then
txtbox1 = rs!ClientName
txtbox2 = rs!ClientDOB
rs.MoveNext
End If

End Sub

Private Sub Report_Open(Cancel As Integer)

Dim strSql As String
strSql = "Select ClientName, ClientDOB From Clients " _
& "Where CStr(Year([ClientDOB])) = '" & sYear & "'"

Me.RecordSource = strSql '--need this so the report
'--knows how many records it has

[Salad]

Matthew Wells wrote:

I don't see you needing to modify the recordsource. Maybe this will be
acceptable in the OnOpen event.

It might look like this instead
private sub Report_OnOpen
Dim sYear as String

sYear = inputbox("Enter Year", "Year")

If Isnumeric(sYear) then
me.filter = Year([ClientDOB]) = Clng(sYear)
Endif
'shows all recs or for specific year entered
Me.Filter = IsNumeric(sYear)
end sub

[Matthew Wells]

Thanks for all the advice, but none is the answer to the question. Yes
there are myriad ways to accomplish this. I don't want to hurt performance
by calling the detail_format event for 10,000 records. The point is that I
have done it the way I showed for years and it suddenly won't work. This is
an existing report that has been in use for 7 years and suddenly won't work.
Hasn't anyone else used this technique?

--
Matthew Wells
matthe...@firstbyte.net

"Rich P" <rpn...@aol.com> wrote in message
news:4b4214b9$0$48215$815e...@news.qwest.net...

[David W. Fenton]

"Matthew Wells" <matthe...@firstbyte.net> wrote in
news:zcm0n.3917$XU....@newsfe03.iad:

> Where CStr(Year([ClientDOB])) = Reports!MyReport.sYear

I think it's very odd that this ever worked. I don't know that you
could ever refer to variables, even public members of a class
module, directly. I've only seen function wrappers for that purpose.

I know you want to have the old way work, but if you can't get it to
work, I'd suggest using the report's OnOpen event to set the
report's RecordSource, with the criteria resolved in VBA code:

Dim strRecordSource As String

sYear = InputBox("Enter Year", "Year")
' do what you need to do to validate that they typed
' something appropriate
strRecordSource =" Select ClientName, ClientDOB From Clients "
strRecordSource = strRecordSource & "Where CStr(Year(ClientDOB))
=" strRecordSource = strRecordSource & sYear
Me.RecordSource = strRecordSource

This avoids any issues with the Access/Jet Expression Services.

I would never use an InputBox() for anything but trivial one-off
purposes. I'd likely use an unbound dialog form in the OnOpen event,
and allow only valid years to be chosen based on the DOB field,
either from a combo box (for a single value) or with a multiselect
listbox (so you could accomodate the choice of more than one year at
a time).

Also, I'd likely code the criteria as:

WHERE ClientDOB >= #" & "1/1/" & sYear
AND ClientDOB < #" & "1/1/" & CLng(sYear) - 1

The reason for this is taht it will use the indexes, whereas your
criteria can't (because you're comparing the output from functions).

You might also consider converting the sYear variable to a numeric
data type, as you'll have to coerce it to a numeric type otherwise.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Marshall Barton]

I never thought of using a report property like that, but I
see no reason for it to fail. My guess is that either you
made some other change that broke it or the db has become
corrupted. At least, check to make sure that sYear is not
the name of something else in the report. If that's not it,
try decompiling and importing everything to a new, blank mdb
file.

--
Marsh

[Matthew Wells]

Well this has been driving me nuts. I did some research and found another
post where this was happening. One guy had two machines running the same
mdb and it worked on his XP box but not his Vista and some other
combinations. I went to an old contract site and this technique is still
working there using Access 2003 and XP. Weird. I also used to use this
method to put the entered date in a text box on the report header like
textbox.controlsource = "Report for : " & reports!thisreport.publicvariable

I have this a lot of places that are suddenly not working. It looks like
I'm going to have to either build my SQL string on the fly which I don't
like to do. I like to keep recordsources in saved queries. I'll have to
build the SQL since I need the user supplied date in the text box header.
I'll substitute the reports!thisreport... with the added date field since it
will now be part of the query. It's either this or build a form to store
report parameters. That's even more work.

Anyone have any better ideas?

Thanks.

--
Matthew Wells
matthe...@firstbyte.net

"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9CF6D179DC280f9...@74.209.136.90...

[Salad]

Matthew Wells wrote:
> Well this has been driving me nuts. I did some research and found
> another post where this was happening. One guy had two machines
> running the same mdb and it worked on his XP box but not his Vista and
> some other combinations. I went to an old contract site and this
> technique is still working there using Access 2003 and XP. Weird. I
> also used to use this method to put the entered date in a text box on
> the report header like
> textbox.controlsource = "Report for : " & reports!thisreport.publicvariable

I'd probably use a label and set the caption.

>
> I have this a lot of places that are suddenly not working. It looks
> like I'm going to have to either build my SQL string on the fly which I
> don't like to do. I like to keep recordsources in saved queries. I'll
> have to build the SQL since I need the user supplied date in the text
> box header. I'll substitute the reports!thisreport... with the added
> date field since it will now be part of the query. It's either this or
> build a form to store report parameters. That's even more work.
>
> Anyone have any better ideas?
>

I prefer a record source in a report like
"Select * from Customers"

Then I can open the report with a filter
Docmd.OpenReport "Test",,,"CustomerID = 123"
or I can open the report and filter
Me.Filter = "CustomerID = 123"
Me.FilterOn = True
but that's my preference. I don't see the need to modify a recordsource
simply for a filter.

Better ideas? For you? No, as there doesn't seem to be a magic bullet
that will fix it without intervention. Maybe create a new database,
import all reports, do a text search for a keyword, like OnOpen, and fix
where needed. Then in the orig mdb, delete all reports and import the
fixed reports from the other mdb.

> Thanks.
>

[David W. Fenton]

Salad <sa...@oilandvinegar.com> wrote in
news:pMOdnTYTH5gJQtnW...@earthlink.com:

> I don't see the need to modify a recordsource
> simply for a filter.

I don't either. Indeed, I don't often use saved QueryDefs for
recordsources of forms or reports.

[Salad]

David W. Fenton wrote:

> Salad <sa...@oilandvinegar.com> wrote in
> news:pMOdnTYTH5gJQtnW...@earthlink.com:
>
>
>>I don't see the need to modify a recordsource
>>simply for a filter.
>
>
> I don't either. Indeed, I don't often use saved QueryDefs for
> recordsources of forms or reports.
>

Same.