Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting records (Access 97)

    Hi all, Just trying to to produce a report from a form and subform. Not too hard to do. One of the reports I want is for the current record only. I want to be able to click on a button that selects/filters the current record, runs the report and unfilters when the report is closed. I am assuming a macro will be the best way to do this but not sure where to start. It is really just the selection or filtering for the current record I am stuck with, the report is not a problem. It works for the whole DB and also if I filter manually. Hope it is easy.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Selecting records (Access 97)

    In your DoCmd.OpenReport command setup the criteria field specifying the forms current record's fields.
    If your form has fields Customer as a field then your OpenReport command could look like:

    DoCmd.OpenReport "Reportname", acViewNormal,, "Customer = '" & Me!textCustomer & "'"

    This assumes that textCustomer (a text field at that) is the name of the customer control on the form, and that Customer is the name of the field of the underlying query of the report.

    HTH
    Pat

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting records (Access 97)

    Thank you for your reply. I have a small problem, and I know what you are going to say. You shouldn't have spaces in your control names and fields. Unfortunately I can't get around this as it has and is in use and can't be changed. This is what the line looks like:
    DoCmd.OpenReport stDocName, acPreview, , "Source Cable Number = '" & Me!Source Cable Number & '"
    It comes up with a syntax error. Any ideas?

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

    Re: Selecting records (Access 97)

    Any time you have spaces in an object name, you have to use square brackets around the name like this:

    "[Source Cable Number] = '" & Me![Source Cable Number] & "'"
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting records (Access 97)

    Thank you. Appears to work.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting records (Access 97)

    YOU SAID {and I know what you are going to say. You shouldn't have spaces in your control names and fields. Unfortunately I can't get around this as it has and is in use and can't be changed}

    Actually these names can be changed without a lot of work. 'speed ferret' (COMMERCIAL SOFTWARE) or 'find and replace' (SHAREWARE?) add ins will allow you to rename the control/field where ever it is in use through out the database.

Posting Permissions

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