1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: SetValue macro fails

Discussion in 'Business Applications' started by DKTaber, Jan 9, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    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.)
     
  2. OBP

    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.
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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.
     
  4. DKTaber

    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.
     
  5. OBP

    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?
     
  6. DKTaber

    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.
     
  7. OBP

    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.
     
  8. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    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!
     
  9. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/973587

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice