Solved Problem Moving to Specific Form Record (1 Viewer)

[PleasantB]

On my form, I have a combo box that lists all record identifiers, for easy navigation.
In the combo box AfterUpdate handler, I execute the following code to navigate to the chosen record.

Me.RecordsetClone.FindFirst "[id] = " & Me![cmbId]
Me.Bookmark = Me.RecordsetClone.Bookmark

This works well most of the time but sometimes the form ends up on the wrong record.
There does not seem to be any connection between the wrong record and intended record.
It is not a specific identifier that causes this issue either, sometimes an identifier will work and other times it won't.
This is much easier to reproduce on my client's machine than on my own, for some reason (of course!).

I tried simplifying the code to the following but the problem remains:

Me.Recordset.FindFirst "[id] = " & Me![cmbId]

Does anyone have any idea as to why this might be happening?

 

[Isaac]

Is there any possibility that NoMatch is true in these cases?

 

[PleasantB]

Since the ids that have caused this problem work sometimes but not others, it seems strange that NoMatch would sometimes be true.
However, this is a strange problem so I'm not ruling anything out. I'll add code to show a dialog if NoMatch is true.

 

[Isaac]

Well, agreed. Do you have anything else going within the form's functionality that might have actually re-queried/defined the form's Recordsource prior to the client's problem happening?

 

[PleasantB]

I was just able to confirm that NoMatch is false when this occurs.
Me.Recordset!Id is equal to the chosen id but Me!Id (text box with ControlSource set to Id) has the wrong record's id.

There's isn't anything that requeries or changes the form's RecordSource.

I ran trace on SQL Server (that is my back-end) and saw these back-to-back:

exec sp_execute 5,32217
exec sp_execute 5,6645

32217 is the id entered and 6645 is the id of the record that the form ends up at.
This indicates that something is causing Access to immediately change the record (no surprise, based on what I'm experiencing).
Unfortunately, it doesn't really give any clue as to why that is happening, just confirmation.

 

[Pat Hartman]

Since your BE is SQL Server, the best solution is to use a query with selection criteria as the RecordSource for the form.

Select ...
From ..
Where SomeField = Forms!yourformname!SomeField

SomeField is an unbound control. It can be a listbox, combo, or textbox depending on what makes sense. In the AfterUpdate event of this unbound control, you need one line of code:

Me.Requery

A major point of using SQL Server as the BE is to have the server do the heavy lifting. Using a form bound to a table or a query with no criteria is how old style Access apps with Jet/ACE BE's were coded. They do not perform well with SQL Server BE's if the BE's have any size to them at all. The point of the suggested method is that you retrieve only the record you actually need from the server.

 

[Isaac]

@PleasantB In this case ... I actually agree with Pat. My preferred method of doing any kind of "filtering, selecting, going-to" on a form is simply to re-set the form's Recordsource. You cannot get more unambiguous than

Me.Recordsource="select ... from .... where ..... = " & Me.Combobox.Value
Me.Requery

I see people constantly wrestling & struggling with the nuances & gotcha's of: form Filters, OpenArgs, FindFirst, Seek, whatever.
Personally I have avoided most of that by simply setting the form's recordsource.

This concept could be criticized as "less efficient" if someone said: Well, my form already HAS its recordsource's recordset fully accessed and paged--so FindFirst is more convenient to me than re-setting the recordsource. But the answer to that is to have the "from" portion selecting from a query that is already somewhat limiting the overall population. Plus, if the proper indexes are set up, and there is no ill advised functions being ran in the where clause that kill performance, then selecting [even *] from that object, with a Where clause that limits the results to one or a couple, is going to be decently performant.

Several things I just don't do because they are unnecessarily complicated and accompanied by too many gotcha's:

• Set a recordsource in Design view, and then try to use vba code to dynamically "add" and "subtract" from the literal string of that recordsource. i.e. Me.Recordsource = Me.Recordsource & " where...", etc. etc.
• Use OpenArgs, in cases where there is no reason I can't just set the Recordsource - and especially when the Where clause contains more than one filter
• Mess with form Filter, at least in cases where there is no reason I can't just clearly set the overall Recordsource. Why? Because a form with an underlying recordset, + Filters on it, is just not as "clear" to troubleshoot and explain compared to a form that has its total Recordsource re-set on demand. One is easier to troubleshoot and explain than the other. And easier to quickly visualize in reading and conceptualizing code. And I find that testing, troubleshooting, describing "at this moment, this is the population you are seeing & this is why", and explaining those/results to the Business, are common developer activities.......And the simpler it is to Step Through, the better off you are.

The method I am recommending has two overall components:

1. Make sure the overall, underlying, Recordsource--which you will set fixed in Design view, which will occur when the form first opens--is something you can live with as an "initial view", + Selecting/Where from, later on, in code. Ideally this isn't the "whole table" of course, but some more discreet dataset. Of course you can even manipulate the Initial View by further limiting Me.Recordsource on form load.
2. Your filter code (in the afterupdate event of comboboxes, listboxes, or textboxes for searching), simply selects * from that object in #1

 

[Pat Hartman]

I don't know whether there is any detrimental effect to changing the RecordSource on the fly. I don't do it because it changes the design properties of the form and that can cause other problems. I would only change the recordSource of the form if I were actually building the SQL in code which I would only do if the search criteria were so complex that it was difficult to make a querydef with arguments.

Using the method I suggested, the form opens "empty" since the form field referenced in the WHERE argument is null.

 

[Isaac]

I don't know whether there is any detrimental effect to changing the RecordSource. I don't do it because it changes the design properties of the form and that can cause other problems

No.

If you design a form using Table1 as the Recordsource, save and close it. Then create a button on the form that executes Me.RecordSource="select col1 from table1", then close the form, then reopen it, the form's Recordsource is still just "Table1".

I tend to avoid using literal Form references within query criteria. Makes things much harder to troubleshoot, get syntax/datatypes right, catch type mismatches between form & query, etc. See it on AWF all the time. Much easier to set an overall query in Design View, then filter by select * from ThatQuery where Something=" & Me.ControlName.

 

[Pat Hartman]

It is good to know that changing the RecordSource doesn't actually change the form although I am surprised.

Using querydefs is more efficient than using embedded SQL, even when the query is being sent to the server for processing since it eliminates some of the up front work that Access needs to do to prepare the query. Newer versions of Access do make temporary querydefs for SQL Record and RowSources in order to make forms more efficient but constantly changing the RecordSource causes Access to do work it shouldn't have to do. In my formative years, I was developing transactions that required sub-second response when being executed thousands of times per minute all across the country so I'm pretty conscious of code that adds processing time unnecessarily. Given that Access forms work at human speed, this is not really an issue because people are very slow compared to computers So, as long as you are running the Access app on your own computer, you have all the bandwidth you need. If you're using Citrix or RDP, it still isn't going to kill you but every unnecessary nano-cycle eventually adds up and an inefficient Access app can certainly add overhead if it is being used in a large enough scale.

 

[PleasantB]

I went with Isaac's suggestion of creating a query to reference from my form's RecordSource.
In the combo box AfterUpdatehandler, I now execute the following code to navigate to the chosen record:

Me.RecordSource = "SELECT * FROM MyQuery WHERE id = " & Me![cmbId]
Me.Requery

I chose this approach over Pat's suggestion of referencing the combo box directly in the form's RecordSource as I wanted to default to the first record on form open (before a specific record has been chosen from the combo box).

This has solved my elusive problem and I finally have a happy client again.
Thank you both so much for your feedback!

 

[Isaac]

I went with Isaac's suggestion of creating a query to reference from my form's RecordSource.
In the combo box AfterUpdatehandler, I now execute the following code to navigate to the chosen record:

Me.RecordSource = "SELECT * FROM MyQuery WHERE id = " & Me![cmbId]
Me.Requery

I chose this approach over Pat's suggestion of referencing the combo box directly in the form's RecordSource as I wanted to default to the first record on form open (before a specific record has been chosen from the combo box).

This has solved my elusive problem and I finally have a happy client again.
Thank you both so much for your feedback!

Glad you got things sorted out!