Solved Need to check if there is data before requery form? (1 Viewer)

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
Hello everyone,

I have the main and subform that both use same table TBLPRODUCT.
The subform is a continous form so that list out all products name while mainform show the product detail which is currently selected on subform.

If the table TBLPRODUCT is empty, somehow the subform (and other bounded controls) on mainform will not display.
In that case, if I try to requery subform then it introduces an run-time error likely the object is not existing.... So I normally have to check

Code:
        If DCount("ProductID", "TBLPRODUCT") > 0 Then         
                Forms!MainFrm.Form.Requery
                Forms!MainFrm.Form!SubFrmTable.Form.Requery
        End If

But I dont feel it efficent, because most of time the table product is not empty. Moreover, if it contains 1,000,000 rows, Dcount might require a amount of time, Am I right? I also know I can simply Resume Next if error occured but I am kind of hating error :).
So my questions are:
1. Should I do the check as above code before requery?
2. Or I should use Resume Next?
3. The statement If DCount("ProductID", "TBLPRODUCT") > 0, does Dcount smart enough to stop as soon as it reached 1 or is still have to count until 1,000,000?

Sorry for weird questions.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:17
Joined
Sep 21, 2011
Messages
14,047
Perhaps look at the form Recordset.RecordCount instead?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
it will take time because you are using a "field" (ProdiuctID) to count in your DCount().
this will check if ProductID is Null or not first before counting.

you use a "constant" on your DCount() so the engine will not check whether it is null or not.

If DCount("1", "TBLPRODUCT") > 0 Then
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
you also need to verify my so called "claim" about Constants in DCount or even in SQL Count():
there are 1 million record in the table of this sample
check which one is faster.

actually I've read it in one of my many books.
it's 1 million record, so the file is big to upload.
https://www.dropbox.com/s/vp44o3oqdflvzfm/sales.zip?dl=0
 

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
it will take time because you are using a "field" (ProdiuctID) to count in your DCount().
this will check if ProductID is Null or not first before counting.

you use a "constant" on your DCount() so the engine will not check whether it is null or not.

If DCount("1", "TBLPRODUCT") > 0 Then
I did not know that, so far. So it sweeps through all row and add 1 to the result?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:17
Joined
Feb 19, 2002
Messages
42,973
I would use three forms rather than two. An unbound main form. A bound list form (not updateable), and a second subform that shows the detail for the record selected in the list.

Or, use a split form and let Access handle things for you
 
Last edited:

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
I would use three forms rather than two. An unbound main form. A bound list form (not updateable), and a second subform that shows the detail for the record selected in the list.

Or, use a split form and let Access handle things for you
Split form of Access is not flexible enough.
Yes, my subform is not updateable, it is to only display the product list. Why dont you ultilize the mainform to shows the detail? What is benefit of using second subform?
btw: When design the forms in this way, both forms are using same data from TBLPRODUCT, if the subfrom's recordsource set as
Code:
subform.form.recordsoure = "SELECT ProductID, ProductName from TBLPRODUCT"
it will introduce an error likely: "The table TBLPRODUCT is exclusive opening...You can't manuplite..." or something similiar. To fix it, I then have to change to
Code:
subform.form.recordsoure = "SELECT * from TBLPRODUCT"
In fact, I only need 2 fields from table, not everything... Have you ever faced this issue and know how to deal with it?
 

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
I
you also need to verify my so called "claim" about Constants in DCount or even in SQL Count():
there are 1 million record in the table of this sample
check which one is faster.

actually I've read it in one of my many books.
it's 1 million record, so the file is big to upload.
https://www.dropbox.com/s/vp44o3oqdflvzfm/sales.zip?dl=0
just downloaded. It is great demonstration. I will "Ctrl +H" my VBA code now :))
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 20:17
Joined
Sep 21, 2011
Messages
14,047
Split form of Access is not flexible enough.
Yes, my subform is not updateable, it is to only display the product list. Why dont you ultilize the mainform to shows the detail? What is benefit of using second subform?
btw: When design the forms in this way, both forms are using same data from TBLPRODUCT, if the subfrom's recordsource set as
Code:
subform.form.recordsoure = "SELECT ProductID, ProductName from TBLPRODUCT"
it will introduce an error likely: "The table TBLPRODUCT is exclusive opening...You can't manuplite..." or something similiar. To fix it, I then have to change to
Code:
subform.form.recordsoure = "SELECT * from TBLPRODUCT"
In fact, I only need 2 fields from table, not everything... Have you ever faced this issue and know how to deal with it?
We have something on this site called an Emulated Split Form.
Perhaps search for that?
 

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
Yeah, I have studied the design,
One continuous form and most of controls are placed in its header section. Therefore, it looks like split-form.
My current project is almost same concept but the continuous form is in a subform, so that I can save its left or right side area for other things.
btw: Same to mine, the controls on your form are also flickering when filter applied or form's recordsource is set. That is kind of annoying, it is even more flickering if form contains button with preset format or form with high-res picture... Dont know how to fix it. I'm using MS office 365
Anyway, the original problem of this thread has been solved.

Thank you all
 

isladogs

MVP / VIP
Local time
Today, 20:17
Joined
Jan 14, 2017
Messages
18,186
you also need to verify my so called "claim" about Constants in DCount or even in SQL Count():
there are 1 million record in the table of this sample
check which one is faster.

actually I've read it in one of my many books.
it's 1 million record, so the file is big to upload.
https://www.dropbox.com/s/vp44o3oqdflvzfm/sales.zip?dl=0
Not sure if this was directed at me...
Anyway I've downloaded and tested it.
Your first test is bound to be slow as its using a non-indexed field - doing that makes no sense
Your 'constant' test is of course much faster BUT not the fastest method possible.

However I've also added 2 more tests
a) DCount on Indexed field (ID)
b) DCount using *

These are all 4 results:
Test 1: Non Indexed Field Count completed. Total record count = 1000000: Time taken = 0.34375 seconds
Test 2: Constant Count completed. Total record count = 1000000: Time taken = 0.015625 seconds
Test 3: Indexed Field Count completed. Total record count = 1000000: Time taken = 0.0234375 seconds
Test 4: DCount* completed. Total record count = 1000000: Time taken = 0.0078125 seconds

As you can see DCount("*" ) is by far the fastest on this set of tests though all except test 1 are fast enough for it to make little difference

Here is the code I used:

Code:
Option Compare Database
Option Explicit

Dim ST As Double, ET As Double, RC As Long

Private Sub Command0_Click()
    ST = Timer
    RC = DCount("region", "[1000000 Sales Records]")
    ET = Timer
    Debug.Print "Test 1: Non Indexed Field Count completed. Total record count = " & RC & ": Time taken =  " & ET - ST & " seconds"
End Sub

Private Sub Command1_Click()
    ST = Timer
    RC = DCount("1", "[1000000 Sales Records]")
    ET = Timer
    Debug.Print "Test 2: Constant Count completed. Total record count = " & RC & ": Time taken =  " & ET - ST & " seconds"

End Sub

Private Sub Command2_Click()
    ST = Timer
    RC = DCount("ID", "[1000000 Sales Records]")
    ET = Timer
    Debug.Print "Test 3: Indexed Field Count completed. Total record count = " & RC & ": Time taken =  " & ET - ST & " seconds"

End Sub

Private Sub Command3_Click()
    ST = Timer
    RC = DCount("*", "[1000000 Sales Records]")
    ET = Timer
    Debug.Print "Test 4: DCount* completed. Total record count = " & RC & ": Time taken =  " & ET - ST & " seconds"

End Sub

EDIT:
I normally repeat these tests on a loop to reduce possible variations due to CPU load differences etc.
These are the results of running each test 100 times on a loop:
Test 1: Non Indexed Field Count completed. Total record count = 1000000: Time taken = 38.3203125 seconds
Test 2: Constant Count completed. Total record count = 1000000: Time taken = 1.65625 seconds
Test 3: Indexed Field Count completed. Total record count = 1000000: Time taken = 1.6484375 seconds
Test 4: DCount* completed. Total record count = 1000000: Time taken = 1.6484375 seconds

As you can see, tests 2-4 give very similar results. I normally use DCount ("*")
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 20:17
Joined
Jan 14, 2017
Messages
18,186
Yeah, I have studied the design,
One continuous form and most of controls are placed in its header section. Therefore, it looks like split-form.
My current project is almost same concept but the continuous form is in a subform, so that I can save its left or right side area for other things.
btw: Same to mine, the controls on your form are also flickering when filter applied or form's recordsource is set. That is kind of annoying, it is even more flickering if form contains button with preset format or form with high-res picture... Dont know how to fix it. I'm using MS office 365
Anyway, the original problem of this thread has been solved.

Thank you all
I don't see any flickering!
However you could try disabling screen updating temporarily (Application.Echo False) when applying a filter or changing the recordsource and then setting Application.Echo True afterwards. That should at the very least reduce flicker.
Or if you have a timer event, try disabling it
 

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
Impressive test! What makes difference btw constant "1" and "*" ?
However, my feeling is DCount* meaningful to recordcount.... it just counts whatever :))
 
Last edited:

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
I don't see any flickering!
However you could try disabling screen updating temporarily (Application.Echo False) when applying a filter or changing the recordsource and then setting Application.Echo True afterwards. That should at the very least reduce flicker.
Or if you have a timer event, try disabling it
Oh, yes, my form having timer to display current date time on top of header section...But your form doesn't.
Flickering happens on both my form and yours.
I have tried to use Application.Echo but no helps. I remember I have asked someone in our forum a year ago, he tested my form and no flickering but he uses Access 2007 or ealier :)). I am using 365
 

isladogs

MVP / VIP
Local time
Today, 20:17
Joined
Jan 14, 2017
Messages
18,186
@Babycat
For info, I've just added some additional timing results to post #15.
Frankly it makes little difference which method is used as long as you DON'T use a non-indexed field

If you use a timer event, you WILL get flickering. If you are updating the time each second, it can be very distracting.
 

Babycat

Member
Local time
Tomorrow, 03:17
Joined
Mar 31, 2020
Messages
275
@Babycat
For info, I've just added some additional timing results to post #15.
Frankly it makes little difference which method is used as long as you DON'T use a non-indexed field
Yeah, I saw your update, as statistical theory: The larger sample size is always give the better result. Thank very much for your efforts.

If you use a timer event, you WILL get flickering. If you are updating the time each second, it can be very distracting.
Unfortunately, yes, update every second. Let me remove the "second"
 

Users who are viewing this thread

Top Bottom