Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries and Mail Merge (XP SR2)

    I often want to isolate the record I am working on in a query so that a Word MailMerge can link to a query with just one record in it. Access seems to make this very complicated.

    I've just put a second computer in my office, and introduced synchornization between the databases. This means that I can't use the automatic ID in the table to isolate the most newly created record any more, since I have to have random IDs. The person who made my database fixed it so that the last modification of any record was noted in a date field, but that doesn't seem to pick up the time of the modification, and my attempts to use it (even using the sum function to give me the highest value) land me at random with any record made that day. Has anyone any ideas as to how I could crack this?

    Thanks in advance

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

    Re: Queries and Mail Merge (XP SR2)

    The code to store the modification date probably uses the Date function, something like

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.ModificationDate = Date
    End Sub

    If you use the Now function instead of the Date function, the timestamp will hold the date and time.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Queries and Mail Merge (XP SR2)

    I presume by synchronization you mean you've added replication to the database. While there are some virtues to replication, it adds significant overhead to your database, as well as requiring the use of random autonumbers. Did you consider creating a small LAN, or even a back-to-back arrangement so both computers could work on the same copy of the database? I realize it may be too late, but the multi-user aspects of Access are one of it's greatest strengths. As Hans notes, if you use the Now() function, it will get you date and time to the nearest few milliseconds, but it is possible that there will be a later record on the other machine unless you synchronize the replicas each time before you initiate the Word MailMerge.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and Mail Merge (XP SR2)

    Thank you both very much for these. and I've taken both bits of advice. 2 further questions if I may.

    a) is something like the date last edited really the only way in which I can isolate a particular record--it would be really nice if I could isolate the record that happened to be open at the time, without having to create something complicated.

    [img]/forums/images/smilies/cool.gif[/img] I had been hesitant about having two users having the database open, because sometimes I get messages saying that the other user has locked it. Am I right in thinking that two or more users can use the database provided that no-one tries to edit the structures?

    best wishes

    Philip

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Queries and Mail Merge (XP SR2)

    In response to your questions:
    a) I presume your users (you and another person?) are working at the table level. One way to isolate a record is to open it on a form - and using the TOP clause in a query is a quick way to isolate the most recent record. That of course assumes that the record is being saved with a full Date/Time field that gets set using the system clock and the Now() function or something equivalent. As long as you can't depend on the largest number in an autonumber field, you are pretty much stuck with a date/time field.

    [img]/forums/images/smilies/cool.gif[/img] If you are making changes to the structure of a table (or a form or report or code), then Access insists that you have exclusive use of the database. To get around that we usually split the database into a front-end and a back-end, where the back-end only contains tables, and the front-end links to the tables and contains forms, reports and code. That gets around the problem for forms and reports, but not for table structure, since that has to be changed in the back-end. Fortunately, most back-end databases are relatively stable from a structure perspective, so the number of instances where you need to change the structure is small once the database has been tested and debugged. If you want to read more about these concepts, see our Database Splitting Tutorial.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries and Mail Merge (XP SR2)

    Thank you very much--this is very helpful. And I like your site too. I find that most books on Access either just take you through what is obvious, or else are impossibly difficult to understand. Your site is a nice intermediary.

Posting Permissions

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