Results 1 to 11 of 11

Thread: report (2003)

  1. #1
    rlbroerman
    Guest

    report (2003)

    i want to print a report of a particular patient and a particular visit of this patient. I want to be prompted for the soc no. and date of visit. Can you help?

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

    Re: report (2003)

    1) Create a report that displays the records for all patients and their visits. You'll probably have to create a query to act as record source.

    2) Create a form with two combo boxes and a command button.
    The first combo box is used to select a patient (SSN) and the second to select a visit date for that patient.
    (See How to synchronize two combo boxes on a form in Access 2002 or in Access 2003 if you want to limit the dates shown in the second combo box to those for the patient selected in the first one.)
    The command button opens the report, specifying a where-condition to select the patient and visit. Code will look like this:

    Private Sub cmdOK_Click()
    Dim strWhere As String
    On Error GoTo ErrHandler

    If IsNull(Me.cboPatient) Then
    MsgBox "Please select a patient", vbExclamation
    Me.cboPatient.SetFocus
    Exit Sub
    End If

    If IsNull(Me.cboVisit) Then
    MsgBox "Please select a visit", vbExclamation
    Me.cboVisit.SetFocus
    Exit Sub
    End If

    strWhere = "SSN = " & Chr(34) & Me.cboPatient & Chr(34) & " AND VisitDate = #" & _
    Format(Me.cboVisit), "mm/dd/yyyy") & "#"
    DoCmd.OpenReport "rptVisits", acViewPreview, , strWhere

    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Substitute the appropriate names.

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

    Re: report (2003)

    Just one thing to add to Hans detailed reply.

    If the Visits table has a unique identifier, such as visitID , then that should be enough to identify the data you want displayed in the report.

    If you put the VisitID as a hidden first column in the second combo box, then the where condition only needs to be:

    strWhere = " [VisitID] = " & Me.cboVisit
    Regards
    John



  4. #4
    rlbroerman
    Guest

    Re: report (2003)

    Dear silver,
    you suggested "& Me cbo" in your solution. I have seen this before but don't know what it means and can find it in the reference books. can you explain it to me, please?
    R broerman

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

    Re: report (2003)

    In the code module belonging to a form or report, the keyword "Me" refers to the form or report itself. One advantage of using it is that when you type <code>Me.</code> (Me followed by a period/point/dot), IntelliSense will pop up a list of all available properties and methods of the form. Type the first letters of the control name, e.g. cboV, until the correct name is selected in the list, then press Tab. The entire name cboVisit will be entered into your module, with the correct spelling.

    Note: in the name cboVisit, the prefix cbo stands for "combo box". This is not obligatory, it's just a convention used by many designers to give each databse object, each control name on a form or report, and each variable name in VBA code a three letter prefix that indicates the type of the object, control or variable. This makes code easier to read, because the prefixes make it clear what kind of item a name represents: frmTest is a form, txtLastName is a text box, and lngID is a long integer.

  6. #6
    rlbroerman
    Guest

    Re: report (2003)

    Thanks for your code.
    I built a form "Print Visit Report" based on your suggestions. But when I click the print record buttom nothing happens. Got any suggestions?
    I am sure there is just something I don't understand, again.
    Thanks so much for your help.
    Rick Broerman
    Attached Files Attached Files

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

    Re: report (2003)

    Here is an attached version that works.

    There were a number of things wrong:

    * You used the code that Hans gave you, but your controls did not have the names that his code was looking for. You have combo0 where his code was looking for cbosocSecNo etc. I changed the code, and left the controls names as they were, but it might have been better to do the opposite.

    * The first combo should onmly query table patients as yoiu only want each social Security Number once.

    *The second combo gets dates, but only those for the social Secuity number already selected, so it uses the first combo as a parameter.

    * your visits table needs a joint key on both date and SocSecNo otherwise you could not enter two records for the same day. As it is you can't enter two visits for the same person on the same day. Might that be a problem? If so add anautonumber field and use that as key.
    Attached Files Attached Files
    Regards
    John



  8. #8
    rlbroerman
    Guest

    Re: report (2003)

    John,
    It has been a while since I worked with your suggestions and they don't seem to work now. What you sent before seemed to work great but I can't get it to work now. Please, send your solution again ifyou still have it. I would be very grateful. Thanks again.
    Rick Broerman

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

    Re: report (2003)

    My previous solution is still attached to my last post. You can get it from there.
    Regards
    John



  10. #10
    rlbroerman
    Guest

    Re: report (2003)

    John,
    I have tried your old post a couple of times but I get an error. When I try to open a form "frm Visit 2 A 3-20-07" it is looking for "qry Patients 2-11-07" which doesn't exist. But I did change your post at all. Help, please.

    Rick Broerman

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

    Re: report (2003)

    I did not look at that form.

    Tthe form that does the job you asked for is "Print Visit Report"
    Regards
    John



Posting Permissions

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