Solved Change Where condition after form is already open

[Saphirah]

Hey everyone,

So first i gotta say i am using a backend on a server, so i am trying to reduce network traffic right now.
The where condition in DoCmd.OpenForm is very optimized. It will only pull the records matching the criteria. In my case i am using an ID field so it will only pull one record from the backend.

My Problem is though, i want to change the record that is displayed with a press of a button. So i need to pull another record from the server . Right now i am setting the filter I know i can set the filter condition, but to my knowledge (correct me if i am wrong) the filter condition will evaluate every record, so it needs to pull all the records from the backend.

So. Is there any way to update or change the "Where Condition" after a form is already opened?

 

[arnelgp]

you can Change RecordSource of the Form:

private sub button_click()
Me.RecordSource = "select * from dbo_table where id = 3;"
end sub

 

[namliam]

best way to do this is to create a Pass through query to base your form on... and place the sql in it like arnelgp suggests.
You can the requery the form which will fire the query on your sql database and display the record

 

[bastanu]

I usually have a main form open all the time (could be hidden) with a hidden unbound textbox holding the ID. For the form I use a saved query (not a SQL statement as the record source) that uses that textbox: "SELECT * FROM tblMyTable WHERE TableID = FORMS!frmMainForm!TableID"). To navigate to a new record I simply update the (hidden) ID on the main form then requery.

Cheers,

 

[namliam]

Problem with your solution @bastanu in this case is the OP is using a sql database. Using the forms solution forces access to evaluate the query at the client side instead of server side... generating a lot of overhead and a potential draw down on performance.

 

[bastanu]

First of all where exactly do you see the OP mention "SQL" server, it can be a LAN Windows server. Secondly I have used the method with a front-end linked to SQL Server, Oracle and MySQL back-ends and the response was instant with many thousands of table records. It looks like the myth you are alluding to is still sadly perpetuating .... The advances in the drivers ensure that the processing is still done by the server with only one record returned to Access.

Cheers,

 

[namliam]

"backend on a server" could be a MS Access backend something else... on a server -to me- says "bigger database" like SQL or Oracle... but can be a mis-interpertation.

many thousands of records, are not as many as it sounds... Slack like this can now a days be hidden by over performance. But can become appearent as recordsets grow and grow.

I will grant you, it has been a while since I did an actual client server application in access.... thus I will gladly yield to you and if it performs it performs.... However I dont see how drivers have anything to do with the fact that the value only exists in access itself.... if anything can solve this issue it is MS Access itself ... which surely can have improved over time since I did anything along these lines with it.

 

[pbaldy]

best way to do this is to create a Pass through query to base your form on... and place the sql in it like arnelgp suggests.
You can the requery the form which will fire the query on your sql database and display the record

It should be noted that a pass through query would be read only.

 

[Isaac]

@Saphirah
My personal advice/experience ... the pass through query only makes sense if the backend is another database, such as SQL server, and if you are OK not being able to edit a record as Paul pointed out.

My personal advice is to skip using Filter or OpenArgs, and simply re-set the form's recordsource.
Me.Recordsource="select * from underlyingquery where id=" & control
Me.Requery
..etc

Maybe not always the best approach, but usually easier than messing with filters and openargs.

 

[CJ_London]

so i am trying to reduce network traffic right now.
The where condition in DoCmd.OpenForm is very optimized. It will only pull the records matching the criteria.

the WHERE parameter of docmd.openform is actually a filter, it is not a criteria. So all records per the form recordsource will be brought across the network. i.e it does not reduce network traffic. Proof - on the target form, ensure the navigation bar is visible - you will see the filter button is highlighted. click it and you will see all the records. Badly named, I know, but all the WHERE parameter does is apply a filter

There is a relatively simple fix, the principle being the form recordsource returns no records (an empty recordset) e.g.

SELECT * FROM myTable WHERE False

in the openform parameters, move the string in the WHERE parameter to the openargs parameter

in the form open or load event replace 'False' with openargs in recordsource

me.recordsource=replace(me.recordsource,"False",me.openargs)

obviously, can be more complex if your recordsource has criteria which includes False for other reasons

note there is no need to requery if you change the recordsource, that will happen automatically

There are other ways to achieve this, but very much depends on the way the app works for the user

 

[pbaldy]

I don't know how to analyze the actual network traffic, but I use a wherecondition to open forms based on tables with millions of records, and they open instantly. I know what you say is true about the filter button, but perhaps the wherecondition also brings a filtered recordset over the wire to begin with?

 

[CJ_London]

key is 'to begin with'. It will prioritise the where records, but all the other records will follow.

 

[Saphirah]

the WHERE parameter of docmd.openform is actually a filter, it is not a criteria. So all records per the form recordsource will be brought across the network. i.e it does not reduce network traffic. Proof - on the target form, ensure the navigation bar is visible - you will see the filter button is highlighted. click it and you will see all the records. Badly named, I know, but all the WHERE parameter does is apply a filter

There is a relatively simple fix, the principle being the form recordsource returns no records (an empty recordset) e.g.

SELECT * FROM myTable WHERE False

in the openform parameters, move the string in the WHERE parameter to the openargs parameter

in the form open or load event replace 'False' with openargs in recordsource

me.recordsource=replace(me.recordsource,"False",me.openargs)

obviously, can be more complex if your recordsource has criteria which includes False for other reasons

note there is no need to requery if you change the recordsource, that will happen automatically

There are other ways to achieve this, but very much depends on the way the app works for the user

Hey everyone, thank you for these informations, you helped me very much!
CJ_London, this trick is awesome! Thank you! I will use this.

I will mark the issue as solved!