REPOST: SendObject error (FAO: Basil)

[Guest]

Basil

Is there any chance that I could see an example of your work so that I might be able to find a way around this problem? I think that might help a lot

Also, would it be the case that you decided to use this method so that you could also tailor the report to the user's needs

Cheers

Jedste

----- Basil wrote: ----

I'm sorry I couldn't help more... I'll leave your queries to the MVPs

To me it seems quite a complex method - I do something very very similar simply by setting the report's controls, record source - even the layout of the whole report on the load event (some calcultaions and variables are stored on the form's control event). In this way you only need the docmd.sendobject command and the rest takes care of itself. Good luck Jedster

Ba

----- Jedster wrote: ----

Hi Basil

Thanks for your response

I was advised that if I wanted to apply criteria to a report (outside of a standard query as in this case) and then mail it on as an attachment, I would first need to specify and run the report with the OpenReport command, and then use the SendObject command whilst omitting reference to the object. Believe it or not, this had actually been working fine for quite some time

Jedste

----- Basil wrote: ----

Hi Jedster

Good to see you've come some way in this..

The first thing I noticed was that there doesn't seem to be any report referenced to be attached. The code I am talking about is

DoCmd.SendObject acSendReport, , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru

Wouldn't you want to include the report name in this? i.e.

DoCmd.SendObject acSendReport, REPORTNAME , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru

I have no idea what was going on with Excel et al. I would have thought that your code would have left an open email (with the relevant recipients and body text) with no attachements

Ba

----- Jedster wrote: ----

I am having problems with the following piece of code. It has been written to run a report from details contained on a dialogue form and then issue the report by email as an RTF attachment. This has been working fine for some time, but, when I now run it, it creates an Excel file instead containing nothing but code

Does anyone know how this problem might have been caused and what I can do to correct it

Dim ReportName, WhereCondition As String,
StandardCondition As String, SubSectCondition As String,
DateCondition As String, BodyMessage As String,
CaptionMessage As String, StandardCaption As String,
AreaCaption As String, DateCaption As String,
Dt As New DateInf

Public Sub Form_Load(
ResetFor
End Su

Public Sub ResetForm(
'Normalise form settings and populate with dates of previous wee
Me.TabCtl7.Visible = Fals
Me.InsideHeight = 240
Me.tglAddCriteria.Caption = "Set additional criteria >>>
Me.cbxAllDates = Fals
Me.StartDate = Dt.FirstOfWeek -
Me.EndDate = Dt.LastOfWeek - 7
Me.StartDate.Visible = True
Me.EndDate.Visible = True
ReportName = "rptDiscontinuedCatalogue"
StandardCondition = "SC<99 AND St='D'"
DateCondition = " AND DiscDate Between #" _
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" _
& Format$(Me.EndDate, "mm/dd/yyyy") & "#"
StandardCaption = "Preparing discontinued report for "
AreaCaption = "all areas, "
DateCaption = "dates as specified"
BodyMessage = "Karen," & Chr(10) & Chr(10) _
& "Please find attached the latest discontinued product update."
End Sub

Private Sub cmdWeeklyReport_Click()
'Mail discontinued report using weekly criteria
Dim Msg, Style, Title, Response
Msg = "Are you sure you want to mail the weekly report?"
Style = vbYesNo + vbExclamation + vbApplicationModal
Title = "Mail Weekly Discontinued Report"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Exit Sub
End If
ResetForm
If Val(Me.cbxAllDates) = -1 Then
WhereCondition = "Department<>'94'" & _
"AND SC<99 AND St='D'"
Else
WhereCondition = "Department<>'94'" _
& "AND SC<99 AND St='D' AND DiscDate Between #" _
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" _
& Format$(Me.EndDate, "mm/dd/yyyy") & "#"
End If
Me.Caption = "Preparing report (electrical excluded)..."
DoCmd.OpenReport _
ReportName:=ReportName, _
View:=acViewPreview, _
WhereCondition:=WhereCondition
Me.Caption = "Discontinued Report"

DoCmd.SendObject acSendReport, , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True
End Sub

 

[david epsom dot com dot au]

I don't think you have to leave out the name of the report -- I don't
think I used to do so. The problem with leaving out the name of
the report, is that it tries to send the 'active object'. This may
even be why you now have a problem.

(david)

Basil,

Is there any chance that I could see an example of your work so that I

might be able to find a way around this problem? I think that might help a
lot.

Also, would it be the case that you decided to use this method so that you

could also tailor the report to the user's needs?

Cheers,

Jedster

----- Basil wrote: -----

I'm sorry I couldn't help more... I'll leave your queries to the MVPs.

To me it seems quite a complex method - I do something very very

similar simply by setting the report's controls, record source - even the
layout of the whole report on the load event (some calcultaions and
variables are stored on the form's control event). In this way you only
need the docmd.sendobject command and the rest takes care of itself. Good
luck Jedster.

Baz

----- Jedster wrote: -----

Hi Basil,

Thanks for your response.

I was advised that if I wanted to apply criteria to a report

(outside of a standard query as in this case) and then mail it on as an
attachment, I would first need to specify and run the report with the
OpenReport command, and then use the SendObject command whilst omitting
reference to the object. Believe it or not, this had actually been working
fine for quite some time!

Jedster

----- Basil wrote: -----

Hi Jedster,

Good to see you've come some way in this...

The first thing I noticed was that there doesn't seem to be

any report referenced to be attached. The code I am talking about is:

DoCmd.SendObject acSendReport, , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True

Wouldn't you want to include the report name in this? i.e.:

DoCmd.SendObject acSendReport, REPORTNAME , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True

I have no idea what was going on with Excel et al. I would

have thought that your code would have left an open email (with the relevant
recipients and body text) with no attachements.

Baz

----- Jedster wrote: -----

I am having problems with the following piece of code.

It has been written to run a report from details contained on a dialogue
form and then issue the report by email as an RTF attachment. This has been
working fine for some time, but, when I now run it, it creates an Excel file
instead containing nothing but code.

Does anyone know how this problem might have been

caused and what I can do to correct it?

 

[Guest]

Hi Jedster

Sorry I didn't reply sooner, I've been off. I'll explain how my system works (I've given examples of the simplest form/report hoping it will be easier to pick out the method) - hopefully some of the methodolgy will be transferable..

The code I have sits in the form and on the report

Form
There are various comboboxes, option groups etc on the form as a way for the user to set what they wish to see on the report. These are fed into publicly defined variables (which actually sit in a seperate module in the module tab) - this report has only 2 (area/monitor). There are then 'action' controls. This is a simplified version of the 'email' control events

Private Sub cmdEmail_Click(

Dim datarecipient As Strin

Select Case framare

Case
area = "Referrals

Case
area = "Clinics

Case
area = "Booked List

End Selec

monitor = CboMonito

datarecipient = InputBox("Please enter the username of the addressee", "Recipient HHNT address") & "@hhnt.org

If Left(datarecipient, 1) = "@" Then Exit Sub ' This will be the case if either they press cancel or leave the input blan

LoadUser 'This is another sub that will get the current user full name etc from their log in - I'll leave the code ou

DoCmd.SendObject acSendReport, "Data Quality Monitoring", acFormatRTF, datarecipient, , , area & ": " & monitor,
"This data monitoring report has been sent by " & UserFullname & vbCrLf & vbCrLf & "Please contact " & UserForename
& " by email, or on extension " & UserExt & " if you have queries on how to use the report.", Fals

End Su

So, from the form we have set the area and monitor variables, and then got the recipient email address (you can do this from another field if you want, or get the address from a table - I have another form which does this and loops through 45 addresses creating and sending a personalised report for each based on variables set on the form and in other fields of the record in the recipient table)

When it gets to the sendobject bit, it simply sends the report - however, since the report needs to be opened for it to be sent, by having all the code to personalise the report on the open event of the report - this will automatically be actioned when sending. I've included a sample of the code on the open event of the report

Report

Private Sub Report_Open(Cancel As Integer

dim sqlstring as string

lblmonitor.Caption = area & " - " & monito

Select Case are

Case "Referrals

Select Case monito

Case "Unknown referral source
sqlstring = "SELECT Referrals.[Referral Key], Referrals.[Hospital number], Referrals.Forename, Referrals.Surname, Referrals.[Referral source], Referrals.Username, Referrals.[Date referral received] "
& "FROM Referrals LEFT JOIN [Referring Trusts] ON Referrals.[Referral source rsno] = [Referring Trusts].[ICHIS RSNO] "
& "WHERE (((Referrals.[Date referral received]) Between [Forms]![Data Quality Monitoring]![txtstdate] And [Forms]![Data Quality Monitoring]![txtendate]) AND ((Referrals.[Referral code])<>'G') AND (([Referring Trusts].[ICHIS Name]) Is Null));
txt4.ControlSource = "Referral source
txt5.ControlSource = "Username
txt6.ControlSource = "Date referral received
txt7.ControlSource = "=''
lbl4.Caption = "Referral source
lbl5.Caption = "Entry clerk username
lbl6.Caption = "Date received
lbl7.Caption = "
Me.GroupLevel(0).ControlSource = "Hospital number
'There are other cases for monitor under the 'Referral area - but they are simila
End Selec

Case "Other Data

Select Case monito

Case "Duplicated patient registration
sqlstring = "SELECT Min(Referrals.[Date referral received]) AS [MinOfDate referral received], Min(Referrals.[Hospital number]) AS [MinOfHospital number], Max(Referrals.[Hospital number]) AS [MaxOfHospital number], Referrals.Forename, Referrals.Surname, Referrals.Sex, Referrals.[Date of birth] "
& "FROM Referrals GROUP BY Referrals.Forename, Referrals.Surname, Referrals.Sex, Referrals.[Date of birth] " _
& "HAVING (((IIf(Max([Hospital number])=Min([Hospital number]),0,1))=1));"
txt1.ControlSource = "MinOfDate referral received"
txt1.Format = "dd mmm yy"
txt2.ControlSource = "MinOfHospital number"
txt3.ControlSource = "MaxOfHospital number"
txt4.ControlSource = "Forename"
txt5.ControlSource = "Surname"
txt6.ControlSource = "Sex"
txt7.ControlSource = "Date of birth"
lbl1.Caption = "Initial referral"
lbl2.Caption = "Hospital number 1"
lbl3.Caption = "Hospital number 2"
lbl4.Caption = "Forename"
lbl5.Caption = "Surname"
lbl6.Caption = "Sex"
lbl7.Caption = "Date of birth"
Me.GroupLevel(0).ControlSource = "MinOfHospital number"
txtdate.Visible = False

Case Else 'This will never happen since it is dealt with through the form, but you should always include error handling
noreport 'This is my error handling routine
Cancel = True

End Select

Case Else 'There are other areas etc, but I've left them out

End Select

DoCmd.Maximize
Me.RecordSource = sqlstring

End Sub

What the above code does is initially set the title according to the variables (by setting the label's caption), it then goes through select cases to look at the area and monitor and subsequently sets a variable (sqlstring) - which wil be used for the recordsource I want for the form, and then sets all the properties I want for the report's objects (control sources/formats/grouping levels etc). I have not included the error trapping stuff or the no_data handler. Both of these must be considered (you don't want to be sending out blank reports do you?).

I hope this method might give you a helping hand or ideas...

Good luck,

Basil


----- Jedster wrote: -----

Basil,

Is there any chance that I could see an example of your work so that I might be able to find a way around this problem? I think that might help a lot.

Also, would it be the case that you decided to use this method so that you could also tailor the report to the user's needs?

Cheers,

Jedster

----- Basil wrote: -----

I'm sorry I couldn't help more... I'll leave your queries to the MVPs.

To me it seems quite a complex method - I do something very very similar simply by setting the report's controls, record source - even the layout of the whole report on the load event (some calcultaions and variables are stored on the form's control event). In this way you only need the docmd.sendobject command and the rest takes care of itself. Good luck Jedster.

Baz

----- Jedster wrote: -----

Hi Basil,

Thanks for your response.

I was advised that if I wanted to apply criteria to a report (outside of a standard query as in this case) and then mail it on as an attachment, I would first need to specify and run the report with the OpenReport command, and then use the SendObject command whilst omitting reference to the object. Believe it or not, this had actually been working fine for quite some time!

Jedster

----- Basil wrote: -----

Hi Jedster,

Good to see you've come some way in this...

The first thing I noticed was that there doesn't seem to be any report referenced to be attached. The code I am talking about is:

DoCmd.SendObject acSendReport, , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True

Wouldn't you want to include the report name in this? i.e.:

DoCmd.SendObject acSendReport, REPORTNAME , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True

I have no idea what was going on with Excel et al. I would have thought that your code would have left an open email (with the relevant recipients and body text) with no attachements.

Baz

 

[Guest]

I never noticed how it might be pronounced! he he

Good luck with all your work - I hope I haven't confused you. Reading through what I wrote, I did leave a fair amount of blanks (and included a fair amount of waffle!) - to fill a bit in, the variables are strings, 'fram' refers to an option group and 'cbo' refers to a combobox on the form

Hope your grandad's op goes ok - hip replacements are very simple and have very little risk attached. I actually work in a specialist NHS orthopaedic hospital in West London (although I am quitting in 2 weeks! I never want to see VBA again! - unemployment beckons with the near impossible task of finding an analytical job in an airline company)

Ba

----- Jedster wrote: ----

Hi Baz (I guess if the name 'Bazster' were to be adopted, it would clearly need to be pronounced with considerable care!!)

Thanks for your tremendous help with this - I'm sure I'll be able to get some great ideas from your code when I have a more thorough look through it. At least I now know who to blame when granddad's referral for his hip replacement doesn't come through!

Cheers, again

Best regards

Jedste

----- Basil wrote: ----

Hi Jedster

Sorry I didn't reply sooner, I've been off. I'll explain how my system works (I've given examples of the simplest form/report hoping it will be easier to pick out the method) - hopefully some of the methodolgy will be transferable..

The code I have sits in the form and on the report

Form
There are various comboboxes, option groups etc on the form as a way for the user to set what they wish to see on the report. These are fed into publicly defined variables (which actually sit in a seperate module in the module tab) - this report has only 2 (area/monitor). There are then 'action' controls. This is a simplified version of the 'email' control events

Private Sub cmdEmail_Click(

Dim datarecipient As Strin

Select Case framare

Case
area = "Referrals

Case
area = "Clinics

Case
area = "Booked List

End Selec

monitor = CboMonito

datarecipient = InputBox("Please enter the username of the addressee", "Recipient HHNT address") & "@hhnt.org

If Left(datarecipient, 1) = "@" Then Exit Sub ' This will be the case if either they press cancel or leave the input blan

LoadUser 'This is another sub that will get the current user full name etc from their log in - I'll leave the code ou

DoCmd.SendObject acSendReport, "Data Quality Monitoring", acFormatRTF, datarecipient, , , area & ": " & monitor,
"This data monitoring report has been sent by " & UserFullname & vbCrLf & vbCrLf & "Please contact " & UserForename
& " by email, or on extension " & UserExt & " if you have queries on how to use the report.", Fals

End Su

So, from the form we have set the area and monitor variables, and then got the recipient email address (you can do this from another field if you want, or get the address from a table - I have another form which does this and loops through 45 addresses creating and sending a personalised report for each based on variables set on the form and in other fields of the record in the recipient table)

When it gets to the sendobject bit, it simply sends the report - however, since the report needs to be opened for it to be sent, by having all the code to personalise the report on the open event of the report - this will automatically be actioned when sending. I've included a sample of the code on the open event of the report

Report

Private Sub Report_Open(Cancel As Integer)

dim sqlstring as string

lblmonitor.Caption = area & " - " & monitor

Select Case area

Case "Referrals"

Select Case monitor

Case "Unknown referral source"
sqlstring = "SELECT Referrals.[Referral Key], Referrals.[Hospital number], Referrals.Forename, Referrals.Surname, Referrals.[Referral source], Referrals.Username, Referrals.[Date referral received] " _
& "FROM Referrals LEFT JOIN [Referring Trusts] ON Referrals.[Referral source rsno] = [Referring Trusts].[ICHIS RSNO] " _
& "WHERE (((Referrals.[Date referral received]) Between [Forms]![Data Quality Monitoring]![txtstdate] And [Forms]![Data Quality Monitoring]![txtendate]) AND ((Referrals.[Referral code])<>'G') AND (([Referring Trusts].[ICHIS Name]) Is Null));"
txt4.ControlSource = "Referral source"
txt5.ControlSource = "Username"
txt6.ControlSource = "Date referral received"
txt7.ControlSource = "=''"
lbl4.Caption = "Referral source"
lbl5.Caption = "Entry clerk username"
lbl6.Caption = "Date received"
lbl7.Caption = ""
Me.GroupLevel(0).ControlSource = "Hospital number"
'There are other cases for monitor under the 'Referral area - but they are similar
End Select

Case "Other Data"

Select Case monitor

Case "Duplicated patient registration"
sqlstring = "SELECT Min(Referrals.[Date referral received]) AS [MinOfDate referral received], Min(Referrals.[Hospital number]) AS [MinOfHospital number], Max(Referrals.[Hospital number]) AS [MaxOfHospital number], Referrals.Forename, Referrals.Surname, Referrals.Sex, Referrals.[Date of birth] " _
& "FROM Referrals GROUP BY Referrals.Forename, Referrals.Surname, Referrals.Sex, Referrals.[Date of birth] " _
& "HAVING (((IIf(Max([Hospital number])=Min([Hospital number]),0,1))=1));"
txt1.ControlSource = "MinOfDate referral received"
txt1.Format = "dd mmm yy"
txt2.ControlSource = "MinOfHospital number"
txt3.ControlSource = "MaxOfHospital number"
txt4.ControlSource = "Forename"
txt5.ControlSource = "Surname"
txt6.ControlSource = "Sex"
txt7.ControlSource = "Date of birth"
lbl1.Caption = "Initial referral"
lbl2.Caption = "Hospital number 1"
lbl3.Caption = "Hospital number 2"
lbl4.Caption = "Forename"
lbl5.Caption = "Surname"
lbl6.Caption = "Sex"
lbl7.Caption = "Date of birth"
Me.GroupLevel(0).ControlSource = "MinOfHospital number"
txtdate.Visible = False

Case Else 'This will never happen since it is dealt with through the form, but you should always include error handling
noreport 'This is my error handling routine
Cancel = True

End Select

Case Else 'There are other areas etc, but I've left them out

End Select

DoCmd.Maximize
Me.RecordSource = sqlstring

End Sub

What the above code does is initially set the title according to the variables (by setting the label's caption), it then goes through select cases to look at the area and monitor and subsequently sets a variable (sqlstring) - which wil be used for the recordsource I want for the form, and then sets all the properties I want for the report's objects (control sources/formats/grouping levels etc). I have not included the error trapping stuff or the no_data handler. Both of these must be considered (you don't want to be sending out blank reports do you?).

I hope this method might give you a helping hand or ideas...

Good luck,

Basil

 

[Guest]

Cheers, Baz. Good luck with the job-hunting!!

Jedster

I never noticed how it might be pronounced! he he.

Good luck with all your work - I hope I haven't confused you. Reading through what I wrote, I did leave a fair amount of blanks (and included a fair amount of waffle!) - to fill a bit in, the variables are strings, 'fram' refers to an option group and 'cbo' refers to a combobox on the form.

Hope your grandad's op goes ok - hip replacements are very simple and have very little risk attached. I actually work in a specialist NHS orthopaedic hospital in West London (although I am quitting in 2 weeks! I never want to see VBA again! - unemployment beckons with the near impossible task of finding an analytical job in an airline company).

Baz

----- Jedster wrote: -----

Hi Baz (I guess if the name 'Bazster' were to be adopted, it would clearly need to be pronounced with considerable care!!),

Thanks for your tremendous help with this - I'm sure I'll be able to get some great ideas from your code when I have a more thorough look through it. At least I now know who to blame when granddad's referral for his hip replacement doesn't come through!!

Cheers, again.

Best regards,

Jedster

----- Basil wrote: -----

Hi Jedster,

Sorry I didn't reply sooner, I've been off. I'll explain how my system works (I've given examples of the simplest form/report hoping it will be easier to pick out the method) - hopefully some of the methodolgy will be transferable...

The code I have sits in the form and on the report:

Form:
There are various comboboxes, option groups etc on the form as a way for the user to set what they wish to see on the report. These are fed into publicly defined variables (which actually sit in a seperate module in the module tab) - this report has only 2 (area/monitor). There are then 'action' controls. This is a simplified version of the 'email' control events:

Private Sub cmdEmail_Click()

Dim datarecipient As String

Select Case framarea

Case 1
area = "Referrals"

Case 2
area = "Clinics"

Case 3
area = "Booked List"

End Select

monitor = CboMonitor

datarecipient = InputBox("Please enter the username of the addressee", "Recipient HHNT address") & "@hhnt.org"

If Left(datarecipient, 1) = "@" Then Exit Sub ' This will be the case if either they press cancel or leave the input blank

LoadUser 'This is another sub that will get the current user full name etc from their log in - I'll leave the code out

DoCmd.SendObject acSendReport, "Data Quality Monitoring", acFormatRTF, datarecipient, , , area & ": " & monitor, _
"This data monitoring report has been sent by " & UserFullname & vbCrLf & vbCrLf & "Please contact " & UserForename _
& " by email, or on extension " & UserExt & " if you have queries on how to use the report.", False

End Sub

So, from the form we have set the area and monitor variables, and then got the recipient email address (you can do this from another field if you want, or get the address from a table - I have another form which does this and loops through 45 addresses creating and sending a personalised report for each based on variables set on the form and in other fields of the record in the recipient table).

When it gets to the sendobject bit, it simply sends the report - however, since the report needs to be opened for it to be sent, by having all the code to personalise the report on the open event of the report - this will automatically be actioned when sending. I've included a sample of the code on the open event of the report.

Report:

Private Sub Report_Open(Cancel As Integer)

dim sqlstring as string

lblmonitor.Caption = area & " - " & monitor

Select Case area

Case "Referrals"

Select Case monitor

Case "Unknown referral source"
sqlstring = "SELECT Referrals.[Referral Key], Referrals.[Hospital number], Referrals.Forename, Referrals.Surname, Referrals.[Referral source], Referrals.Username, Referrals.[Date referral received] " _
& "FROM Referrals LEFT JOIN [Referring Trusts] ON Referrals.[Referral source rsno] = [Referring Trusts].[ICHIS RSNO] " _
& "WHERE (((Referrals.[Date referral received]) Between [Forms]![Data Quality Monitoring]![txtstdate] And [Forms]![Data Quality Monitoring]![txtendate]) AND ((Referrals.[Referral code])<>'G') AND (([Referring Trusts].[ICHIS Name]) Is Null));"
txt4.ControlSource = "Referral source"
txt5.ControlSource = "Username"
txt6.ControlSource = "Date referral received"
txt7.ControlSource = "=''"
lbl4.Caption = "Referral source"
lbl5.Caption = "Entry clerk username"
lbl6.Caption = "Date received"
lbl7.Caption = ""
Me.GroupLevel(0).ControlSource = "Hospital number"
'There are other cases for monitor under the 'Referral area - but they are similar
End Select

Case "Other Data"

Select Case monitor

Case "Duplicated patient registration"
sqlstring = "SELECT Min(Referrals.[Date referral received]) AS [MinOfDate referral received], Min(Referrals.[Hospital number]) AS [MinOfHospital number], Max(Referrals.[Hospital number]) AS [MaxOfHospital number], Referrals.Forename, Referrals.Surname, Referrals.Sex, Referrals.[Date of birth] " _
& "FROM Referrals GROUP BY Referrals.Forename, Referrals.Surname, Referrals.Sex, Referrals.[Date of birth] " _
& "HAVING (((IIf(Max([Hospital number])=Min([Hospital number]),0,1))=1));"
txt1.ControlSource = "MinOfDate referral received"
txt1.Format = "dd mmm yy"
txt2.ControlSource = "MinOfHospital number"
txt3.ControlSource = "MaxOfHospital number"
txt4.ControlSource = "Forename"
txt5.ControlSource = "Surname"
txt6.ControlSource = "Sex"
txt7.ControlSource = "Date of birth"
lbl1.Caption = "Initial referral"
lbl2.Caption = "Hospital number 1"
lbl3.Caption = "Hospital number 2"
lbl4.Caption = "Forename"
lbl5.Caption = "Surname"
lbl6.Caption = "Sex"
lbl7.Caption = "Date of birth"
Me.GroupLevel(0).ControlSource = "MinOfHospital number"
txtdate.Visible = False

Case Else 'This will never happen since it is dealt with through the form, but you should always include error handling
noreport 'This is my error handling routine
Cancel = True

End Select

Case Else 'There are other areas etc, but I've left them out

End Select

DoCmd.Maximize
Me.RecordSource = sqlstring

End Sub

What the above code does is initially set the title according to the variables (by setting the label's caption), it then goes through select cases to look at the area and monitor and subsequently sets a variable (sqlstring) - which wil be used for the recordsource I want for the form, and then sets all the properties I want for the report's objects (control sources/formats/grouping levels etc). I have not included the error trapping stuff or the no_data handler. Both of these must be considered (you don't want to be sending out blank reports do you?).

I hope this method might give you a helping hand or ideas...

Good luck,

Basil