Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Entering Info In Unrelated Table (Access XP)

    I need assistance, using VB, to have information that was modified on a form entered into a table that is unrelated to the form. (Please bear with me; my attempts to explain anything usually lead to confusion.)

    I have a table with user information called 'tUsers'.
    I have a table that will be used to record changes to user information called 'tUser Modifications'.
    I have a form that is used to edit user information called 'fUsers Edit'; this form's record source is 'tUsers'.

    Using the 'On Unload' event of 'fUsers Edit', I need to have Access update 'tUser Modifications' with any updated information.

    The problem is that VB gives the following error when attempting this:

    Run-time error '2465': ...can't find the field '|' referred to in your expression.

    [tuser modifications]![ModItem] = "First Name"

    I've tried a variety of ways to reference the field:

    [tuser modifications].[moditem] = "First Name"
    <tables>![tuser modifications]![moditem] = "First Name"
    <tables>.[tuser modifications].[moditem] = "First Name"

    Nothing works...always the same error.

    Can someone help me understand what I'm doing wrong?

    Eric

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    You can't refer directly to a table that is not part of the record source of the form. You must either create an update query or append query and execute that, or open a recordset on the modifications table and manipulate that.

    Could you explain in more detail how the modifications table is to be manipulated? The line of code you attempted indicate that it doesn't have the same structure as the users table., so it is not clear to me yet what should be done.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    Why would you be doing it on the unload event of the form, anyhow? The logical place is the AfterUpdate event of the form. You don't want to do any updating to a remote table if nothing was changed.
    Charlotte

  4. #4
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    Hans...

    The modification table's entire usage is simply to capture modified user information for the purpose of tracking changes to user records. Once the information is in, it remains in and isn't deleted. Essentially, there are a total of five fields in the modification table: User (full name), Item (whatever was modified...first name, location, etc.), Before (what it was before being modified), After (what it is after being modified), and ModDate (date information was modified).

    While I was hoping to be able to refer to an unrelated table like you can with a form, at least I now know that I can stop banging my head trying to come up with the correct coding. <chuckle> I'll just create an append query like you suggested. Thanks!

    Eric

  5. #5
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    Charlotte...

    My knowledge of Access, while growing, is still somewhat middle-of-the-road. I never considered the form's AfterUpdate event because it didn't occur to me. I've only used the AfterUpdate event for the controls on the form. My primary concern was ensuring that information was not entered into the modifications table before the form was closed.

    When does a form's AfterUpdate event take place? Can you tell me the events that happen, in the order they occur, when a form is closed? (For that matter, can you tell me the same for when a form is opened?)

    Eric

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    From the online help for Visual Basic in Access:
    <hr>When you first open a form, the following events occur in this order:

    Open > Load > Resize > Activate > Current

    If you're trying to decide whether to use the Open or Load event for your macro or event procedure, one significant difference is that the Open event can be canceled, but the Load event can't. For example, if you're dynamically building a record source for a form in an event procedure for the form's Open event, you can cancel opening the form if there are no records to display.

    When you close a form, the following events occur in this order:

    Unload > Deactivate > Close

    The Unload event occurs before the Close event. The Unload event can be canceled, but the Close event can't.<hr>
    When you leave a record, whether by moving to another one or by closing the form, Access checks if the user modified any of the controls bound to the record source. If so, the Before Update and After Update events occur, in that order. The Before Update event can be canceled, the After Update event can't.

    If your user modifies a record, then moves to another one without closing the form, the Before Update and After Update events occur, but none of the Unload > Deactivate > Close series of events.

  7. #7
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    Hans...

    I now recall reading the event order in the Access help. Thanks for reminding me.

    And thanks for the info on the form Before & After Update events!

    Eric

  8. #8
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    Hans...

    While I truly appreciate the help I've been given, I'm still having difficulty getting this to work. Additionally, I went off-track from the original issue which, hopefully, you - or someone - will help me correct.

    The problem I'm having is when a field is blank...or null.

    The user table requires only the first and last name to be entered. (This is just to get the user in the database; all other fields can be added at a later time.)

    As mentioned previously, the user modification table has five fields. After changing a record, the user modification table should be updated as follows:

    User: Full name of the edited user
    Item: Whatever was modified (first name, last name, extension, etc.)
    Before: What the record was before being modified ('N/A' if the field was empty before being modified)
    After: What the record currently shows
    ModDate: (current date)

    For the most part, it works fine. However...let's say there are two employees: Jim Dandy & John Doe. Both work in the sales department; Jim's extension is 111 & John's extension is 112.

    1) Jim Dandy prefers to be known as James. So you edit the record and the user modification table reflects this as follows:

    User: James Dandy
    Item: First Name
    Before: Jim
    After: James
    ModDate: (current date)

    2) John Doe prefers to be known as Jack. So you edit the record and the user modification table reflects this as follows:

    User: Jack Doe
    Item: First Name
    Before: John
    After: Jack
    ModDate: (current date)

    User: Jack Doe
    Item: Department
    Before: N/A
    After: Sales
    ModDate: (current date)

    User: Jack Doe
    Item: Extension
    Before: N/A
    After: 112
    ModDate: (current date)

    As you can see, using basically the same code, and changing the exact same thing (first name only), the modification table is updated differently for each user. Additionally, it isn't the same thing that's added to the table for each user. For example, let's say you have three users and, for whatever reason, the first name - and ONLY first name - is modified. The modification table will reflect this as:

    1) First user's name is updated
    2) Second user's name is updated. Additionally, the department & extension are also updated by indicating they were empty before (which is untrue)
    3) Third user's name is updated. Additionally, the extension, email, cell phone & password are updated by indicating they were empty before (which is untrue)

    Now, I know you can't solve this by just reading what I've written. But, if you could ask me questions that I can answer, perhaps we can work toward a solution.

    Thanks!
    Eric

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Entering Info In Unrelated Table (Access XP)

    One possibility is to create an After Update event procedure for each control that can be edited by the user. Advantage is that the OldValue property contains the previous value of the field, the Value property the new one. Downside is that if the user changes the first name, tabs to the last name, shift+tabs back to the first name, changes it, tabs out, etc., you'll register each change separately, and you'll have to write a lot of procedures.

    Alternatively, you could store the value of each field in variables of type Variant (to allow Nulls) in the On Current event of the form. If the user changes anything, eventually the After Update event will fire; you can compare the values of the fields with the values of the variables; if changed, write a record to the log file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •