Control Source gives ERROR when referring to blank subform

[bal123]

Hi,

I hope everyone is well.

I have a form with datasheet subform that lists orders. On the main form I have an unbound text box called txt_order. The purpose of this control is merely to show (order number for) which record is selected in the subform.

The control source for this control merely refers to the [Order No] control in the subform, which works fine. The only issue I have is when the subform has no records displayed I get #?Name and using a NZ formula I get #Error. The control source I have written is: =Nz([subfrm_orders].[Form]![Order No],0)

There are 3 buttons on the main form which change the record source for the sub form and requery it by different date ranges (day, Week, Month). These work fine.

When there are records displayed and I click them, the txt_order field on the main form correctly displays the order number from the selected record in the subform.

I would like it when there are no fields displayed to say "No Records" or display nothing, which is what I tried with the nz. Not sure why this is not working.

Any assistance/insight is greatly appreciated.

Many Thanks.
B

 

[RuralGuy]

Maybe this link will help: http://allenbrowne.com/RecordCountError.html

 

[bal123]

Thanks Rural Guy,

Had read that while digging around on the net. Was hoping that there may be a better/easier work around.

It does kind of make sense. Will handle the displaying of the selected record differently. Perhaps via vba on form load or something. All records show by default on form load and only show no records if a search is used that has no results. So may update the field control source via vba after searching.

Thanks again for your kind help.