Getting Automation object not found error when setting the value of a Form's control

[MaxwellX]

Hi,

I have a little experience in VB and also Office 2003, but I am new to VBA and Macros in Access 2007.

What I am trying to achieve here is to update two hidden controls when user clicks the "Save and New" button on the Inventory Details form.

I am using the MS Access 2007 template available from the MS website called "Inventory". You should be able to see it from your Access 2007's "Getting Started" page and then browse for templates under the "Business" category. It is an online template.

If you are able to refer to the "Inventory Details" form, please do so. If not, I'll try to explain clearly.

Okay, so I've added a few fields to the Inventory table to customise it. I've added two 'check' fields called First Entered By and Last Modified By. There are both Text fields.

These two fields are added to the Inventory Details form, and are set to Visible=No to hide them from user visibility.

Now comes the purpose of these two fields. On this form, when the user has entered data for this inventory item's record and then clicking Save and New, it will check whether the First Entered By entry is empty or not. If it is empty, it gets the currently logged in user's name and then put it there. If it is not empty (means it is an existing record being modified by the current user), then it will put the current username in the Last Modified By field.

This is to put a "check" on the record to see it was first created by who and last modified by who. We have to log in with a username before we can have access to this file as it is stored on a server, thus having this "check" field is sufficient. I am not after fool-proof security. Just want to keep it simple as this application is not for profit.

The basic idea is like this:
When User clicks the Save and New button, the Next Record selector or closes the form --> Check if First Entered By is empty or not
> If empty, get Username and enter into the First Entered By field
> If not empty, get Username and enter into the Last Modified By field

However, if all other fields are not edited at all when User closes the form, this check should not be performed which would cause a false alarm and end up creating a blank entry with the FEB/LMB fields filled in.


What I did is to add on to the existing macro in the On Click event of the Save and New button.

Condition                                     Action       Arguments
[Form].[Dirty]                                RunCommand   SaveRecord
[me].[First Entered By].[Value] Is Null       RunCode      =[me].[First Created By].[Value]=fOSUserName()
[me].[First Entered By].[Value] Is Not Null   RunCode      =[me].[Last Modified By].[Value]=fOSUserName()
[Form].[Dirty]                                RunCommand   SaveRecord

That was part of the macro code for the Save and New button at the On Click event.

I know the logic is not there, but that's what I have so far with my limited knowledge.

The problem I'm having now is that when I click the Save and New button, I get the error "The object doesn't contain the Automation object 'me.'.

I am not familiar with the correct syntax for the macro here. I've tried some other ways but keep bumping into the Automation object error.

Could someone kindly help me out? This is my first time working on Access 2007 and touching on Macro. The last time I did Access was a few years back during my studies, and it was very basic.

Thank you in advance!

 

[JamesMcS]

I'd do this in VBA if I were you - just use the code editor instead of a macro and use an if statement....

 

[MaxwellX]

I'd do this in VBA if I were you - just use the code editor instead of a macro and use an if statement....

But I'm not sure of the codes and variables. I would do this in VBA too if I could...

 

[JamesMcS]

The problem with the macro, from what I can see as I've not used them like this, is that me is showing as [me]. I think that means Access thinks it's an object, as in a form or field.

So from your macro I'm assuming you want the record saved every time a control is updated?

Code wise it would be (in the form's on dirty event):

runcommand accmdsaverecord

And in your button's on click event:

If [first created by].value is null then
[first created by].value = fosusername()
Elseif [last modified by].value is null then
[last modified by].value = fosusername()
Endif