Solved: SetValue macro fails

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Have a mental block I need help unblocking! Situation: Access split database. Form for one table contains a name lookup combo box that uses a query containing only the primary key ID and a formula that produces lastname, firstname middle, sufx. The ID is the field that finds the record.

However, I also have a date field on the form called [EditDate]. I want that field to update to Date() if the record is edited. So I have a simple SetValue macro as an AfterUpdate event where the Item is [Forms]![DMMemVol]![EditDate] and the Expression is Date().

Here's the problem. The combo box that looks the record up works perfectly IF I DON'T HAVE THE SETVALUE MACRO AS AN EVENT. As soon as I put the macro in as either a BeforeUpdate or AfterUpdate event, and the user has just edited a record then tries to use the combo box to find another record, we get the dreaded "Halt" box indicating that the SetValue macro failed. If you select "Debug", it takes you to the VBA for the combo lookup box, which is as follows:

Private Sub QFName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![QFName])
Me.Bookmark = rs.Bookmark
Title.SetFocus
End Sub

The line that is highlighted as the one that's not working is the "Me.Bookmark = rs.Bookmark".

The odd thing about this is that I have another larger database that has identically the same combo box and [EditDate] field where both the combo box and the SetValue macro work perfectly.

What's wrong? (Be aware that my knowledge of and ability to use VBA is severely limited.)
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Don, the only thing that I can think of is that somehow the Macro is changing the form's Recordset in some way.
Change the Before or After Update event to an Event Procedure, click the the 3 dots and enter in the sub
me.[EditDate] = Date()
See if that stops the error.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Don, the only thing that I can think of is that somehow the Macro is changing the form's Recordset in some way.
Change the Before or After Update event to an Event Procedure, click the the 3 dots and enter in the sub
me.[EditDate] = Date()
See if that stops the error.
Hi, Tony. Nope; didn't work. I also notice that after editing any record, the navigation buttons cease to work. IOW, the form has in effect crashed.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Tony: Here's some additional info. After editing a record and then attempting to navigate to the next record, I get this (long) error message: "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."

If I then hit the Esc key, I'm confronted by the familiar "Action Failed" box for macros, that contains:
Macro Name: SetPersRecsEditDate
Condition: True
Action Name: SetValue
[Forms]![DMMemVol]![EditDate], Date()

Don't know if this provides any clues.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Did you put the VBA code in the VBA Editor?
Can you post a copy fo the database less data?
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Can't do via TSG because of the (extremely) low limit of file size, but will send via e-mail.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Don, received, I fixed it by moving the Me.EditDate = Date to the Form's On Dirty Event Procedure instead of the after update.
 

DKTaber

Thread Starter
Joined
Oct 26, 2001
Messages
2,871
Don, received, I fixed it by moving the Me.EditDate = Date to the Form's On Dirty Event Procedure instead of the after update.
Thanks much, Tony. Only tried it once, but it appears to work perfectly. I'm still bewildered why the macro does not work in this database but does in a much older one. In fact, I imported the macros from the older database and changed the object names, and they all work except that one.

I will mark the thread as 'solved'. Happy New Year!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top