Solved Null Value - Runtime Error 2427 - You entered an expression that has no value

[Saviolin]

Hello Everybody!

I'm am getting this error "Runtime 2427 You entered an expression that has no value."

I've tried others solutions, that it brought other errors (2447 / 424 / 13)

It is a Form with an Input Box, you type a value number and then it check this in a Query. If the "value number" exists in the Table, everything it's fine, the VBA code runs, and the Form it is show in a perfectly way. But if the value number doesn't exist in the Table, so it´s a null value, and here its the trouble.
I want that in case the value number is null, the Form will show a text like " The Input Code doesn´t Exist", or something like that.

This is the VBA code:

Private Sub Form_Load()

Form_Prueba3Form.lblinfo.Caption = ""

 Me.Prod.SetFocus
 
 If IsNull(Me.Prod) Then
 
 Me.lblinfo.Caption = "El código buscado no existe."

Else
 
 Me.lblinfo.Caption = [Prod]
 
End If
 
End Sub

Any idea how to solve this problem?

Thanks!

 

[theDBguy]

Hi. I don't see an Inputbox() line in the code you posted. How does it relate to this code? Which line is highlighted when you go to debug?

 

[Pat Hartman]

The code does not belong in the load event. It belongs in the Current event.

 

[Saviolin]

Hi DBguy!

The InputBox comes from an Access Query and this is the SQL code:

SELECT Productores_mails.Codigo AS [Código Productor], Productores_mails.[Apellido y Nombre] AS Productor, Productores_mails.Mail AS [E-Mail], IIf([Deudores]="S","Si","No") AS [Envía Deudores por Premio], Gestionador.Gestionador
FROM Productores_mails INNER JOIN Gestionador ON Productores_mails.Codigo = Gestionador.[Cod Prod]
WHERE (((Productores_mails.Codigo)=[ Ingrese el Código de Productor]));

The highlighted line is this:

Me.lblinfo.Caption = [Prod]

The code does not belong in the load event. It belongs in the Current event.

Hi Pat!

I don't understand the question, the code belongs to the Form "Form_Prueba3" inside there's a Subform "Form Busqueda Mail x Cod Productor2".

The Form has a Command Botton just to return to a Previous Menu, and a Label, where I want a specific text appear every time the Input Value is Null or show the Value if is not null.

I leave two Form captures, the first one when the data is ok, and the other one when input value is Null, the red label with the message doesn't appear

Thank you

 

[CJ_London]

not clear which form you are referring to, but if it is the subform, be aware that subforms open, load and current before the main form opens

 

[theDBguy]

The highlighted line is this:

Me.lblinfo.Caption = [Prod]

Just curious, what happens if you change it to this?

Me.lblInfo.Caption = Me.Prod

 

[Saviolin]

not clear which form you are referring to, but if it is the subform, be aware that subforms open, load and current before the main form opens

Hi CJ_London!
I try to open the subform, and when I type a number not included in the Table, the subform opens, but without any data on it. I suppose the null value makes this

Thanks!

 

[Cronk]

Me.lblinfo.Caption = [Prod]

Where does the value Prod come from? It's not in your query in #4

 

[Saviolin]

Just curious, what happens if you change it to this?

Me.lblInfo.Caption = Me.Prod

Hi,

I try, and now the error changes to "Runtime 2447 There is an invalid use of the .(dot) or ! operator or invalid parentheses"

 

[Saviolin]

Where does the value Prod come from? It's not in your query in #4

Hi Cronk!

Prod is a Text Box´s name in the Form and its control origin is this:

=[Form]![Prueba3Form]![SubForm Busqueda Mail x Cod Productor Piloto].[Form]![Productor]

Thanks

 

[Cronk]

Is [SubForm Busqueda Mail x Cod Productor Piloto] the name of the subform itself or the name of the control holding the subform?

 

[Saviolin]

Hi All!

After reading all your questions, you all make me think again in what I was doing, so I decided to change the Form and Subform way by just a Form, I put the textbox, Label and command button on the header of the Form, I use the same VBA code with the suggested line by DBguy, and magically, all becames wonderful!!

Thank you very much, DBguy, Pat Hartman, Cronk, and CJ_London, you were very helpful, and very quick to try to help me.

Thanks!!!!

 

[Pat Hartman]

don't understand the question, the code belongs to the Form "Form_Prueba3" inside there's a Subform "Form Busqueda Mail x Cod Productor2".

I made a statement. I did not ask a question.
Look at the header of the code you posted. It is the form's Load event. Code of this nature does NOT belong in the load evne of the form, it belongs in the Current event so it will run for each new record, not just once when the form opens.

Also. you do not need to set focus to a control before examining it. The only time this would be true is if you want to examine the control's .text property. Access controls have three "buffers".
.OldValue - original value when the record loads
.Value - this is the default and so is normally omitted. It references the current value of the control. Use Me.ControlName rather than Me.ControlName.Value
.Text - this one confuses most people. This is the place where data is captured as it is typed so if you are doing something in the Change event, which runs once for each character as it is typed, you need to use the ltext property to see this data before it is moved to the .value buffer.

 

[theDBguy]

Hi All!

After reading all your questions, you all make me think again in what I was doing, so I decided to change the Form and Subform way by just a Form, I put the textbox, Label and command button on the header of the Form, I use the same VBA code with the suggested line by DBguy, and magically, all becames wonderful!!

Thank you very much, DBguy, Pat Hartman, Cronk, and CJ_London, you were very helpful, and very quick to try to help me.

Thanks!!!!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Saviolin]

I made a statement. I did not ask a question.
Look at the header of the code you posted. It is the form's Load event. Code of this nature does NOT belong in the load evne of the form, it belongs in the Current event so it will run for each new record, not just once when the form opens.

Also. you do not need to set focus to a control before examining it. The only time this would be true is if you want to examine the control's .text property. Access controls have three "buffers".
.OldValue - original value when the record loads
.Value - this is the default and so is normally omitted. It references the current value of the control. Use Me.ControlName rather than Me.ControlName.Value
.Text - this one confuses most people. This is the place where data is captured as it is typed so if you are doing something in the Change event, which runs once for each character as it is typed, you need to use the ltext property to see this data before it is moved to the .value buffer.

Thanks Pat by all your advices, I'll keep it in mind in future proyects, i could solve my problem changing the way I made the Form

Thank you so much for everything

 

[Saviolin]

Hi. Glad to hear you got it sorted out. Good luck with your project.

Thank you!