Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Unanswered: viewing specific records in a report

    hey there,

    two questions. i am running this query which is based on a 4 tables. one of the tables - Appointments - has in a field - Prescribed Medication - which may or may not contain information. in the query i have prescribed medicatio and it associated cost (from another table), but when i run the query, the only records showing up are those that have something in the Prescribed Medication field. Is there any way around that?

    next, I was wondering how to view a specific record in a report based on a key field. the form that is based on report has a patient id, name, date, time etc, and a 'view patient bill' command button. when the button is clicked, the information for the current record in the form should only come up. usually the wizard asks if u want to view all records or specific records for forms but it didn't work for the report.

    bajan_elf
    "The extreme always make an impression." - Jeff Hardy

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    For your limited recordset return (Pres Meds), my guess is that you are joining to your assoc price table with a direct join as opposed to a left outer join (i.e., include all the recs from your Appointments table and those that match in the price table). BTW, setting up a normalized Pres Meds table on its own and some referential integrity between the price table (if you have different vendors) and your Appts table will also resolve this issue.

    For your limited report return, you will want to either pass your identifier field to your report from your form using the OpenArgs property of each, or, have your report based off a query and have the form change the SQL of the query based on whatever parameters you want to use.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    ok i fixed the first problem i had, but i still don't understadn the second one. what u are talking about sounds like greek to me.

    i already have a query and it has a unique identifier called PatientID. the form has a button to open the patient's bill report (which is based on the query). i thought maybe there was some kinda code i would have to put in the button like

    DoCmd.OpenReport "rptPatientBill", View, FilterName

    something like that, maybe? where the filtername would be the identifier...
    "The extreme always make an impression." - Jeff Hardy

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    The last argument of DoCmd.OpenReport is OpenArgs, you can assign a value to this property that will be exposed on the report as part of its OpenArgs property. So you could pass a specific PatientID or name or what-have-you, and the report can read this. You can then use the report's Open Event to determine what filter it should have.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    ok this is what i have in the event procedure of the preview button

    Private Sub cmdPreviewBill_Click()
    On Error GoTo Err_cmdPreviewBill_Click

    Dim stDocName As String

    stDocName = "rptPatientBill"
    DoCmd.OpenReport stDocName, acPreview, OpenArgs="PatientID"

    Exit_cmdPreviewBill_Click:
    Exit Sub

    Err_cmdPreviewBill_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewBill_Click

    End Sub


    I'm not too sure about the syntax for the OpenArgs property in the doCmd...

    you said to use the on open event in the report to determine what kind of filter i should have, i have this dbase book that is of no help. what kind of code should go there?
    "The extreme always make an impression." - Jeff Hardy

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Code:
    DoCmd.OpenReport stDocName, acPreview, OpenArgs="PatientID"
    should be
    Code:
    DoCmd.OpenReport stDocName, acPreview, OpenArgs=Me.PatientID
    Otherwise you're just sending the literal 'PatientID' to your report.

    Then in your report_open (?) event, you can use
    Code:
    Me.Filter = "[PatientID]=" & Me.OpenArgs
    Me.FilterOn = True
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    ok i put in the correct code for the preview button, but i'm getting a compile error (method or data member not found) for the second one. where u have

    Me.Filter = "[PatientID]=" & Me.OpenArgs

    Me.OpenArgs <- shouldn't that be Me.PatientID ?
    "The extreme always make an impression." - Jeff Hardy

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I wrote the code quickly with too many assumptions. As a result, the spirit is correct, but the syntax is not. My concern was that you were passing a literal string instead of a form control's value.

    Code:
    DoCmd.OpenReport stDocName, acPreview, OpenArgs=Me.PatientID
    is wrong because OpenArgs=Me.PatientID is being passed as a filter name, not as OpenArgs.

    When using parameters, you have two choices and they must be consistent, you can use the assignment operator :=, or you can put the parameter value in its correct position. In this case it is the last position:

    Code:
    DoCmd.OpenReport stDocName,acViewPreview, , , , Me.PatientID
    Your report is validly complaining because it really doesn't have an OpenArgs property (well it might, but it would be null). It has a filter it can't use.

    Don't change the report code, change the form code.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by HomerBoo
    I wrote the code quickly with too many assumptions. As a result, the spirit is correct, but the syntax is not. My concern was that you were passing a literal string instead of a form control's value.

    Code:
    DoCmd.OpenReport stDocName, acPreview, OpenArgs=Me.PatientID
    is wrong because OpenArgs=Me.PatientID is being passed as a filter name, not as OpenArgs.

    When using parameters, you have two choices and they must be consistent, you can use the assignment operator :=, or you can put the parameter value in its correct position. In this case it is the last position:

    Code:
    DoCmd.OpenReport stDocName,acViewPreview, , , , Me.PatientID
    And specifically if we're talking about Access 2000 and before there is not an OpenArgs property for the OpenReport ...

    Your report is validly complaining because it really doesn't have an OpenArgs property (well it might, but it would be null). It has a filter it can't use.

    Don't change the report code, change the form code.

  10. #10
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If M Owen is correct, and he ususally is, and you are using pre Access2k, then you may want to follow the other piece of advice I gave which is writing a query specifically for the report then changing the query's sql to what you need and opening the report.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  11. #11
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    I am using Access 2000 (so fail). it seems i will have to go back on ur advice about the query. i will need some help though.

    i already have the query formulated with the things i want in the report and it is running fine. where u say i will have to change the query's sql - i don't quite follow (not very good with sql anyway).

    if i have the query, and the report based on the query, and a button on the form to open the report based on the PatientID in the form...

    can u show me an example of the sql i would need to put in the query?
    "The extreme always make an impression." - Jeff Hardy

  12. #12
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You change it by either adding or altering the where clause. Keep your existing SQL and update the where with something like
    WHERE ([PatientID]=7) or whatever value you need.

    If you need help in updating your query's SQL programatically, let me know if you're using DAO or ADO. (If you're in Access2K, you should be using ADO, despite what Izy might use )
    All code ADO/ADOX unless otherwise specified.
    Mike.

  13. #13
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    I need some more help. like i said - complete lost at sql programming. i did however manage to change to sql view after much debating.

    the sql window shows alot of code... where u said

    'WHERE ([PatientID]=7) or whatever value you need.'

    that will be kind of difficult because how i want the report to work is that there are other records in the form, so when the user goes to the next record, he or she will be able to view that patient's bill.
    "The extreme always make an impression." - Jeff Hardy

  14. #14
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I also wrote two key things that I'm not confident you picked up on.

    1. We're going to programatically change your query's SQL. This means you don't need the SQL View window open unless you need to trace an error, and 2, the SQL will be reprogrammed for a different user.

    2. I asked you to specify if you use ADO or DAO, or do you do any VBA programming at all? If you don't, you should learn, but I can still help too.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  15. #15
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    sorry if i may appear slow at this, its just that i never had the use for sql programming... usually the stuff i did was straight forward and i didn't have to worry about those kind of things. i do know 'some' vba coding so i can't be that bad.

    next off i'm using ADO.
    "The extreme always make an impression." - Jeff Hardy

Posting Permissions

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