How to open single form at a blank record when NOT using data entry

[Josef P.]

Using "filters" the way Access does, requires SQL Server et al to return the contents of entire tables/recordsets which are then filtered locally.

What do you mean specifically? Maybe I don't use this "access way" myself.
Docmd.OpenForm ... , WhereCondition:= ...
is equivalent in behavior to

Form.Filter = ...
Form.FilterOn = true

(When someone opens the form unfiltered to set a filter afterwards, they want it to run slowly. )
In both cases, Access will attempt to pass the criteria to the DBMS, retrieve the primary key values, and then load the matching data. (Of course, this only works if the expression is SQL-enabled.)

As pat says - the ace (or jet) rdbms

Unfortunately, I don't know how to check the data retrieval at ACE/Jet. (The jet showplan does not say much about this.)
Perhaps with a query with a calculated field and a function that logs the queried values. However, this does not necessarily say anything about the loading behavior, since the calculation could still run just before the display.

 

[Josef P.]

Eberhard already showed one possible way.
docmd.OpenForm FormName:= "TestForm", WhereCondition:= "1=0"
Once the form is loaded, you can use the Filter property with VBA to load the desired records.

I prefer to use an unbound form in my applications to set the filter conditions and then filter a subform.
Then I can use a linked table, ADODB recordset (select statement or call of stored procedure) as needed to fetch the appropriate data. (I use only active DBMS.)

 

[spaLOGICng]

Hello All,

I have a query suppling a subform in a tabbed form.

In the form I have a textbox that acts as the criteria for the query (LIKE "*" & ...... & "*" ) when the form it opened it returns all the records until a surname if typed into the textbox, so it works fine.

Problem is that I want the form to open to show a blank record (or ideally nothing) but not as a data entry form as then the search doesn't work. I have tried to put autonumberID 1 as a blank record but then it just doesn't show in the query.

Any ideas?

Thanks

It sounds to me like you have AllowNewRecords set to false. If you have DataEntry set to True, it will not show records.

Also, while I hate tab controls, preferring navigation controls, If I have a filtering mechanism on a subform, it needs to located in the header or footer section.

Try working with the Filter instead of query. However, I prefer to uses TempVars in Queries in the manner you are using it.

If you work with filers, the filteron property needs to be set to true. Data entry needs to be False.

As for your specific need, using TempVars, I will set the value to anything else that I know will not be in the search column. I will generally use -1 or 0 for number columns or zzzzzzzzzzz for text columns