Solved Access cannot find the field referenced in the expression

[zelarra821]

Hi.

I have a problem when I refer to a field, from VBA, since it tells me that it cannot find the field, or it gives me invalid Null error. That is to say:

As you can see below, in the report source query, these fields do exist:

This database is a copy of another one: I have created this one that you are seeing for a client taking from example the one that we are using for the store that we have, and in this last one it does not give this error.

I honestly don't know what I'm doing wrong.

Thanks.

 

[bob fitz]

I neither speak nor understand any languages other than English but from what I think I understand the vba error message is telling that you don't have a control named "Nombre".
In your code you use
Me.Nombre

which refers to a control with that name. Perhaps you have a field with that name in the report's recordsource in which case try:
Me!Nombre

 

[zelarra821]

I am very sorry, I have not noticed that the messages were in Spanish. I've translated everything but that.

I suppose you will not care if the query is in Spanish. What I'm going to copy here is the two messages:

1. Invalid use of Null
2. ... can't find the "Nombre" field referenced in the expression.

I have tried what you tell me, and it gives me an error that I have marked you before as 2.

Thank you very much.

 

[bob fitz]

Can you post a copy of the db for us to play with

 

[zelarra821]

Yes of course. There you go.

I explain to you. This database is for the management of invoices and budgets. I have simplified it, to get to the point, and therefore you are only going to see invoices. In "FFacturas" generates the invoice. There is a button to print called "Imprimir". When you click on that button, it calls you to a function that I have in a module called "mdlCodigos", and what it does is hide the Dirección and Ciudad fields (both the label and the field) if there is nothing in them. The error is that it tells me that these fields tell me that they do not exist, but if you see the origin of the report you will see it. As much as I think about it, I am not able to find an answer.

Thank you.

A greeting.

 

[Gasman]

You have digits in the names? Domicilio1, Ciudad1, and 2 ?

The error is n the module being called.

 

[zelarra821]

Hi.

I don't have numbers in the Nombre, Domicilio1, Ciudad1 and Ciudad2 fields.

The error can not only be in the module. If you add in the report, for example, Me.Domicilio, it gives you the error that it cannot find the field.

By the way, Dirección and Domicilio is the same in Spanish, as well as Ciudad and Población, which I do not know why I have put in each place one thing.

 

[Gasman]

You have this in the module

 If RName.Domicilio = "" And Not RName.Ciudad = "" Then

and Domicilio1 in the report. Same problem with Ciudad ?

 

[zelarra821]

Yes, exactly.

Domicilio and Ciudad are the fields in the Clientes table. And Domicilio1 and Ciudad2 are the text boxes of the report. In the module I use the fields in the Clientes table, and they tell me that they don't exist.

 

[Gasman]

Not even sure you can reference source fields in a public module?. I have only ever did it for controls.
I tried Bob's syntax with ! and get

 

[zelarra821]

That is the mistake it gives me. The same is what you say, that you cannot do what I want.

 

[Gasman]

To save anyone else trudging through the code, the o/p is passing a report reference to a public module and trying to reference the source fields in the report recordset and fails at the first If statement.

Public Function OcultarCamposDeCliente(RName As Report)

    Dim Direccion As String
    Dim Ciudad As String
       
    If RName.Domicilio = "" And Not RName.Ciudad = "" Then
        RName.LblDireccion.Visible = False
        RName.Domicilio1.Visible = False
    End If

    If RName.Ciudad = "" Then
        RName.LblPoblacion.Visible = False
        RName.Ciudad2.Visible = False
    End If

    If RName.Domicilio = "" And RName.Ciudad = "" Then
        RName.LblDireccion.Visible = False
        RName.LblPoblacion.Visible = False
        RName.Domicilio1.Visible = False
        RName.Ciudad2.Visible = False
    End If

End Function

I've tried with even adding controls for those fields and it still balks with the error previously mentioned.?

 

[zelarra821]

I can't help you because I don't know how to fix the problem.

 

[Deleted member 147267]

I suspect that it's because an object reference isn't being passed - it's the object itself. In that case I'd expect to have to access the object methods and properties by using complete reference syntax. So maybe something like
RName.Controls("ControlNameHere")
EDIT
Based on what I see in that code, why pass the object externally anyway? Why not just pass 2 variant values if it has to be done externally?

 

[zelarra821]

Ahhh, ok. Thanks a lot.

 

[Deleted member 147267]

FYI - edited last response. This post will trigger an email notification.

 

[zelarra821]

Ok, I will do it in this way. Thanks

 

[Gasman]

I suspect that it's because an object reference isn't being passed - it's the object itself. In that case I'd expect to have to access the object methods and properties by using complete reference syntax. So maybe something like
RName.Controls("ControlNameHere")

@Micron I've just tried that in the immediate window and get the same error.

 

[Deleted member 147267]

OK, I downloaded your db in an effort to stop guessing. To the best of my knowledge, you can refer to fields in a recordsource when there are no controls on a report only but you can't refer to them as if they were a member of the report controls collection - because they are not. You would need to either refer to the field of the record source or the proper name of the report control. So using Domicilio1, which is the name of the control, should simplify things. However, you have 4 report controls that have circular references (they refer to themselves) and you cannot do that. You need to redesign to eliminate this.

Also, it is pointless IMO to attempt to refer to a form or report's control values during the open event because no data has been loaded until the load event. If you move the function call to the load event, the report should at least open if you fix the control name references. BTW, there is a mix of those names in the code - using Domicilio and Domicilio1 to seemingly refer to the same thing.

 

[Gasman]

Also, it is pointless IMO to attempt to refer to a form or report's control values during the open event because no data has been loaded until the load event.

That will the main reason. I did put this in my copy

    For i = 0 To RName.Controls.Count - 1
        Debug.Print RName.Controls(i).Name
    Next

and got to see the controls. I even added two dummy controls for those fields and it still complained.
I was not sure when the fields values were available.