MS Access: Infamous 'Access can't find the field..."

[MajP]

I am not sure if this is your only problem but that syntax is incorrect.
the dot "." is used before a property and SignSeal is not a property of the recordset.

Either
me.Recordset.Fields("SignSeal")
or
Me.Recordset!SignSeal

If you want to understand why ! works, see discussion

Bang Vs Dot, Me, and Default Properties

This is a cleaned up version of a long thread with several sidebar conversations originally located at the link at the bottom of this thread. There is a lot of confusion about the use of Me. or Me! and Bang vs Dot in general. A lot of the discussion on the Internet is wrong or incomplete. The...

www.access-programmers.co.uk

When you add a field to form it gets added automatically to a controls collection even without a visible control. Now it is a property of a form or report.
This is my you can do
Me.someField
but not
Me.recordset.somefield

 

[GPGeorge]

I have a similar issue and I do not have the skill to figure out how to solve it. I believe I am just not accessing the actual record somehow.

I want a image to become visible if a boolean field is true
My code is:
View attachment 116688
I have the "seal" in a footer section of a report (invoice) called OrderSelect the name of the fied in table is SignSeal
Access will ethier say that it can not find the field or you can not do that in an ADP or something like that.

Can anyone help with this issue?

That's not really valid VBA. I suggest two things. (As MajP already noted)

Add Option Explicit to the top of this and every other module in your accdb. And compile your VBA before trying to run it so that Access can report syntax errors.

Here, there is no reason to refer to a recordset. This should do it, assuming that "SignSeal" is the name of a field in the report's recordsource and that there is a control named "signed_sealed" in the report.

Private Sub Report_Load()

Me.signed_sealed.visible = Me.SignSeal

End Sub

On the other hand, I think a good case can be made for prefixing controls on forms and reports to make the references to them clearer than if they have the same name as a field.



Private Sub Report_Load()

Me.txtsigned_sealed.visible = Me.SignSeal

End Sub

 

[DHookom]

Which section of the report is signed_sealed in. The code should be in the On Format of that section.

Why are you using a recordset? Is SignSeal a field in the report’s recordsource?

 

[arnelgp]

Private Sub Report_Load()

Me.txtsigned_sealed.visible = Me.SignSeal

End Sub

just make sure you Add SignSeal field in your report (make the Visible property to No).

 

[Pat Hartman]

1. ALWAYS use Option Explicit. You need to change the database properties so it is always added to new modules but you will need to add it manually to all existing modules.
2. Move the If to the Report's RecordSource query so you can use conditional formatting.

 

[Shecky]

That's not really valid VBA. I suggest two things. (As MajP already noted)

Add Option Explicit to the top of this and every other module in your accdb. And compile your VBA before trying to run it so that Access can report syntax errors.

Here, there is no reason to refer to a recordset. This should do it, assuming that "SignSeal" is the name of a field in the report's recordsource and that there is a control named "signed_sealed" in the report.

Private Sub Report_Load()

Me.signed_sealed.visible = Me.SignSeal

End Sub

On the other hand, I think a good case can be made for prefixing controls on forms and reports to make the references to them clearer than if they have the same name as a field.



Private Sub Report_Load()

Me.txtsigned_sealed.visible = Me.SignSeal

End Sub

Hey george,
Really appreciate your feedback on this issue.
I am not fully understanding why this is not working. Here I have used the code that you have suggested, yet the outcome is the same.

The field is specified in the report:

And the control is named correctly:

But the result is access does not seem to be able to find the field

Someone had previously started that the control value needed to be saved before?
I do not understand what is meant by adding option explicit. Is the fact that this has not been done the reason it is failing?

 

[MajP]

do not understand what is meant by adding option explicit. Is the fact that this has not been done the reason it is failing?

Option explicit forces you to declare variables. You cannot use variables that are not declared and it will show you the problem at compile. if not VBA lets you declare variables on the fly. Which is something you probably never meant to do.

Example

Dim intX as integer

intX = 5
'add 10 to intX
debug.print inX + 10

You meant to add 10 to intX but you made a typo and VBA creates a new variable on the fly inX. These errors can be real hard to see especially with longer names.

If you have option explicit it will show at compile time that inX is not declared and you will realize your problem. It is stupid that vba allows this to start with.

The point was you might have something as simple as a bad spelling, but cannot see it easily which would cause your code to fail.

 

[MajP]

Not sure what you are showing but I see
Signed-Seal
and
SignSeal

 

[Shecky]

Option explicit forces you to declare variables. You cannot use variables that are not declared and it will show you the problem at compile. if not VBA lets you declare variables on the fly. Which is something you probably never meant to do.

Example

Dim intX as integer

intX = 5
'add 10 to intX
debug.print inX + 10

You meant to add 10 to intX but you made a typo and VBA creates a new variable on the fly inX. These errors can be real hard to see especially with longer names.

If you have option explicit it will show at compile time that inX is not declared and you will realize your problem. It is stupid that vba allows this to start with.

The point was you might have something as simple as a bad spelling, but cannot see it easily which would cause your code to fail.

MajP
Thanks appreciate the explanation.
I have changed my Tools options so that explicit gets added to all new modules.
I have googled and looked for the proper method to add explicit to existing modules.
Where is the correct place to add the code? I do not quite understand what is meant by each "module"
Would that be the same as each event that gets triggered in access?

 

[GPGeorge]

MajP
Thanks appreciate the explanation.
I have changed my Tools options so that explicit gets added to all new modules.
I have googled and looked for the proper method to add explicit to existing modules.
Where is the correct place to add the code? I do not quite understand what is meant by each "module"
Would that be the same as each event that gets triggered in access?

Code modules can be of two types (3 if you consider class modules, but they are separate and not handled the same way).

Each form and report in an Access database can have a code module "behind" it. These are referred to as "Microsoft Access Class Objects" in the VBA IDE. If you do not need code for a specific form or report, you don't need to create a module for it. If you do, it is sometimes referred to as a "code behind" object because it's "behind" the form or report.

In addition, code that is available throughout the database can be in stand alone modules. Here are the three types of modules in a database. Note that the nodes can be expanded to show all of the form and report modules, and all of the standalone modules.

Events are handled by Functions or Subs in a code module. So, no, that's not the same thing.

 

[MajP]

I have googled and looked for the proper method to add explicit to existing modules.

Unfortunately there is no easy way to do it except pasting it into existing modules. (If you had tons of code, you can write code that writes code. But that would be way harder then simply pasting into a few existing modules)

I do not quite understand what is meant by each "module"

Think of them as containers to hold code. As George mentioned there are 3 types of modules.

A form or report has a container to hold its code. These can have event procedures, sub routines, functions, and properties.

You can create standard Modules as you wish to group like code. These do not have event procedures or properties only functions and sub routines. You can have as few or as many as you wish. I will have a module for things like working with dates, using file dialog, working with query functions, etc.

Custom Class modules are a more advanced concept (eventhough form and report modules are class modules.). These are "templates" to create objects that can have properties, methods, functions, and events. Many people build advanced access apps without using any custom classes. I use them extensively.

You will be very glad you required variable declaration (option explicit). I have spent hours debugging people's code not realizing they did not have option explicit set. A simple spelling error in code can make the logic seem broken and often extremely hard to see the problem. Once you add option explicit the compiler immediately points out the problem.