Value missing when no records exist

[mistera]

I've searched the forums and cannot find a problem similar enough to my issue to resolve my problem.

I have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:

[WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period

while the second subform has a filter of:

[WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period

and the third subform has a filter of:

[WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period

The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.

Does anybody have any ideas as to how I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?

HELP!!

 

[JHB]

How about setting the default value = 0, for those controls/fields?

 

[Mihail]

What about using the On Error statement ?
In the error handler, based on error code, set the value to 0
something like this:

On Error GoTo ErrorHandler
Dim myVar As .....
   myVar = ControlName 'This will raise an error if no records
   'Code where you use myVar

Ex:
Exit Sub/Function

ErrorHandler:
   Select Case Err.Number
     Case YourErrorNumber
       myVar=0
       Resume Next
     Case Else
       MsgBox("Error: " & Err.Number & " , " & Err.Description)
       Resume Ex
   End Select
Exit Sub

 

[CJ_London]

This is a slight aside, but you should not be building your subform filters as you are - it would be better to construct the following in your main form current event

MySubform1.form.filter=[WBS Element]="DEF" And [Period]=" & Period
MySubform1.form.filter=true
MySubform2.form.filter=...
etc

But returning to your current problem, I can suggest a possible soultion but it depends on what the subform is required to do - can you tell me the settings for dataentry, allowadditions, allow edits etc? (from what you are describing it sounds like allowadditions=false but need to confirm)

 

[mistera]

Thanks to everyone who responded to my post. I'd like to address each of the responses:

JHB: Setting the Default Value property for the control will only set that value for new records. Since these subforms are for display purposes only, that property will never be used since no new records are being created.

Mihail: I am not receiving any kind of error and so I don't think the On Error event will be triggered. Each control on the subforms is bound to a field in the form's record source. However, if the record source yields no records, there is no value for the record source field. The value isn't even null, so I can't use the Nz function.

CJ_London: Thanks for the suggestion to set the subform filters in the On Current event for the mainform. I'm guessing that this will be more efficient than setting them in each subform. Is this correct? As for your query about the use of the subforms, they are only there to display details of data linked to the data on the mainform. The subform controls on the mainform have properties set for Enabled=No and Locked=Yes. As for the subforms themselves, the properties for Data Entry, Allow Additions, Allow Deletions, and Allow Edits are all set to No. Any solution that you can offer to simply display zeroes for each subform control when no records exist in the subform's record source is much appreciated!

 

[spikepl]

Then set AllowAdditions= true, and all visible fields Locked = true for the subforms, and enabled must probably also be true for the subform controls on the main form

 

[mistera]

Woo, hoo! Thanks spikepl for the suggestion. That worked!

I changed each subform's property for Allow Additions to Yes. I also changed the properties for each control on each subform for Enabled to No and Locked to Yes. Also, the mainform Enabled and Locked properties for the subform controls were also set that way.

That did it. Thanks to all for your assistance!

 

[CJ_London]

Does this solve this part of your post?

I want these fields to display 0 instead of blank so I can use them in other calculated fields

- to display 0's set the default value for the fields to 0

 

[Mihail]

Mihail: I am not receiving any kind of error and so I don't think the On Error event will be triggered.....

..... and, of course, you don't tried.

 

[mistera]

I did not have to specify a default value on the subform control's default value property because the underlying table's design has the field to which the control is bound set with a default value of zero.

The missing piece that made the zero default work without truly allowing additional records to be added to the table was to set the Allow Additions property to Yes while disabling and locking the control and in fact, the entire subform. I originally had the Allow Additions property set to No.