How to open single form at a blank record when NOT using data entry (1 Viewer)

[mounty76]

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

 

[theDBguy]

In the Load event, you could try:

DoCmd.GoToRecord , , acNewRec

 

[Gasman]

Perhaps do not set the form recordsource until something is typed into your control?

 

[mounty76]

In the Load event, you could try:

DoCmd.GoToRecord , , acNewRec

Didn't work unfortunately have this set

 

[mounty76]

Perhaps do not set the form recordsource until something is typed into your control?

How do I do that?

 

[Gasman]

How do I do that?

set form recordsource vba - Google Search

 

[Gasman]

Didn't work unfortunately have this set

What does that mean?

 

[theDBguy]

Didn't work unfortunately have this set

I just gave it a try, and it worked for me. Wonder why it didn't work for you...

 

[Pat Hartman]

LIKE, when used that way prevents the use of any index so the database engine always has to perform a full table scan to return selected records. AND it always returns all records when no criteria is specified

I would bind the form to a query that uses "Where somefield = 0" where 0 is not a valid option. Then change the recordsource when the user uses the search box. That will open the form to a new record where you can start typing immediately.

 

[ebs17]

or ideally nothing

WHERE False
WHERE 1 = 0

Only data records for which the selected filter results in True are displayed.
So set the filter explicitly to False or to a condition that can never result in True - of course without an OR link to other conditions.

 

[Pat Hartman]

I don't ever use filters since my BE's are almost always RDBMS or are likely to be converted in the future. So, starting out with good client/server techniques is my style. That way I have ONE development pattern regarding data acquisition and it works whether the BE will be Jet/ACE or SQL Server or some other RDBMS. Saves a lot of brain strain and when the time comes to upsize an application I built, I can be a hero and do it in an afternoon. And it only takes that long because you always need to extensive testing after a conversion to ensure you haven't missed anything.

 

[ebs17]

I think loading an empty form (combo box, list box) and thus using a filter when loading is a good idea, especially when the data source is very large, I don't want to see everything and I know I need to filter immediately anyway. Loading the form or controls is much faster.

 

[CJ_London]

Set your form recordsource to something like

SELECT * FROM myTable WHERE False

in your textbox after update event put something like

me.recordsource="SELECT * FROM myTable WHERE empName like '*" & txtboxname & "*'"

or perhaps modify your query to something like

SELECT * FROM myTable WHERE empName like "'*" & forms!myform.form.txboxname & "*' AND nz(forms!myform.form.txboxname,"")<>""

 

[Pat Hartman]

I think loading an empty form (combo box, list box) and thus using a filter when loading is a good idea

Let's be precise with our terminology. Are you suggesting using forms bound to a query that initially returns nothing but then changing the recordsource to a table or query with no criteria and applying the Filter property? Because in that case, all the data is brought down from the server into local memory and then Access filters the local data. If the BE is Jet/ACE, this operation does not seem to be inefficient but if the BE is SQL Server, it goes against the firm suggestion to retrieve ONLY the data you need at this time from the server. That means using select by column name and including a Where or Having clause to get the server to do the heavy lifting and to minimize LAN traffic.

OR

Are you using the generic meaning of Filter which would include selection criteria in a query as I suggested?

I repeat - if your BE is Jet or ACE, Access is so closely bound to those two database engines that the process of local filtering is not inefficient. However, if you are using SQL Server or some other RDBMS or see the potential for later up-sizing, you will be far better off using good client/server techniques from the beginning. When we see posts from people who upsize applications built using old style Jet/ACE techniques like forms bound to tables and local filtering, and they are writing because the converted app is way too slow, their bound forms are the reason. And it is not because the forms are bound, it is because they are not using good practices to make data retrieval as efficient as it can be.

 

[ebs17]

Because in that case, all the data is brought down from the server into local memory and then Access filters the local data.

This is often said, but it is not necessarily correct.
Source: Michael S. Kaplan , on his now defunct website: trigeminal.com

Jet can work with indexes and will only load the selected records and not the whole table from a Jet backend when used for index-supporting filters. Index usage is an all-time important topic for me.
My expectation would be the same if the filter was set to False as the only condition, i.e. that no data would be loaded.

Such cases (entire table, filter without index use, filter with index use, filter on False) can be tested for yourself and form loading times can be measured and conclusions can be drawn quite easily.

Jet and TSQL work quite well together, but of course not 1:1. Communication problems will be reflected in additional steps and thus in additional loads of data. In good cases, however, a filter will be run by TSQL and not sent to the server via Jet via ODBC => ADODB.Recordset on TSQL-Query?

 

[CJ_London]

Jet can work with indexes and will only load the selected records and not the whole table from a Jet backend when used for index-supporting filters.

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. So form loading times is not relevant in this respect. Although I totally agree that filters/criteria applied to indexed fields will be significantly faster than non - indexed fields

Easy to check - ensure your form has the navigation bar displayed. Open it with a filter applied to the criteria parameter, You will see the filter option is 'active'. Click on it to remove the filter and all records are displayed - subject to any actual criteria.

 

[ebs17]

it just means the filtered records are loaded first before display, the rest of the records continue to be loaded in the background

That would be exciting. So I load with Filter = False, but I would still have all the records available locally and could continue to work with them, even if the connection to the backend was lost? Crashes due to short-term network interruptions are not an issue?

This can be a profitable topic.
Stupid me, I load table contents that should be permanently available locally, e.g. due to performance problems when loading in ongoing processing, explicitly as a copy into the local environment, of course then with the problems of the generated redundancy.

 

[Uncle Gizmo]

Create a query that is based on the table underlying the form.

Open the query in the query designer grid and set the criteria for the unique ID to zero "0"

Use this query as your forms record source, either the query directly, or the query SQL string, whichever you prefer. I prefer using the SQL string, others prefer using the query....

 

[CJ_London]

could continue to work with them, even if the connection to the backend was lost?

No - lose the connection you lose the recordset. Dao needs to have a table/query to reference.

if connections are intermittent I would use an ado disconnected recordset but involves a lot more work if you need to reconnect to update. There are other issues with ado such as right click filter/sort won’t work, you need to write your own right click menus if required


but good for things like combos and listboxes which don’t need constant updating

 

[Pat Hartman]

Jet can work with indexes and will only load the selected records and not the whole table from a Jet backend when used for index-supporting filters. Index usage is an all-time important topic for me.

I have specifically mentioned the difference between Jet/ACE and SQL Server. PLUS the OP wants to use LIKE *x* which will prevent the use of ANY index regardless of which BE is connected to,.