Setting Recordsource Property causing error?

[EJO]

I was hoping I could get some insight as to what is causing an error...

In an A2k Mdb, with SQL Server backend, I have been updating the record
source properties of several forms/subforms to improve
performance--probably to offset my coding skills ;P. The only form I
have had trouble with is a subform in which i need to actually change
the recordsource. The subform loads fine, but when I choose tab 2, the
field with my PK errors. If any other tab is selected, then go back to
tab 0, the PK field errors. The PK field does not error when selecting
tab 1.


The db has been in use for several months with no problem until I
changed the below:



Dim SRrecordset

Select Case Me.TabCtl37.Value
Case 0
SRrecordset = "Select * From SR Where
SiteCode=Forms!Site.Form!SiteCode;"
Me.RecordSource = SRrecordset
Me.Filter = ("((SR.Cancelled)=False) AND ((SR.Accept_Act) Is
Null))")
Me.FilterOn = True

~some additional pre-existing code~

Case 1
SRrecordset = "Select * From SR Where
SiteCode=Forms!Site.Form!SiteCode;"
Me.RecordSource = SRrecordset
Me.Filter = ("(((Sr.Cancelled)=True) OR ((Sr.Accept_Act) Is Not
Null))")
Me.FilterOn = True

~some additional pre-existing code~

Case 2
SRrecordset = "Select * From SR;"
Me.RecordSource = SRrecordset
Me.FilterOn = False

~some additional pre-existing code~




(Runtime 3070:

The Microsoft Jet Database Engine does not recognize 'SR' as a valid
fieldname or expression.)

Thanks for any assistance,

Eric

 

[L5hopes]

And the pre-existing filter for Case 1 worked? How odd. Filters are not
simply extensions of your original RecordSource query. They take the
RESULT of the query and THEN filter, so table identifiers would be
wrong. That is: you don't filter on "SR.Cancelled=True OR SR.Accept_Act
Is Not Null" etc, just "Cancelled=True OR Accept_Act Is Not Null" etc
-- for all Cases using filters.

By the way, since you're defining the record source in code, why not
incorporate your Filter criteria into the SQL query in the first place,
and only pull the records you actually want from the back-end? Then you
won't need these Filters at all. (Also, if you're not doing a table
join, you don't need to qualify the WHERE criteria fields either;
again, no "SR.Cancelled=", just "Cancelled=", but you probably know
that already.)

--eon--

 

[EJO]

Thank you very much for your critique! Yes, the filter worked. I
understand what you are getting at though. After doing what you
suggested--the smart way--by putting all the criteria in the
sql--duh--the SR field errored for each tab instead of just the two
tabs. Thinking of your statement about table identifiers, i then
changed the control source of the field with the error to SR.SR, and
the field displays properly. I believe when I originally created the
filters I had a similar problem that made me use the identifier. I
don't even know enough about VBA to make a guess as to why (or
programming in general for that matter).

The problem now, after changing the Control Source to SR.SR, is that
many references are not recognizing the field, even if I change it to
SR.SR--probably for some reason related to using the table identifier,
correct? There are no other fields which are having this
problem--perhaps this related to using the field as the PK?

Thanks again!

 

[L5hopes]

Can't quite fathom what you mean in the second paragraph. "references
not recognizing the field". What kind of references? Other controls on
the form? Functions? Event Procedures? What's the context? Are there
any error messages? Perhaps you could post the code of a reference
that's not recognizing the field. Is it from the same module as the tab
using the new RecordSource? Or on another subform, perhaps?

--eon--