Problems with filtering

[zaphod2003]

I have an access front end pointing to a database on an SQL server.
This works OK, but when I create a form to examine the contents of a simple table the form shows no records until I use the "filter by form" options and click close without typing anything, then the form gets populated correctly.

I'm not invoking any filtering by default and tried to disable all filters which rendered all records unviewable. I cannot find any option which would cause this effect.

Any suggestions?

[OBP]

Are you populating your Form via a Query?
If not try creating a query and check that it is being populated from the SQL server, then change the record source of your form to the Query.

[zaphod2003]

Are you populating your Form via a Query?
If not try creating a query and check that it is being populated from the SQL server, then change the record source of your form to the Query.


No I am not populating via a query. I simply used for the form wizard to point directly to the table.

Just a couple of questions about your idea:

(a) How does this work? Say for example, I use a query to get all the records how do I send data back to the table that has been changed? I guess its some form of update function (see also point b),
(b) The tables will grow to be quite large. How do I ensure that I don't end up transferring MBs of data between the form and server (or does Access take care of this)?

[OBP]

It works just like using the table, except you can filter and sort the records in the query before they get to the Form.

[geekgirlau]

Generally speaking, it's a good idea in Access to look at filtering before you show the data. This avoids the problem you mentioned of loading millions of records unnecessarily.

One method I use fairly frequently is to create an unbound form - a form with no data source. At the top of the form I have one or more filter fields, where users either select options from a combo box, or type text or date criteria. Once they have entered the filter criteria, you then set the data source for your form as an SQL string incorporating the filter criteria. If your data is in SQL Server, you can create a pass-thru query on the fly and use this as your data source - it's lightening fast compared with opening the whole table.

One other thing to mention - you need to test your query to make sure it's updateable. As long as the data in the query can be updated, it will handle changes in the same way as a table as OBP mentioned.