Solved Equation: If it isn't an error and it isn't Null yet it has no value, what is it?

[brucemc777]

I have the following assignment in VBA though the Assignment itself probably doesn't matter (GECo is a declared variable. Trying to assign it the value in the textbox GrossEquipmentCost when that value exists) :

GECo = Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost

The issue is that when the textbox for GrossEquipmentCost is totally empty i get the following error:

And so in wishing to avoid this error i try to test for it. If i try

IsError(GECo = Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost)

i get False.

If i try

IsNull(GECo = Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost)

i get False.

If i try nz(GECo = Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost)
I get

If (in the Immediate window) i try

? Forms!frmOrderCreate!frmOrderAddEquip.GrossEquipmentCost = ""

I get

Same as above for "= vbNullString".

So if it isn't an error but it is Errors 2427, -2147352567 (80020009) and 438, what is it and how do i test for it in order to avoid it?

I think i'm confused.

Much appreciate the advantage of learning from your knowledge,
-Bruce

P.S. And by the way, i have no idea as to when or why to use ".Form." near the end after all the forms have been stipulated in the "FORMS!" statement, but i find sometimes when i stick it in it stops an error from occurring and other times i have to omit it...

 

[June7]

GECo is declared as what?

You appear to be referencing a control within a subform. A subform is created by installing a subform container control on a form and setting the container's SourceObject property. Referencing a subform requires referencing the container control. I always name container different from the object it holds, like ctrAddEquip. Then you use .Form to reference the form object in the container. So my code would be:

Forms!frmOrderCreate.ctrAddEquip.Form.GrossEquipmentCost

or

Me.ctrAddEquip.Form.GrossEquipmentCost

 

[Josef P.]

Perhaps there is no data record in the subform.
The locals window can help to find the problem.

 

[arnelgp]

maybe try using Nz() function:

GECo = Nz(Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost, 0)

 

[MajP]

If there are no records you will get that error. No records is not the same as null.

 

[brucemc777]

GECo is declared as what?

You appear to be referencing a control within a subform. A subform is created by installing a subform container control on a form and setting the container's SourceObject property. Referencing a subform requires referencing the container control. I always name container different from the object it holds, like ctrAddEquip. Then you use .Form to reference the form object in the container. So my code would be:

Forms!frmOrderCreate.ctrAddEquip.Form.GrossEquipmentCost

or

Me.ctrAddEquip.Form.GrossEquipmentCost

CECo is declared as Long
Yes, we have a parent-child relationship. The parent is frmOrderCreate. The child is frmOrderAddEquip. GrossEquipmentCost is a field in a continuous form that is showing as a record with empty fields because it is the first record to be input for the account.

I also have a field in the same child form's footer ([EquipmentRowCount]) (which i really should have titled EquipmentRecordCount...) that provides a record count ( = Count([IDEquipment]) where IDEquipment is the key for the items in the continuous form. In a permutation where i try to read that prior to reading the record field value of GrossEquipmentCost, it also is blank and results in the same error of "You entered an expression that has no value". Hell yes! That is my point of testing that! If it has no value OR a value of 0 i want to branch one way, and if it has a value greater than 0 then another. I do not have any issues reading other fields in the same child form in the format i am employing, but look forward to experimenting with that which you have provided; thank you!

 

[brucemc777]

Perhaps there is no data record in the subform.
The locals window can help to find the problem.

This is correct in that at this point there is no data in the subform, only labels and the like, but how do i test if there is no data? Since other methods throw errors i would expect that IsError would result in True, but it comes back False. I might be implementing wrong as i predominantly coded in Excel VBA and i find the differences to the point of being absurd, but it is what it is...

I am about to clear all errors, precede with a On Error Resume Next, then after the line, provided the code continues to run and not halt, check for errors before resetting err.clear. Occurred to me in the middle of the nite and hopeful, but whether or not it works, i still wonder why something that throws errors doesnt trigger IsErro - though if i recall there was a variation of type of error in Excel for which one had to specifically test, but i admit i am losing personal memory (akin to "losing my marbles").

Thank you!

 

[brucemc777]

maybe try using Nz() function:

GECo = Nz(Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost, 0)

Many thanks! Unfortunately btdt. Also throws the "no data" error!

 

[brucemc777]

If there are no records you will get that error. No records is not the same as null.

OK! So how do i test for that? I was trying to overcome this possibility by including a textbox that would display the record count, but i find that not only if there are no records it, or at least my implementation of it, also is blank and generates the same error. As the Control Sourse for that textbox:
= Count([IDEquipment] where IDEquipment is the key for the items in the continuous form.

Thank you!

 

[Minty]

You need to check the record count in the subform first, something like

If Me.frmOrderAddEquip.Form.Recordset.RecordCount =  0 Then ...

 

[June7]

I tested this issue. Subform does not have any records. Code in main form Load event reference to subform Count() textbox returns 0, reference to bound control returns Null.

BTW, don't need to reference field name to get record count. Count(*) works as well. I tested both and same result - 0.

 

[MajP]

You need to show the whole code. The code should only run if there are records. If the code runs from the subform it could run on the subs occurrence event.

 

[brucemc777]

Many thanks!

I have to break away for a while - moving company about to be here to give an estimate (here is some hate speech: I HATE the process of moving), but very much looking forward to working with the info you folks have graciously provided!

 

[The_Doc_Man]

I'm going to ask a dumb question, but in a quick scan of this thread, I am not sure.

What is the name of the actual subform and what is the name of the subform control? It is possible that both the form and the control have the same name - which would be bad, exactly because of problems like this.

This syntax:

Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost

is correct if and only if frmOrderAddEquip is the name of the control. In post #6 you SEEM to suggest that frmOrderAddEquip is a child form, which - if true - is part of the problem.

 

[brucemc777]

Well Hi there The_Doc_Man! Do you do this 24/7???
Parent: frmOrderCreate
Child: frmOrderAddEquip
Control (textbox that relies upon a record): GrossEquipmentCost
other control in child form being used to test: EquipmentRowCount
That last one should be renamed EquipmentRecordCount
(It's only since Access that i have not been including control identifiers in their names, as with years in Excel if it were a textbox i would always start the naming with tb as in tbEquipmentRecordCount, or a combobox was cbx, a checkbox was chb... I am trying to define the advantages of either style)

 

[brucemc777]

I turned on my house front monitoring cameras to watch for moving person. Takes up a screen though, but at least i still have another...

 

[cheekybuddha]

If (in the Immediate window) i try
? Forms!frmOrderCreate!frmOrderAddEquip.GrossEquipmentCost = ""
I get

But you're not testing the same thing:

Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost
                                     ^^^^^
                                     |||||

???

 

[pbaldy]

I use this:

Forms: #Error when the Subform has no records

 

[cheekybuddha]

If the code runs from the subform it could run on the subs occurrence event.

This looks like it was victim of a phone's AutoCorruptCorrect !

 

[cheekybuddha]

And so in wishing to avoid this error i try to test for it. If i try
IsError(GECo = Forms!frmOrderCreate!frmOrderAddEquip.Form.GrossEquipmentCost)i get False.

I don't think IsError() does what you think it does.

See documentation here