Solved Adding criteria to record source query if...

[zeroaccess]

Hey All,

I've searched for answers on this but keep coming up with various ways of adding WHERE statements. I do not want to add a WHERE.

I am trying to pull only the record(s) I need to work with. A WHERE seems to pull the recordset and filter on the local machine. It's like asking the library to send you ALL the books, then YOU will decide which one you want. At least, that is what I'm seeing in the navigation bar.

Query Criteria limits the records returned to the user, which is preferred.

So, say I want to open an existing record from a record list, which opens in my popup data entry form. I want to move away from the current method of opening WHERE the ID on the form = the ID on the list, to adding the record's ID as criteria to the record source query. Then when the form opens, you should only see 1 record to cycle and no filter. Minimal memory use, minimal ask on the server side of things.

So a way to insert the ID as criteria is what I need. It would be easy enough to just type an IF statement into the Criteria, but I don't think that's possible.

Thank you for any insights.

 

[theDBguy]

Hi. Probably lots of other ways to do it, but just as a thought, try using a query with a criteria for your form. I would suggest using a TempVars for the criteria; and before you open the form, set the value of the TempVar.

 

[zeroaccess]

Hi. Probably lots of other ways to do it, but just as a thought, try using a query with a criteria for your form. I would suggest using a TempVars for the criteria; and before you open the form, set the value of the TempVar.

I have to keep in mind that since we're talking about clicking on an existing record in the record list to pop up that record in the form, it's different than adding new. If you hit NEW, it's going to do:

OpenForm acFormAdd

As discussed previously, which is great. Only 1 record exists in the form to cycle on the navigation bar using this method. So when clicking NEW, I want to avoid the record source query throwing any errors if it's looking for an ID on the record list and you're not on the record list or otherwise not supplying one.

 

[theDBguy]

I have to keep in mind that since we're talking about clicking on an existing record in the record list to pop up that record in the form, it's different than adding new. If you hit NEW, it's going to do:

OpenForm acFormAdd

As discussed previously, which is great. Only 1 record exists in the form to cycle on the navigation bar using this method. So when clicking NEW, I want to avoid the record source query throwing any errors if it's looking for an ID on the record list and you're not on the record list or otherwise not supplying one.

Hi. I don't think I follow. Earlier, you used a Where clause of 1=0, and the query/form didn't choke. So, I am not sure why it would fail now. I would probably use something like: [FieldName]=Nz(TempVars!TVName,0)

 

[CJ_London]

have the form you are opening have a recordsource something like

SELECT *
FROM myTable
WHERE False

no records are returned and you won't get the #name displaying in the controls

in your list afterupdate event (or whichever event you are using to open the form) do one of the following

docmd.openform "myform"
forms!myform.recordsource =replace(forms!myform.recordsource,"False","ID=" & myID)
'form will automatically repopulate

or

dim rst as dao.recordset
set rst=currentdb.openrecordset("SELECT * FROM myTable WHERE ID=" & myID)
docmd.openform "myform"
set forms!myform.recordset=rst
'form will automatically repopulate

which method depends on what you want to do - check there is a record before opening the form? use the second method

code is a quick hash - but gives the idea

 

[MajP]

As discussed previously, which is great. Only 1 record exists in the form to cycle on the navigation bar using this method. So when clicking NEW, I want to avoid the record source query throwing any errors if it's looking for an ID on the record list and you're not on the record list or otherwise not supplying one.

Is this a new question of is this the Zoom box thing. If it is, you are over thinking the whole thing. Just use the solution I provided.

 

[The_Doc_Man]

I am trying to pull only the record(s) I need to work with.

Well... technically you can't REALLY do that because Access pulls in everything to itself. But then again you can limit the returned records if you assure a good INDEX is on the fields that you are planning to use in your filter.

In the absence of an index, Access has no choice but to do a relation scan (read the whole frickin' table) to find matching records on a WHERE clause. BUT if you have an index on the selected field, Access will read the entire index and then will only return things that the index says matched your search criteria. Be careful of using wild card searches because a LIKE "*something" will force a relation scan anyway.

 

[zeroaccess]

have the form you are opening have a recordsource something like

SELECT *
FROM myTable
WHERE False

no records are returned and you won't get the #name displaying in the controls

in your list afterupdate event (or whichever event you are using to open the form) do one of the following

docmd.openform "myform"
forms!myform.recordsource =replace(forms!myform.recordsource,"False","ID=" & myID)
'form will automatically repopulate

or

dim rst as dao.recordset
set rst=currentdb.openrecordset("SELECT * FROM myTable WHERE ID=" & myID)
docmd.openform "myform"
set forms!myform.recordset=rst
'form will automatically repopulate

which method depends on what you want to do - check there is a record before opening the form? use the second method

code is a quick hash - but gives the idea

Thanks - this is along the lines I was thinking. I was thinking the lazy way would be to have two versions of the saved query and use one as the recordsource when hitting NEW and the other with criteria when opening an existing. Then again, with your method, it might not matter how I open a new form (see OP) if there are no records anyway.

Ideally I would have just the one query and insert the ID of the selected record only when clicking in from the record list. So essentially it loads a recordsource with one record. This is an alternative to using WHERE which pulls too many records into the form and then filters. If I ever move to SQL server, I need to design with efficiency in mind.

I will have a go with your methods next time I get the chance.

 

[CJ_London]

Doc makes a good point about indexing and using an initial * with Like

 

[zeroaccess]

Doc makes a good point about indexing and using an initial * with Like

Yep. I don't have any * searches and I did go through and do some indexing.

I just realized that really late last night I posted my solution to this in the wrong thread.

Hi. Probably lots of other ways to do it, but just as a thought, try using a query with a criteria for your form. I would suggest using a TempVars for the criteria; and before you open the form, set the value of the TempVar.

Good idea. This is what I ended up doing:

https://www.access-programmers.co.u...ts-to-improve-performance.309762/post-1672326

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.