Curiosity question relating to Dcount in a Text Box and in VBA

[OssieMac]

This question is really one of a curiosity nature because I have a
workaround. However, I would just like to know why something does not work.

I have a continuous form where the user enters a value in the Form Header
and is presented with an on screen report.

The user then has one button to Print the report and another to Export the
data to Excel.

The Record Source for the form is a table named [Report Receipts In].

So that the user is not presented with data from a previous query, I delete
all the records from the table with the following code in the Forms On Load
event:-

CurrentDb.Execute "Delete * from [Report Receipts In]"

The table is re-created with the following code which runs a Make Table
Query after the user enters the required parameter.

Dim stDocName As String
'Remove the forms record source so the Query can use the table
Me.RecordSource = ""

stDocName = "Receipts In"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

'Reattach the forms record source
Me.RecordSource = "Report Receipts In"
Me.Requery
Me.Recalc

All the above works fine. However, the form header has a Text Box with the
following formula as the Control Source to count the number of records
returned:-
=DCount("*","[Report Receipts In]")

Now my question is if no records are returned, why don’t I get a zero in the
Text Box with the above formula and in VBA why doesn’t the following code
work:-

If Me.Number_Records = 0 Then 'Error on this line
MsgBox "No data to export."
Exit Sub
End If

Error details of the code are:-

Runtime error 2427
You entered an expression that has no value.

I have tried various combinations of the formula in the Text Box and in the
VBA code by nesting it with the Nz function to return Zero and Zero length
string and it still doesn’t work.

It all works fine if records are returned and a value greater than Zero is
returned in the field.

The following VBA code does work and hence I use it for the test in lieu of
the above. However, as the Dcount function is the same as the Text Box
Control source, it has simply heightened my curiosity.

If DCount("*", "[Report Receipts In]") = 0 Then
MsgBox "No data to export."
Exit Sub
End If

As always, all replies will be greatly appreciated.

 

[Allen Browne]

If no records are returned, and no new record can be added, the Detail
section of the form goes completely blank.

When this occurs, Access still displays the controls in the Form Header and
Form Footer, but it gets thoroughly confused about their values. Details and
example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This question is really one of a curiosity nature because I have a
workaround. However, I would just like to know why something does not
work.

I have a continuous form where the user enters a value in the Form Header
and is presented with an on screen report.

The user then has one button to Print the report and another to Export the
data to Excel.

The Record Source for the form is a table named [Report Receipts In].

So that the user is not presented with data from a previous query, I
delete
all the records from the table with the following code in the Forms On
Load
event:-

CurrentDb.Execute "Delete * from [Report Receipts In]"

The table is re-created with the following code which runs a Make Table
Query after the user enters the required parameter.

Dim stDocName As String
'Remove the forms record source so the Query can use the table
Me.RecordSource = ""

stDocName = "Receipts In"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

'Reattach the forms record source
Me.RecordSource = "Report Receipts In"
Me.Requery
Me.Recalc

All the above works fine. However, the form header has a Text Box with the
following formula as the Control Source to count the number of records
returned:-
=DCount("*","[Report Receipts In]")

Now my question is if no records are returned, why don’t I get a zero in
the
Text Box with the above formula and in VBA why doesn’t the following code
work:-

If Me.Number_Records = 0 Then 'Error on this line
MsgBox "No data to export."
Exit Sub
End If

Error details of the code are:-

Runtime error 2427
You entered an expression that has no value.

I have tried various combinations of the formula in the Text Box and in
the
VBA code by nesting it with the Nz function to return Zero and Zero length
string and it still doesn’t work.

It all works fine if records are returned and a value greater than Zero is
returned in the field.

The following VBA code does work and hence I use it for the test in lieu
of
the above. However, as the Dcount function is the same as the Text Box
Control source, it has simply heightened my curiosity.

If DCount("*", "[Report Receipts In]") = 0 Then
MsgBox "No data to export."
Exit Sub
End If

As always, all replies will be greatly appreciated.

 

[Marshall Barton]

This question is really one of a curiosity nature because I have a
workaround. However, I would just like to know why something does not work.

I have a continuous form where the user enters a value in the Form Header
and is presented with an on screen report.

The user then has one button to Print the report and another to Export the
data to Excel.

The Record Source for the form is a table named [Report Receipts In].

So that the user is not presented with data from a previous query, I delete
all the records from the table with the following code in the Forms On Load
event:-

CurrentDb.Execute "Delete * from [Report Receipts In]"

The table is re-created with the following code which runs a Make Table
Query after the user enters the required parameter.

Dim stDocName As String
'Remove the forms record source so the Query can use the table
Me.RecordSource = ""

stDocName = "Receipts In"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

'Reattach the forms record source
Me.RecordSource = "Report Receipts In"
Me.Requery
Me.Recalc

All the above works fine. However, the form header has a Text Box with the
following formula as the Control Source to count the number of records
returned:-
=DCount("*","[Report Receipts In]")

Now my question is if no records are returned, why don’t I get a zero in the
Text Box with the above formula and in VBA why doesn’t the following code
work:-

If Me.Number_Records = 0 Then 'Error on this line
MsgBox "No data to export."
Exit Sub
End If

Error details of the code are:-

Runtime error 2427
You entered an expression that has no value.

I have tried various combinations of the formula in the Text Box and in the
VBA code by nesting it with the Nz function to return Zero and Zero length
string and it still doesn’t work.

It all works fine if records are returned and a value greater than Zero is
returned in the field.

The following VBA code does work and hence I use it for the test in lieu of
the above. However, as the Dcount function is the same as the Text Box
Control source, it has simply heightened my curiosity.

If DCount("*", "[Report Receipts In]") = 0 Then
MsgBox "No data to export."
Exit Sub
End If

Regardless of any bugs you are running into, you would still
need to do the DCount in the VBA procedure. The reason you
can not use a text box to do the calculation and check the
text box's value in code is that control expression
calculations are performed asynchronously in a low priority
task while your VBA code is performed in the high priority
foreground task.

Very confusing that, but it means your
If Me.Number_Records = 0 Then
statement has a good chance of executing before the value of
the Number_Records text box is calculated.

I recommend that you remove the expression from the text box
and set its value in your code:

Me.Number_Records = DCount("*", "[Report Receipts In]")
If Me.Number_Records = 0 Then 'Error on this line
MsgBox "No data to export."
Exit Sub
End If

I don't see a good reason for using a temp table in you
explanation above. Why can't you just use a query for the
form record source instead?

 

[OssieMac]

Thankyou Allen and Marshall.

Allen's web site explained the situation perfectly and now my curiosity has
been put to rest.

Marshall, your idea to populate the text box with the number of records from
code works well. Should have thought of doing that myself but I think that my
curiosity as to why I was having a problem was the overpowering thought.

Anyway, if I remember correctly, the reason that I used the Make Table query
and then the resulting table as the Record Source for the form was that I was
being asked for a parameter for the query each time the form was opened but
perhaps I was not doing something correctly. I wanted the user to be able to
select the parameter from a multi column combo box. I am trying to ensure
that the correct receipt number is entered by displaying both the receipt
number and the name with the hopes that the user will select correctly.

I am still quite new to Access and learning something new nearly every day
and your help are much appreciated.

--
Regards,

OssieMac

This question is really one of a curiosity nature because I have a
workaround. However, I would just like to know why something does not work.

I have a continuous form where the user enters a value in the Form Header
and is presented with an on screen report.

The user then has one button to Print the report and another to Export the
data to Excel.

The Record Source for the form is a table named [Report Receipts In].

So that the user is not presented with data from a previous query, I delete
all the records from the table with the following code in the Forms On Load
event:-

CurrentDb.Execute "Delete * from [Report Receipts In]"

The table is re-created with the following code which runs a Make Table
Query after the user enters the required parameter.

Dim stDocName As String
'Remove the forms record source so the Query can use the table
Me.RecordSource = ""

stDocName = "Receipts In"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

'Reattach the forms record source
Me.RecordSource = "Report Receipts In"
Me.Requery
Me.Recalc

All the above works fine. However, the form header has a Text Box with the
following formula as the Control Source to count the number of records
returned:-
=DCount("*","[Report Receipts In]")

Now my question is if no records are returned, why don’t I get a zero in the
Text Box with the above formula and in VBA why doesn’t the following code
work:-

If Me.Number_Records = 0 Then 'Error on this line
MsgBox "No data to export."
Exit Sub
End If

Error details of the code are:-

Runtime error 2427
You entered an expression that has no value.

I have tried various combinations of the formula in the Text Box and in the
VBA code by nesting it with the Nz function to return Zero and Zero length
string and it still doesn’t work.

It all works fine if records are returned and a value greater than Zero is
returned in the field.

The following VBA code does work and hence I use it for the test in lieu of
the above. However, as the Dcount function is the same as the Text Box
Control source, it has simply heightened my curiosity.

If DCount("*", "[Report Receipts In]") = 0 Then
MsgBox "No data to export."
Exit Sub
End If

Regardless of any bugs you are running into, you would still
need to do the DCount in the VBA procedure. The reason you
can not use a text box to do the calculation and check the
text box's value in code is that control expression
calculations are performed asynchronously in a low priority
task while your VBA code is performed in the high priority
foreground task.

Very confusing that, but it means your
If Me.Number_Records = 0 Then
statement has a good chance of executing before the value of
the Number_Records text box is calculated.

I recommend that you remove the expression from the text box
and set its value in your code:

Me.Number_Records = DCount("*", "[Report Receipts In]")
If Me.Number_Records = 0 Then 'Error on this line
MsgBox "No data to export."
Exit Sub
End If

I don't see a good reason for using a temp table in you
explanation above. Why can't you just use a query for the
form record source instead?

 

[Marshall Barton]

Thankyou Allen and Marshall.

Allen's web site explained the situation perfectly and now my curiosity has
been put to rest.

Marshall, your idea to populate the text box with the number of records from
code works well. Should have thought of doing that myself but I think that my
curiosity as to why I was having a problem was the overpowering thought.

Anyway, if I remember correctly, the reason that I used the Make Table query
and then the resulting table as the Record Source for the form was that I was
being asked for a parameter for the query each time the form was opened but
perhaps I was not doing something correctly. I wanted the user to be able to
select the parameter from a multi column combo box. I am trying to ensure
that the correct receipt number is entered by displaying both the receipt
number and the name with the hopes that the user will select correctly.

The standard way to supply a parameter to a query is to
provide an unbound text box in the form's header section for
users to enter the parameter value. Then the query can
retrieve it without any prompting by making the parameter
look like:
Forms!theform.thetextbox

There is rarely a real need for a temp table and they cause
the database to bloat. They should be avoided if at all
possible.

 

[OssieMac]

Thanks once again for all your help. I will try your suggestions.