UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access - Forms On Apply Filter Does Not Fire When Linking To SQL Via Odbc, Access 2016    
 
   
Petran
post Apr 4 2020, 03:54 AM
Post#1



Posts: 7
Joined: 3-April 20



I have a Form, linked to a Table on a SQL Database via ODBC.

My challenge is that I need to get the Record Count in the underlying table or Query to display on the form. This is done automatically when the form's 'Record Selectors' is set to "Yes" when in Form Design mode.
The display is then at the bottom left hand side of the Form. See Figure Attachment "RecordSelectors" (I do not know what this part of the From is called?)

When the form is Opened, the Record Count is displayed. When I do my first filter, the Record Count is displayed but any further Filtering after that returns no Record Count.

The only code on the form is during the 'Open' event and on 'ApplyFilter' event. The code is displayed in "FilterCode" attachment.

I must mention that if my Front-End Database is linked to MS Access Back-End (.MDB) file, this problem does not occur

Any assistance is appreciated. . . .
Attached File(s)
Attached File  RecordSelectors.jpg ( 7.98K )Number of downloads: 0
Attached File  FilterCode.png ( 28.46K )Number of downloads: 2
 
Go to the top of the page
 
arnelgp
post Apr 4 2020, 04:59 AM
Post#2



Posts: 1,528
Joined: 2-April 09
From: somewhere out there...


I don't think there is a need for a code
in ApplyFilter event that will move the record
pointer.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2020, 08:56 AM
Post#3


UA Admin
Posts: 37,285
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

I think you may be seeing an artifact of the difference in latency loading a larger recordset from a remote server as apposed to a local accdb.

I suspect that if you force the form to move focus to the last record, the recordcount would update. It may be that your code is not actually causing that to happen. Try it manually.

But that is also a sign you need to rethink the design of the form entirely.

Here's why. Access is pretty good at the "Load 'em all up at once and Filter" approach. It's sort of a default for many, many Access relational database applications.

SQL Server is NOT so good at that approach. And that means we have to rethink our form designs. Instead of loading an entire table as a recordsource, we want to use a parameterized query that loads as few records as possible, one would be ideal in most cases.

That's because pulling records from a server-based database tends to be so much slower. You're seeing only one of the problems that causes.

Ideally, you'll have a way to "pilot" a sample of records for users to choose from and only then requery the form to load just the record -- or small batch of records -- the user needs at that moment.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Petran
post Apr 4 2020, 09:20 PM
Post#4



Posts: 7
Joined: 3-April 20



Thanks for your response ArnelGP.
Please elaborate on your statement that there is no need for coding in On ApplyFilter Event, maybe I am missing something. . . .
Remember that I am using SQL as a Backend Database, and if I leave the 'Goto Last, Goto First" code out, no count record information is provided.
It is true that if an .MDB file is used as the Backend, no code is required, but not so with an SQL backend.
Go to the top of the page
 
Petran
post Apr 4 2020, 10:46 PM
Post#5



Posts: 7
Joined: 3-April 20



Thanks for the Welcome note GroverParkGeorge

Allow me to respond to some of your interesting comments:

1) I think you may be seeing an artifact of the difference in latency loading a larger recordset from a remote server as opposed to a local accdb
Petran: I am aware of the latency. It was my first challenge when i converted my Client's Back End Database to SQL, however, the database has no more than 30,000 records in the Prime tables, so the latency affect is tolerable wink.gif

2) I suspect that if you force the form to move focus to the last record, the recordcount would update.....
Petran: The recordcount updates the first time (when the goto first, goto last when the On Open event is fired) and then again when the On ApplpyFilter event is fired the first time, thereafter never again.
......It may be that your code is not actually causing that to happen. Try it manually
This is where the problem lies If I move to the Last Record manually and then again (Manually) to the first record, recordcount is displayed correct.

I think that this is where the unexplainable happens. my question is: Why does the code gets executed the first time around and never again thereafter. I have traced the code by single stepping through each line, it seems that the "goto first, goto last" is just ignored. iconfused.gif

Added to this, I have existing Forms that is also linked to the same underlying SQL Database, where the same code is in the "On Open" and "On Apply Filter" events and it works fine.
If I use the Form that works as a template, as soon as I change the RecordSource on the Template to the new query that is based on the new table, the problem occurs. The table that is used in the (problematic) form only has 3000 records in it. Even if I only put 10 records in the underlying SQL table, the same thing happens.

shrug.gif



Go to the top of the page
 
GroverParkGeorge
post Apr 5 2020, 08:20 AM
Post#6


UA Admin
Posts: 37,285
Joined: 20-June 02
From: Newcastle, WA


Okay, so the recordcount is updated properly when you force the recordset move manually. That rules out the problem being with the table itself. I also assume it has a Primary Key and appropriate indexes? The issue is that the display of the recordcount is not updated when the action is carried out in code, and only in this one form.

Even with 3,000 records, I would still avoid this approach, "load 'em all and filter". It's less efficient and I've never seen it work well with remotely hosted SQL Server tables. Even on a small peer-to-peer network within an Office. As I was trying to think of another possible reason for this, it occurred to me that, in over 10 years now, I have designed or worked on only one Access relational database where I did not immediately convert the "load 'em all and filter" strategy to "load the one you need" strategy. And that was at the insistence of the client. I showed them the improved performance, but they wanted to "keep it the way it is" anyway. So be it, familiarity over performance.

That said, given the fact that I have little or no recent, relevant experience, I'm hard pressed to think of another possible strategy here.

The only other thing that I could think of might be other code, perhaps in the form's Current event, that is interfering.




--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AlbertKallal
post Apr 5 2020, 04:58 PM
Post#7


UtterAccess VIP
Posts: 3,071
Joined: 12-April 07
From: Edmonton, Alberta Canada


As others noted - really a design change here is warranted.

Pulling all that large amount of data into a form really don't make a lot of sense.

However, the on-filter event DOES fire. (put a debug.print "on filter event" - note how it displays in the debug window)

It turns out due to UI quirk, that doing this in on-load (for linked SQL server tables) in access works fine:

CODE
   Me.Recordset.MoveLast
   Me.Recordset.MoveFirst


And I suggest dumping the docmds - they are sensitive to focus. (if you have other forms, especially if running a timer - then focus can change, and the OTHER form will eat your docmds. - not the current form).

However, that UI quirk I was talking about?

If you place a move last in the ApplyFilter event?
The built in form UI event que does a move first - this is "close" to async occuring.

So, in the ApplyFilter - try this:

CODE
  Me.Recordset.MoveLast


What occurs is the above fires - then access will fire off a move first for you when the form updates!

I find the above works on linked tables to SQL server.

We will try and keep this information from the bandwidth gobble police - since you pulling large numbers of records for little good reason.

But, give the above trick a try - it should work for you.

So, - on-load (movelast + move first)

For on-apply filter - only put in a move last - access will do the move first for you. Your move-first I believe is ignored, or the event que is "sorted" by Access. I'll let one of the gurus here further explain this issue since it beyond my knowledge.

Also, be VERY careful if you have some on-current code. I have a love + hate relationship with on-current, and I tend to avoid it when possible now days.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
Petran
post Apr 6 2020, 12:42 AM
Post#8



Posts: 7
Joined: 3-April 20



Thanks for your interest and support on this one.

I think I might have solved the issue. I am still testing to confirm that I am correct but the preliminary test indicate a positive.

The crux of the matter is that the Form does not allow adding of new records. This is in the Record Selectors at the bottom of the screen. This in itself did not mean much.
I also discovered the the underlying query does not allow addition of a new record.

I then checked the Form's Data Tab (in design view) and ensured that the 'Allow Addition' is set to "Yes" and that the Recordset Type is "Dynanset".
I also made sure in the SQL Table definition that there are no unwanted indexes and that the Primary Keys are setup correctly.

Eventually I returned to my Form and the Record Selectors 'Add New Record' is not Greyed out.

This seemed to have resolved the issue
Go to the top of the page
 
Petran
post Apr 6 2020, 07:26 AM
Post#9



Posts: 7
Joined: 3-April 20



Thanks for the valuable information, I will certainly follow the suggestion.
Go to the top of the page
 
AlbertKallal
post Apr 6 2020, 02:12 PM
Post#10


UtterAccess VIP
Posts: 3,071
Joined: 12-April 07
From: Edmonton, Alberta Canada


Good stuff!

Just remember that tip about "only" having a move-last in the on-apply filter event. It does work for me, but it might be somewhat flakey. Give it a try.
(I don't have a lot of details as to why/when Access does the move-first for you.

So, - on-load (move last, move first = ok)

And for apply filter event - only do a move-last
I have only tested this using me.RecordSet.MoveLast, and not with docmd's
R
Albert
Go to the top of the page
 
Petran
post Apr 11 2020, 12:44 PM
Post#11



Posts: 7
Joined: 3-April 20



Albert

I followed your instructions and added the Goto statements in the Onn Load and On ApplyFilter Events.
It worked well up to a point. . .

After adding 53 records to the table in SQL, the On ApplyFilter events falls back the the old problem, the response times to get the count in the Record Selector field (at the bottom of the screen) takes up to 20 seconds.
I verified each field in the new records, nothing wrong.
Each record added (via an Input Form) correctly, yet up to record number 53 everything was fine.

I am of the opinion that there must be a bug in MS Access, but i do not know whr3e to go with it. Can you assist?
Go to the top of the page
 
AlbertKallal
post Apr 11 2020, 09:08 PM
Post#12


UtterAccess VIP
Posts: 3,071
Joined: 12-April 07
From: Edmonton, Alberta Canada


Hum,

QUOTE
Goto statements


Might be use of terminology we are getting mixed up with here.

Are you using docmds, or using as I suggested this:

Me.RecordSet.MoveLast

So, make sure you are using me.RecordSet.MoveLast/MoveFirst – that will and should eliminate some issues.
(avoid the docmd's when possbile)


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
Petran
post Apr 12 2020, 06:28 AM
Post#13



Posts: 7
Joined: 3-April 20



Albert

Apologies, aware that terminology is important to prevent confusion.

I used your code 'Me.Recordset.Movelast' up to a point that I have added about 150 records to the underlying SQL table. I did this programmatically. (i.e via an input form)

I did some further digging and this is what happend

having less that 150 records, it worked fine. (Got passed the issue at 53 records) What happened then was that I get a Run-time error 91: Object variable or With block variable not set. There are no Recordsets involved at this point so I do not know why the error.
What I did found, when i debugged the code in the 'On ApplyFilter' I see that there is a parameter (ApplyType) that is passed when the event is triggered.

This value changes from 1 to 0 depending on something I cannot see. What it tells me is that whatever is executed to create the parameter is the culprit

I do not have any code in the 'On Filter' event.

I will persevere with the issue, there must be an explanation and a solution.

Thanks for your input. . .

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    2nd June 2020 - 08:23 PM