automation error message

[Jen0dorf]

Hi

Have an issue with an error



I created a public variable

Option Compare Database
Option Explicit
Public varGraveNo As String

thats everything in the vb window no other entry and I'm reasonable sure it's in a standard module but I've posted a screen shot.

I Have a form with an unbound text box is named GraveNo

In the afterupdate event of the text box I added this code:

=[varGraveNo]=[Me].[GraveNo]

which I assumed would set up the variable

On the form I have a button which will open another form based on a query which will use the variable as a filter.

But when I enter a number in the text box and press my button the following appears:

The expression After Update you entered as the event property setting produced the following error: The object does not contain the Automation object 'varGraveNo'

I've done a google search for help but despite much reading have come up with nothing

Has anyone any ideas?

thanks

Ian

[June7]

Remove the leading = sign.

[Jen0dorf]

Hi

thanks removes the = (decided to hate the = sign)

As is always the case lost one error only to get another

cannot find the object

 'varGraveNo]=[Me

.

if .. is anew macro or macro group make sure you have typed it's name correctly

IS it normal for Access to truncate code errors?

ie is the code above the full line or has access missed off the start and end?

Thanks for the hint about the =

Ian

[John_G]

[varGraveNo] should not be in square brackets. Access will look for a form control or table field with that name, which it can't find, thus giving you the error.

references to Variables , global or otherwise, should not have square brackets.

[June7]

Ooops, I should have seen the brackets. As John said, remove them from the VBA variable. They are used to define Access objects.

[Jen0dorf]

Hi

back again - still having issues so as my brain was going in circles I went back to basics! To create a variable and then use that to populate a field

So I created a new database with just two tables

I declared a public variable

In form one I placed a text box and in the afterupdate event added to code to create the variable

In form 2 I added a second text box and as it's control source used the variable.

the idea was that I would prove I had created the variable.

However I get the same error which I think means the variable can't be found.

I've uploaded the database in the hope that another typo will be found!!

Apologies for being a pain but this is really gettingto me

thanks

Ian

[June7]

Cannot use Me alias in Access controls. The expression in YourAge textbox ControlSource will not work. Cannot populate a field this way.

There is no code behind the form. To build VBA event code behind a form or report, select [Event Procedure] in the event property then click the ellipsis (...) to open the VBA editor. Type code in the event procedure. This is where that expression must go.

However, there is also no code that sets the age variable. Why would you need this? Why are you even saving age to table? Age should be calculated when needed.

[Jen0dorf]

Hi

thanks for the reply.

The use of age etc was arbitary just wanted to create a simple database to test out the code I'm using in the real database.

From your reply you indicate that there is no code to set the age variable. I suspect this is where my real issue is?

In form one on the text box I have the onupdate set to

YearsOld = Me.[AgeTextBox]

I assumed that this would setup the variable ? so that when when the button was pressed the variable YearsOld would be populated with the value of the text box.

I assume my logic is wrong?? If so then how do I get the "user" typed value of a text box intoa variable?

Sorry to be a pain but all my reading on google etc tells me my code should work

cheers

Ian

[Jen0dorf]

Hi

thought ( terrible thing) I had a solution whilst trawling the web found this MS link

http://answers.microsoft.com/en-us/o...d307cc4?auth=1

which appears to be exactly what I've done although I'm using access 2010

All very confusing

Ian

[John_G]

Referring to post #8 above -

Your code as shown will assign the value to the variable YearsOld. Now the question is: where have you declared that variable with a Dim statement?

In order for that variable to be recognized in other forms or modules, you must declare it in a standard module (i.e. not in a form or report) using:

Global YearsOld as Integer

Just using a Dim to declare it won't work.

If you didn't declare it in a standard module, your code might work without error, but all it will do is create the variable in that Sub, but it will be recognized only in that Sub. Check out the use of Option Explicit - it should (IMO) be included in the declarations section (top) of every form, report or standard code module. It forces you to explicitly declare every variable you use. (References to form controls are not considered to be variables.) Using Option Explicit in all modules will save you all kinds of time trying to find errors, as it will often generate compilation errors caused by typing mistakes.

There are places you cannot use a global variable: queries don't like them, and you can't use them in an expression to populate a form control using the "=" sign; you need to use VBA.

[Jen0dorf]

Hi

as usual thanks for the input, seems I need to use VBA as I want to eventually use the variable as criteria in a select query!!

I'll get working on it

cheers

Ian

[June7]

No, an expression in textbox will not set a VBA variable. This must be done in VBA procedure.

A query cannot directly reference a normal VBA variable. However, a query can reference a VBA custom function and that function can reference the variable. Also, query can reference a TempVars variable. https://blogs.office.com/2010/09/27/...2007-and-2010/