SQL Backend, Access Frontend - Filtering problem - plz help

[alecwood via AccessMonster.com]

Hi All

I've been asking this on lots of forums but noone answers

I wonder if someone can assist me. I've got an inherited access database
which for the most part uses bound forms (about 40 of them) to an access
back-end. Database is in continuous use by around 10 clients. I am not very
experienced with databases, but with a big pile of books I am learning.

We have a need to make some of the data accessible to customers outside, so
on the advise of various in the know people I have upsized to an Access
Project which will allegedly make things more reliable, and make presenting
data by internet easier, as well as coping with an expected surge in data
volume, but now my Access frontend is broken

The crux of my problem is that I had used the following on-click event, or a
very similar one, in most of my forms to search the database
DoCmd.ApplyFilter "",
"[t_MainData]![nAmk_ID]=[Forms]![frmPreInsp]![SearchDetails]"

This doesn't work and gives me an error "Run-time error '30100'; Cannot
apply Filter on one or more fields specified in the Filter property."

I have searched these forums, all my books, google, microsoft.com etc
without success.

Can anyone help point me in the right direction?

 

[Sylvain Lafontaine]

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &
[Forms]![frmPreInsp]![SearchDetails]

Personnally, I don't use filter or server filter, so I cannot help you
further is this doesn't help. The best way for you would be to change the
record source of the form so to take into account this filtering when
necessary.

 

[Sylvain Lafontaine]

Also, I don't understand the Left part of the equality sign:
"[t_MainData]![nAmk_ID]=

Is t_MainData the name of the main form followed by the name of a control or
if this is the name of one of the table of the query?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &
[Forms]![frmPreInsp]![SearchDetails]

Personnally, I don't use filter or server filter, so I cannot help you
further is this doesn't help. The best way for you would be to change the
record source of the form so to take into account this filtering when
necessary.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All

I've been asking this on lots of forums but noone answers

I wonder if someone can assist me. I've got an inherited access database
which for the most part uses bound forms (about 40 of them) to an access
back-end. Database is in continuous use by around 10 clients. I am not
very
experienced with databases, but with a big pile of books I am learning.

We have a need to make some of the data accessible to customers outside,
so
on the advise of various in the know people I have upsized to an Access
Project which will allegedly make things more reliable, and make
presenting
data by internet easier, as well as coping with an expected surge in data
volume, but now my Access frontend is broken

The crux of my problem is that I had used the following on-click event,
or a
very similar one, in most of my forms to search the database
DoCmd.ApplyFilter "",
"[t_MainData]![nAmk_ID]=[Forms]![frmPreInsp]![SearchDetails]"

This doesn't work and gives me an error "Run-time error '30100'; Cannot
apply Filter on one or more fields specified in the Filter property."

I have searched these forums, all my books, google, microsoft.com etc
without success.

Can anyone help point me in the right direction?

 

[alecwood via AccessMonster.com]

It's in the VBA code for the form in the access front end.

tMainData is the data table to which the form is bound, and nAmk_ID a column
of that table.

[Forms]![frmPreInsp]![SearchDetails] is an unbound text box, "SearchDetails"
on the form "frmPreInsp". So, the Docmd.ApplyFilter sets a filter on the
current recordset to show only those records where the column "nAmk_ID" is
same as contents of the SearchDetails textbox, entered by the user.

Because it's bound directly to the table - it's an upsized access database -
there's no recordset for me to alter using commands such as Me.Recordset as
far as I know.

I guess really I am expecting someone to tell me I'll just have to rewrite
the thing, but since that's such a mammoth task given my current skill level,
I was hoping someone might throw me a lifeline so I can postpone that
particular joy for a later time.

Alec

Also, I don't understand the Left part of the equality sign:
"[t_MainData]![nAmk_ID]=

Is t_MainData the name of the main form followed by the name of a control or
if this is the name of one of the table of the query?

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &

[quoted text clipped - 36 lines]

 

[Sylvain Lafontaine]

If your frontend an ADP file or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

It's in the VBA code for the form in the access front end.

tMainData is the data table to which the form is bound, and nAmk_ID a
column
of that table.

[Forms]![frmPreInsp]![SearchDetails] is an unbound text box,
"SearchDetails"
on the form "frmPreInsp". So, the Docmd.ApplyFilter sets a filter on the
current recordset to show only those records where the column "nAmk_ID" is
same as contents of the SearchDetails textbox, entered by the user.

Because it's bound directly to the table - it's an upsized access
database -
there's no recordset for me to alter using commands such as Me.Recordset
as
far as I know.

I guess really I am expecting someone to tell me I'll just have to rewrite
the thing, but since that's such a mammoth task given my current skill
level,
I was hoping someone might throw me a lifeline so I can postpone that
particular joy for a later time.

Alec

Also, I don't understand the Left part of the equality sign:
"[t_MainData]![nAmk_ID]=

Is t_MainData the name of the main form followed by the name of a control
or
if this is the name of one of the table of the query?

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &

[quoted text clipped - 36 lines]

Can anyone help point me in the right direction?

 

[aaron.kempf]

yeah I don't believe in filter or serverfilter also

I would merely change the recordsource to include the where clauses
that you need

hope that helps

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &
[Forms]![frmPreInsp]![SearchDetails]

Personnally, I don't use filter or server filter, so I cannot help you
further is this doesn't help. The best way for you would be to change the
record source of the form so to take into account this filtering when
necessary.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All

I've been asking this on lots of forums but noone answers

I wonder if someone can assist me. I've got an inherited access database
which for the most part uses bound forms (about 40 of them) to an access
back-end. Database is in continuous use by around 10 clients. I am not
very
experienced with databases, but with a big pile of books I am learning.

We have a need to make some of the data accessible to customers outside,
so
on the advise of various in the know people I have upsized to an Access
Project which will allegedly make things more reliable, and make
presenting
data by internet easier, as well as coping with an expected surge in data
volume, but now my Access frontend is broken

The crux of my problem is that I had used the following on-click event, or
a
very similar one, in most of my forms to search the database
DoCmd.ApplyFilter "",
"[t_MainData]![nAmk_ID]=[Forms]![frmPreInsp]![SearchDetails]"

This doesn't work and gives me an error "Run-time error '30100'; Cannot
apply Filter on one or more fields specified in the Filter property."

I have searched these forums, all my books, google, microsoft.com etc
without success.

Can anyone help point me in the right direction?

- Show quoted text -

 

[alecwood via AccessMonster.com]

It is ADP

If your frontend an ADP file or a MDB file with linked tables?

It's in the VBA code for the form in the access front end.

[quoted text clipped - 34 lines]

 

[alecwood via AccessMonster.com]

I know recordsource is better, but it will take me a long time to convert the
entire front-end to use recordsets and unbound forms.

Alec

yeah I don't believe in filter or serverfilter also

I would merely change the recordsource to include the where clauses
that you need

hope that helps

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &

[quoted text clipped - 47 lines]

- Show quoted text -

 

[Sylvain Lafontaine]

I've tried to reproduce your problem but with no success; ie. everything
looks fine when I apply a filter. Even using ! instead of . in
"[t_MainData]![nAmk_ID]" or removing the part [t_MainData] change nothing.

The only thing that I can see that would cause an error would be that
[nAmk_ID] is the name of the control on the form and not the name of the
field in the underlying recordset or table.

The error message make a reference to the name of a filter, are you sure
that you using "" or nothing as the name of the filter?

Finally, is SearchDetails a string or a number? In the case of a string,
you must enclose it between quote. However, if I make the test on my side,
I got the error 7874 in this case and not the error 30100. (BTW, searching
for the error 30100 and JET or Access on Google gives nothing.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

It is ADP

If your frontend an ADP file or a MDB file with linked tables?

It's in the VBA code for the form in the access front end.

[quoted text clipped - 34 lines]

Can anyone help point me in the right direction?

 

[alecwood via AccessMonster.com]

It works for me as a split access database, but in all cases I get this same
failure when I upsize to an Access project with an SQL server backend.

SearchDetails is a number in this case.

I'm going to go and check all the control names now, make sure they match the
field names accurately.

Thanks for the input guys.

Alec

I've tried to reproduce your problem but with no success; ie. everything
looks fine when I apply a filter. Even using ! instead of . in
"[t_MainData]![nAmk_ID]" or removing the part [t_MainData] change nothing.

The only thing that I can see that would cause an error would be that
[nAmk_ID] is the name of the control on the form and not the name of the
field in the underlying recordset or table.

The error message make a reference to the name of a filter, are you sure
that you using "" or nothing as the name of the filter?

Finally, is SearchDetails a string or a number? In the case of a string,
you must enclose it between quote. However, if I make the test on my side,
I got the error 7874 in this case and not the error 30100. (BTW, searching
for the error 30100 and JET or Access on Google gives nothing.)

It is ADP

[quoted text clipped - 5 lines]

 

[aaron.kempf]

contrl names should NOT match field names

you're just asking for confusion / trouble / bugs

It works for me as a split access database, but in all cases I get this same
failure when I upsize to an Access project with an SQL server backend.

SearchDetails is a number in this case.

I'm going to go and check all the control names now, make sure they match the
field names accurately.

Thanks for the input guys.

Alec

I've tried to reproduce your problem but with no success; ie. everything
looks fine when I apply a filter. Even using ! instead of . in
"[t_MainData]![nAmk_ID]" or removing the part [t_MainData] change nothing.

The only thing that I can see that would cause an error would be that
[nAmk_ID] is the name of the control on the form and not the name of the
field in the underlying recordset or table.

The error message make a reference to the name of a filter, are you sure
that you using "" or nothing as the name of the filter?

Finally, is SearchDetails a string or a number? In the case of a string,
you must enclose it between quote. However, if I make the test on my side,
I got the error 7874 in this case and not the error 30100. (BTW, searching
for the error 30100 and JET or Access on Google gives nothing.)

[quoted text clipped - 5 lines]

Can anyone help point me in the right direction?

 

[aaron.kempf]

if I were you, I would not touch recordSETS with a 10 foot pole

but rewriting to use recordSOURCES?

it's just more efficient to only load the records you need

I know recordsource is better, but it will take me a long time to convert the
entire front-end to use recordsets and unbound forms.

Alec

yeah I don't believe in filter or serverfilter also

I would merely change the recordsource to include the where clauses
that you need

hope that helps

Try something like:
DoCmd.ApplyFilter "", "[t_MainData]![nAmk_ID]=" &

[quoted text clipped - 47 lines]

- Show quoted text -