Solved Need to check if there is data before requery form?

[Babycat]

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

        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]

Perhaps look at the form Recordset.RecordCount instead?

 

[isladogs]

If the search field is indexed, this should be very fast even with 1 million records.
However, in answer to your question, please read my article Speed Comparison Tests 7 - Mendip Data Systems

 

[arnelgp]

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

 

[Babycat]

Perhaps look at the form Recordset.RecordCount instead?

Oh, yes, so simple as it is. Thank you

 

[Babycat]

If the search field is indexed, this should be very fast even with 1 million records.
However, in answer to your question, please read my article Speed Comparison Tests 7 - Mendip Data Systems

Thank you very much. I will read them, I know your articles are always informative

 

[arnelgp]

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]

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?

 

[Babycat]

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

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

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]

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 )

 

[Gasman]

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

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

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?

 

[isladogs]

We have something on this site called an Emulated Split Form.
Perhaps search for that?

Its available from Emulated Split Form - Mendip Data Systems or Emulating the split form | Access World Forums (access-programmers.co.uk)
NOTE: The latest released version is 11B

 

[Babycat]

Its available from Emulated Split Form - Mendip Data Systems or Emulating the split form | Access World Forums (access-programmers.co.uk)
NOTE: The latest released version is 11B

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]

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:

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 ("*")

 

[isladogs]

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]

Impressive test! What makes difference btw constant "1" and "*" ?
However, my feeling is DCount* meaningful to recordcount.... it just counts whatever )

 

[Babycat]

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]

@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]

@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"

 

[arnelgp]

I normally use DCount ("*")

which is Also a Constant and not a Fieldname, don't you get it?
using a Field, Access will Evaluate it if it is Null Or Not first, that is why the delay.
while using a Constant, access will evaluate Once.