Replies

The problem with opening recordsets on parameter queries is that you have to explicitly specify the parameter values for the query and then execute it.

I’d be tempted to change your code to something like

Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

Dim myStr as string

On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database

mystr = “TRANSFORM Count(tblVolunteerSchedule.VolDate) AS CountOfVolDate”
mystr = mystr & “SELECT tblVolunteers.LastName, tblVolunteers.FirstName”
mystr = mystr & “FROM tblVolunteers LEFT JOIN tblVolunteerSchedule ON tblVolunteers.VolunteerID = tblVolunteerSchedule.VolunteerID”
mystr = mystr & “WHERE (((tblVolunteerSchedule.VolDate) Between #”
mystr = mystr & format([Start Date],”mm/dd/yyyy”) & “# And #” & format([End Date],”mm/dd/yyyy”) & “#))”
mystr = mystr & “GROUP BY tblVolunteers.LastName, tblVolunteers.FirstName”
mystr = mystr & “PIVOT tblVolunteerSchedule.VolDate;”

Set db = CurrentDb
Set rst = db.OpenRecordset(mystr)

This will generate the query for you. You will have to replace the [StartDate] and [EndDate] with valid fields or values.

otherwise have a look in the help for using the querydef object

eg

Set rst = query1.openrecordset

Andrew,

It’s the second thing I did. Interestingly enough turning the macros back on didn’t work for me under XP.

I can only assume that this relates to the effected security level.

the appWord.WordBasic.DisableAutoMacros 1
set the Macro Security Level to high.
The appWord.WordBasic.DisableAutoMacros 0
had no effect. Obviously we are not meant to be able to reduce security levels via code, again to prevent macro viruses I can understand this.

I can also live with manually resetting the Macro security level after I have completed processing.

Again, thanks for the help.

regards

Stewart

Don’t forget to turn the automacros back on at the end of your macro by using the second line. This will avoid dramas next time you DO want the automacros to fire.

Andrew,

It’s the second thing I did. Interestingly enough turning the macros back on didn’t work for me under XP.

I can only assume that this relates to the effected security level.

the appWord.WordBasic.DisableAutoMacros 1
set the Macro Security Level to high.
The appWord.WordBasic.DisableAutoMacros 0
had no effect. Obviously we are not meant to be able to reduce security levels via code, again to prevent macro viruses I can understand this.

I can also live with manually resetting the Macro security level after I have completed processing.

Again, thanks for the help.

regards

Stewart

Don’t forget to turn the automacros back on at the end of your macro by using the second line. This will avoid dramas next time you DO want the automacros to fire.

I appreciate you taking the time to reply.

I tried the alt-D approach with no success (after your suggestion) and was trying very hard to figure out what my next step was.

The trainable dialog closer isn’t any good to me as I do not know what dialog will appear, or even if one will.

Andrew Lockton somehow managed to dig up a solution from an obscure part of the lounge so I’m ok now.

Thanks.

Stewart

I appreciate you taking the time to reply.

I tried the alt-D approach with no success (after your suggestion) and was trying very hard to figure out what my next step was.

The trainable dialog closer isn’t any good to me as I do not know what dialog will appear, or even if one will.

Andrew Lockton somehow managed to dig up a solution from an obscure part of the lounge so I’m ok now.

Thanks.

Stewart

Andrew,

how on earth did you find that. Yet another bit of Hans magic, delivered by Andrew magic.

I had searched through the lounge but I would have never found that by myself in a million years.

using the code

Set appWord = CreateObject(“Word.Application”)
appWord.WordBasic.DisableAutoMacros 1

solved my problem perfectly.

once again Thank You.

Andrew,

how on earth did you find that. Yet another bit of Hans magic, delivered by Andrew magic.

I had searched through the lounge but I would have never found that by myself in a million years.

using the code

Set appWord = CreateObject(“Word.Application”)
appWord.WordBasic.DisableAutoMacros 1

solved my problem perfectly.

once again Thank You.

you could always build the path in the after update event of the field and assign it to another field on the form for the user to click on.

assuming your surname field is called txtSurname
create a new edit control called txtxSurnameLink and set the is hyperlink property to true.
in the after updae event for txtSurname

do some thing like this
Me.txtSurnameLink = “pathsurname” & Me.txtSurname & “.htm”

in your report you could do the same thing for display.

As far as I know you can’t use a hyperlink in a report except for dipsplay as there is no way of retrieving the click on the link from a report canvas.

you could always build the path in the after update event of the field and assign it to another field on the form for the user to click on.

assuming your surname field is called txtSurname
create a new edit control called txtxSurnameLink and set the is hyperlink property to true.
in the after updae event for txtSurname

do some thing like this
Me.txtSurnameLink = “pathsurname” & Me.txtSurname & “.htm”

in your report you could do the same thing for display.

As far as I know you can’t use a hyperlink in a report except for dipsplay as there is no way of retrieving the click on the link from a report canvas.

Charlotte,

if my comment offended you in any way, you have my sincere appologies. It was a post made out of admiration and amusement.

Stewart

Charlotte,

if my comment offended you in any way, you have my sincere appologies. It was a post made out of admiration and amusement.

Stewart

Mate,

I wasn’t having a go at her, I was admiring her style. She has helped me also.

Stewart

Mate,

I wasn’t having a go at her, I was admiring her style. She has helped me also.

Stewart

Hans,

I’ve used

    With appAccess
        Debug.Print .IsCompiled
        .RunCommand acCmdCompileAndSaveAllModules
        Debug.Print .IsCompiled
    End With

and was surprised that the RunCommand method is applicable to the application object. I checked the help and there it was, looks like I need to have a better look at the object model.

Just as an aside, I got curious at thsi point and started looking for the differences between a DoCMD.RunCommand and an Application.RunCommand. The help provided the following…
“To run the RunCommand action in Visual Basic, use the RunCommand method of the Application object. (This is equivalent to the RunCommand method of the DoCmd object.)”

It was interesting that when I tested with a module that contained a syntax error it didn’t generate an error, it just left the database in an uncompiled state, not that it matters as I can just flag for manual investigation if the IsCompiled property remains false.

Thanks for the tip.

Cheers

Stewart

Hans,

I’ve used

    With appAccess
        Debug.Print .IsCompiled
        .RunCommand acCmdCompileAndSaveAllModules
        Debug.Print .IsCompiled
    End With

and was surprised that the RunCommand method is applicable to the application object. I checked the help and there it was, looks like I need to have a better look at the object model.

Just as an aside, I got curious at thsi point and started looking for the differences between a DoCMD.RunCommand and an Application.RunCommand. The help provided the following…
“To run the RunCommand action in Visual Basic, use the RunCommand method of the Application object. (This is equivalent to the RunCommand method of the DoCmd object.)”

It was interesting that when I tested with a module that contained a syntax error it didn’t generate an error, it just left the database in an uncompiled state, not that it matters as I can just flag for manual investigation if the IsCompiled property remains false.

Thanks for the tip.

Cheers

Stewart