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

[ebs17]

OP wants to use LIKE *x*

My first thought is always: does it have to be? Because something like that almost triggers physical pain in me, because of strings (byte width) and pattern searches (confused search in a data pile instead of targeted access to shelves in an orderly warehouse) and, as mentioned, the compulsory renunciation of indexes.

But that's the way it is: an intellectually simple and universal solution is often preferred over good data structuring and performance. It is often sufficient for simple tasks and small databases. If it is then scaled to real challenges, the nasty surprise comes.

 

[Pat Hartman]

Don't blame me. I didn't design the app

 

[Josef P.]

Indexed or not, when a filter is applied to docmd.openform (using the misleadingly named criteria parameter) it just means the filtered records are loaded first before display, the rest of the records continue to be loaded in the background.

With which DBMS is this the case? (Or maybe I'm misunderstanding you.)

With the SQL server, only the selected data are loaded.

Example:
Form: TestForm bound to a linked table from SQL-Server.
=>
Call:
docmd.OpenForm FormName:= "TestForm", WhereCondition:= "id = 201"

SQL Profiler output:

SQL:BatchStarting	SELECT "tabTest"."id" FROM "dbo"."tabTest" "tabTest" WHERE ("id" = 201 )
SQL:BatchCompleted	SELECT "tabTest"."id" FROM "dbo"."tabTest" "tabTest" WHERE ("id" = 201 )
SQL:BatchStarting	SET TEXTSIZE 2147483647
SQL:BatchCompleted	SET TEXTSIZE 2147483647
RPC:Completed	declare @p1 int
set @p1=9
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "id","upsize_ts","a","b","M1","M3","c","D1","D2","Betrag","x","JN" FROM "dbo"."tabTest" WHERE "id" = @P1',201
select @p1	Microsoft Office
RPC:Completed	declare @p1 int
set @p1=10
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "id","upsize_ts","a","b","M1","M3","c","D1","D2","Betrag","x","JN" FROM "dbo"."tabTest" WHERE "id" = @P1 OR "id" = @P2 OR "id" = @P3 OR "id" = @P4 OR "id" = @P5 OR "id" = @P6 OR "id" = @P7 OR "id" = @P8 OR "id" = @P9 OR "id" = @P10', 201,201,201,201,201,201,201,201,201,201
select @p1	Microsoft Office

Then nothing more comes. When closing the form exec sp_unprepare is executed.

 

[Pat Hartman]

@Josef P. I believe the discussion was regarding "filters" which is a native Access method that many applications use where a form is bound to a table or a query without criteria and then the filter property is used to limit the data displayed. Using "filters" the way Access does, requires SQL Server et al to return the contents of entire tables/recordsets which are then filtered locally.

 

[CJ_London]

With which DBMS is this the case?

As pat says - the ace (or jet) rdbms

 

[ebs17]

the rest of the records continue to be loaded in the background. So form loading times is not relevant in this respect

You could move the measuring point to

DoCmd.GoToRecord acDataForm, "FormName", acLast
Forms("FormName").Recordset.MoveLast

At least the second statement explicitly addresses the recordset. You could also display the number of records there.
I can't believe there are hidden records in a recordset.

 

[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.

 

[Pat Hartman]

When someone opens the form unfiltered to set a filter afterwards, they want it to run slowly

And that is what the OP is asking for.

 

[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