Print Selected Record via Form 2

[awl]

I have the main “frmList of Individuals” which has a subform “frmAppointments” which consists of several scheduled appointments. The Primary Key in the “frmList of Individuals” is [ClientID#] and the two Primary Keys in “frmAppointments” are [ClientID#] and [ApptID#]. I have a Command Button on the “frmAppointments” which the Code (needing some work) is listed below to print the “frmAppointment Letter”, which consists of the individual, department, date and time of appointment, etc.

Private Sub cmdPrint_Current_Record_Click()
On Error GoTo Err_cmdPrint_Current_Record_Click

Dim stDocName As String

stDocName = "macPrint frmAppointment Letter"
DoCmd.RunMacro stDocName

Exit_cmdPrint_Current_Record_Click:
Exit Sub

Err_cmdPrint_Current_Record_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Current_Record_Click

End Sub

What I want is when someone schedules an appointment, they enter the “frmList of Individuals”, enter the scheduled date/time, then Press the Print Command Button and the “frmAppointment Letter” is generated, and the two “frmList of Individuals” and “frmAppointment Letter” are then closed.

In the “macPrint frmAppointment Letter” the ACTIONS: a) RunCommand: SelectRecord; b) OpenForm: Form Name: frmApointment Letter, View: Blank; Filter Name: Blank; Where Condition: Blank; c) Close: frmList of Individuals; d) Close: frmAppointment Letter.

When the button is pressed, the two forms get closed, however, nothing prints. Hopefully with the information provided above, how can I get just the selected appointment [ApptID#] with the matching [ClientID#] to print the “frmAppointment Letter” and close the two forms. I'm sure there is something missing in the Where Condition. Suggestions would be grateful.

 

[OnTheFly]

I am not going to be able to help you with the macro as I rarely use them. I will however try to help you accomplish this task.

First, I would suggest you create a report that has the information you want to print formated in it. I think you will be much happier with the outcome and look of the printout.

Bind the report to the same record source as your frmAppointmentLetter making sure the ApptID# is part of the record source.

Then if you put this in the On Click event of the command button you should get the results you are looking for.

Dim lngApptID as Long

lngApptID=Me!ApptID#
docmd.OpenReport "rptAppointmentLetter",acViewNormal,,"ApptID#=" & lngApptID
DoCmd.Close acForm, "frmListofIndivifuals",acSaveNo

Hope this helps.

OnTheFly

 

[awl]

Thanks for the reply. I created the “rptAppointment Letter”. The record source “qryList of Individuals w/Appointment Dates” has the [ApptID#]. I placed the coding below to the OnClick event of the Command Button of the “frmList of Individuals”. However, when I press the CmdButton, the Microsoft Visual Basic error: Run-time error 2465: “Physical Exams – PHA Appointments can’t find the field ‘ApptID’ referred to in your expression.” When I ask for it to Debug, it highlights the 3rd line: IngApptID = Me!ApptID#. I then inserted the # sign in the expression below where the 3 ApptID was listed and still I can’t get it to work. Your help would be appreciated!

Private Sub cmdPrint_Current_Record_Click()
Dim lngApptID As Long

lngApptID = Me!ApptID#
DoCmd.OpenReport "rptAppointment Letter", acViewNormal, , "ApptID#=" & lngApptID
DoCmd.Close acForm, "frmList of Individuals", acSaveNo

Exit_cmdPrint_Current_Record_Click:
Exit Sub

Err_cmdPrint_Current_Record_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Current_Record_Click

End Sub

 

[PHV]

Replace all of occurrences of ApptID# by [ApptID#]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OnTheFly]

My first question would be "is ApptID#" the name of the field that stores the Appointment ID?" I assumed this from your post.

If it is then try Me![ApptID#] to see if that helps. Using special characters like #, @, & and such in field names is something you will want to avoid.

If it is not the actual field name then change it to the actual field name in the following lines of code

lngApptID=Me!ActualFieldName
DoCmd.OpenReport "rptAppointment Letter", acViewNormal,,"ActualFieldName=" & lngApptID

Hope this helps.

OnTheFly

 

[awl]

Yes, OnTheFly, [ApptID#] was the name of the field being used. Per your suggestion, I delected all "#" symbols and Yes, the coding works just fine.

Yes, PHV, before deleting the # symbols, I closed each occurrence with [ ] and that also worked. Thanks both of you for your professional responses.