Simple 'LastModified' throwing an error

[dannwebb]

I followed the instructions from Microsoft (h t t p : / / office.microsoft.com/en-us/access-help/store-the-date-and-time-when-a-record-is-modified-HA010342039.aspx) to include a "Last modified" date on a form. I did this, oh, several years ago and it's been working just fine. Access 2010, XP (corporate environment, can't change this).

After Windows Updates were pushed through over night, I'm getting the following error when I try to save after updating the database:

"The object doesn’t contain the Automation Object ‘XXXXX’.
You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn’t make the property or method available for Automation Operations.

Check the component’s documentation for information on the properties and methods it makes available for Automation Operations."

My only option is to Stop Macros and exit without saving.

I removed the LastModified field from the form, saved and closed, all good. Then, out of curiosity, I followed the Microsoft directions again from scratch to reinsert. This time I got a Return without GOSUB error. Googling this error returns 3 results, one of which has someone stating that GOSUB is older than they are and doesn't know why the person asking about it is even using it.

I'm resigned to living without my LastModified field, although it would be helpful. Is there perhaps another way of getting this information in a form other than a macro?

cheers.
dann.

 

[David R]

Are you at all able to handle a VBA window? This is trivial there, but I don't speak Macronese so I can't help with your original code.

Anyway, I do this all the time.

Private Sub Form_BeforeUpdate(Cancel As Integer) 
        Me.LastModified = Now() 
End Sub

 

[David R]

Also, http://www.access-programmers.co.uk/forums/showthread.php?t=176469 doesn't ring any bells, does it?

 

[dannwebb]

Are you at all able to handle a VBA window? This is trivial there, but I don't speak Macronese so I can't help with your original code.

Anyway, I do this all the time.

Private Sub Form_BeforeUpdate(Cancel As Integer) 
        Me.LastModified = Now() 
End Sub

Yes. I've appended your code. Just not quite sure what to do next.

 

[David R]

Update something in your form and see if it stored the LastModified datestamp correctly?

 

[dannwebb]

Also, h t t p : / / w w w .access-programmers.co.uk/forums/showthread.php?t=176469 doesn't ring any bells, does it?

I'm afraid not. It doesn't look quite like the same thing. But thank you.

 

[dannwebb]

Update something in your form and see if it stored the LastModified datestamp correctly?

I'm afraid I don't know how to do that, but thank you.

In any case, I've removed the offending macro from the form, because I couldn't save any edits, and I had to keep working in it. It contains ~3,600 records, and I'm in the process of adding/editing ~500 more. I stopped by here yesterday to ask for advice, but the code is now gone due to the problem it was causing.

I was just curious if there was another way to get the same result.

 

[dannwebb]

Update something in your form and see if it stored the LastModified datestamp correctly?

Oh nuts. I misunderstood ... you were telling me what to do next. Sorry. I'll see if I can figure out how to do that.
/smacks self up side of the head.

 

[dannwebb]

Ok, I updated something, and now I get:

"Compile error: Method or data member not found"

 

[David R]

I have this fear that you're trying to put the VBA code into the Properties screen. That would account for some of the problems you are encountering...

Go to Properties. Clear out any misbehaving macros/VBA. Click on the form button in the top left of Design View so you get the box with a box inside it, then go to the Event tab and click the [...] button at the end of Before Update. You should get a VBA screen; put the code in I showed above, substituting the correct name of your LastModified field. Close the VBA window, close and save your form.

Now open your form, update something, and move to a new record/exit the form to see if LastModified is updated correctly. If not, we probably need to see a sample/strippeddown database.

 

[dannwebb]

Umm ... no, not putting it in Properties. I went to Database Tools/Visual Basic, and pasted it in there. Isn't that right? Isn't that the "VBA window"? I didn't even think of going into the form and to its properties ... I wouldn't even know where to put it in there.

When I follow your directions, I get to the bit of clicking [...] and I get a box telling me to choose between Macro Builder, Expression Builder, or Code Builder. I chose Code Builder as a guess, and ended up in the same place I described above.

So. I'd removed it after the previous error, and when I paste it there again, open my form, update something, and save, this time I get:

The expression Before Update you entered as the event property setting produced the following error: Ambiguous name detedted: Form_BeforeUpdate.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

I'm not sure this is worth me bothering you for much longer, you are very kind to try to help ... this seems extraordinarily problematic for such a seemingly simple thing. Perhaps I should just live without my "last modified" date field :\

 

[David R]

Code Builder should be right. It should fill in the first line and last line for you (the 'framework' of the event). Maybe you pasted all three lines and thus it had Form_BeforeUpdate twice?

 

[dannwebb]

Code Builder should be right. It should fill in the first line and last line for you (the 'framework' of the event). Maybe you pasted all three lines and thus it had Form_BeforeUpdate twice?

I've taken a screen shot of what that window looks like when I paste the code (and yes, I pasted all three lines of it). Is this right?

 

[David R]

I see no reason that wouldn't work. Is LastModified actually the name of the control on your form??

 

[dannwebb]

Yes.
I started from scratch .... inserted a new text control, renamed it from Text2354 to LastModified, inserted the code precisely as you directed, and when I attempt to save I get the same error as I described above which began "The expression Before Update you entered as the event property setting produced the following error ...."

This is doing my head in.

 

[CJ_London]

Do you actually have a field called LastModified in your table? That is what you are trying to update - then the controlsource of your new text control (renamed from Text2354) should be LastModified

 

[dannwebb]

Ack ... no, I hadn't. I forgot I'd removed it when the macro was erroring.

I've replaced it ... with its Data Type as Date/Time (is that right?), and gone back into the form and made sure that the controlsource is that field, LastModified.

I still get the same error.

(Next time my manager offers training opportunities, I'm putting my hand up for Access!)

 

[CJ_London]

Is this the error you are talking about?

produced the following error: Ambiguous name detedted

If so, this could imply you have a field or control name which is a reserved word.

Suggest you post the recordsource to your form

 

[dannwebb]

Ok .. hope I'm doing the right thing here.. exported to Excel and attached.
Thank you for your patience.

 

[CJ_London]

No - the recordsource to your form - open your form in design view, view form properties, go to the data tab and copy what is in the recordsource property - or are you saying that the recordsource is something like myTable and what you have sent is the contents of myTable?

Here is a link to explain your error message

http://support.microsoft.com/kb/817411

So, quite simply you have a duplication somewhere, this often happens when developers use words like 'Name' or 'Date' which are reserved words, which is what I was checking for.

Reviewing earlier posts - perhaps you have Form_BeforeUpdate posted more that once in your code? Try compiling it to see if this highlights the error