Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ? Plus Audit Report (A2K SR1)

    I have a frmCheckInEdit that allows the operator to edit information in tblRental. If the operator
    changes the [RoomNumber] field, I want to change [Occupied] in tblRoom for the old room
    number to

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

    Re: ? Plus Audit Report (A2K SR1)

    (1) You can do this in the AfterUpdate event of the Room number control. The old room is in the OldValue property, the new room in the Value property of the control.

    If a form based on tblRoom is open, you can set the Occupied values there.
    Otherwise, you can use DoCmd.RunSQL or DAO to execute two update queries. The SQL for these would be something like:
    "UPDATE tblRoom SET Occupied=False WHERE RoomNumber=" & [controlname].OldValue
    "UPDATE tblRoom SET Occupied=True WHERE RoomNumber=" & [controlname].Value

    If RoomNumber is a text field, you must enclose it in quotes:
    <pre>" ... WHERE RoomNumber='" & [controlname].OldValue & "'"</pre>


    Standard rant:

    You don't really need an Occupied field. It is derived information. Storing it in a field may cause problems if it isn't updated correctly for some reason. You can create a query based on tblRoom and tblRental that calculates whether a room is occupied.

    (End of rant)

    (2) If you do a search on "audit" or "tracking" or "revisions", you'll find several threads on the subject of tracking revisions. For instance, those starting at <post#=148901>post 148901</post#> and <post#=149961>post 149961</post#>.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ? Plus Audit Report (A2K SR1)

    The derived information seem like the way to go.

    I haven

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ? Plus Audit Report (A2K SR1)

    I don't see the attachment.

    Tom

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

    Re: ? Plus Audit Report (A2K SR1)

    <P ID="edit" class=small>Edited by HansV on 05-Aug-02 17:02.</P>Tom,

    I have attached a zipped Access 97 database. You'll have to unzip and convert it.
    There is no "occupied" field in the tblRooms table.
    Have a look at the queries.
    qryCurrentlyOccupied is a selection query that shows the rooms that are occupied today.
    qryCurrentlyOccupied2 displays a list of all rooms with an occupation flag for today.
    qryOccupied is a crosstab query that displays an overview of room occupation for dates with at least one occupancy.
    qryOccupied2 is similar, but shows all dates. This query is the record source of a sample report.

    Attachment added 15:05 UTC
    HTH,
    Hans
    Attached Files Attached Files

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

    Re: ? Plus Audit Report (A2K SR1)

    Oops, you're right. Sorry about that. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    I have attached the zipfile to my previous reply.

    Hans

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ? Plus Audit Report (A2K SR1)

    Thanks for your database. I was able to incorporate the qryCurrentlyOccupied and
    qryCurrentlyOccupied2. I added a Criteria 0 to qryOccupied2 which returns the vacant rooms. It
    works fine based on the report being run today.

    Is there a way to have the query to ask for a report date. i could then change the search criteria to
    ReportDate rather than Date(). I would also need to be able to access that ReportDate to print the report
    date on the rptVacancy which is based on qryCurrentlyOccupied2.

    Many thanks for your enormous assistance.

    Tom

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: ? Plus Audit Report (A2K SR1)

    "Is there a way to have the query to ask for a report date."
    Queries where the criteria are not set in advance, but at run time are known as parameter queries and work in two ways I can think of .
    If you enclose some words in square brackets on the criteria line e.g. [enter start date], the query will ask a question using the words inside the brackets , and treat the answer as the criteria for the query. You can make it more complicated if you wish by having something like this on the criteria line:
    Between [enter start date] and [enter finish date] and the query will ask two questions.

    Another way to achieve the same result is to have a form whose job is to hold the dates you want to use as the criteria for the query , and have the query get them from there. I usually call such a form a report filter.
    e.g. if my form is "frmreportfilter" then the query might have on its criteria line:

    Between [forms]![frmreportfilter]![datefrom] and [forms]![frmreportfilter]![dateto]

    You can use the expression builder to build the criteria expression. Putting the dates on a form makes it easy to also put them on the report, because you can include two unbound text boxes on the report and set the control source of each to the the datefrom and dateto textboxes on the filter form in the same way.

    I don't know how to get access to the dates entered if you have the query prompt for them, but there may be a way.

    I always use the form option. I can check for data type and empty values etc.
    Regards
    John



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

    Re: ? Plus Audit Report (A2K SR1)

    Tom,

    John Hutchison already answered your question. I agree with him that it's best to use a form and to refer to text boxes on the form in both the query and the report.

    But if you want to use a parameter query, you can still refer to the date the user entered in your report:
    1. <LI>In the design of qryCurrentlyOccupied, replace Date() in the criteria line by [Which date] (or any prompt text you want, between square brackets).
      <LI>Next, select Query/Parameters... Under Parameter, enter the exact prompt text (in this case, [Which date]), and under Data Type, select Date/Time.
      <LI>In the design of the report, add a text box to the header or footer. Set its control source to

      =[Which date]
    Note: it'll still work if you omit step 2. But specifying explicitly that the parameter is a date makes Acess check the user input. It also ensures that it will be displayed as a date on your report. Without step 2, the report will display the parameter exactly the way the user entered it.

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ? Plus Audit Report (A2K SR1)

    Many thanks. I was able to incorporate your suggestions.

    Tom

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ? Plus Audit Report (A2K SR1)

    Again, many thanks for your helpful suggestions.

    Tom

Posting Permissions

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