Crosstab query error 3104

[ridders]

Hi

I am using the following crosstab query as part of code to create an Excel
spreadsheet from Access:

TRANSFORM Count(PupilGrades.Att) AS CountOfAtt SELECT Subjects.SubjectID
FROM (Subjects INNER JOIN (PupilGrades INNER JOIN ClassModules ON
PupilGrades.ModuleID = ClassModules.ModuleID) ON Subjects.SubjectID =
ClassModules.SubjectID) INNER JOIN Classes ON ClassModules.ClassID =
Classes.ClassID WHERE (((PupilGrades.Att) <> '') AND
((Classes.Year)=GetYearGroup()) AND ((PupilGrades.SessionID)=GetSession()))
GROUP BY Subjects.SubjectID ORDER BY Subjects.SubjectID PIVOT PupilGrades.Att
The query works fine on its own but when used as part of the code module, I
get Access error 3104
“Can't specify fixed column heading N/A in a crosstab query more than onceâ€

I have tried variations on the WHERE items but with similar results i.e. the
query works on its own but not as part of the form code.

However other similar crosstab queries work fine within this code.
For example this works perfectly:

TRANSFORM Count(PupilGrades.Eff) AS CountOfEff SELECT Classes.SubjectID FROM
(Classes INNER JOIN ClassModules ON Classes.ClassID = ClassModules.ClassID)
INNER JOIN (Grades INNER JOIN PupilGrades ON Grades.Code = PupilGrades.Eff)
ON ClassModules.ModuleID = PupilGrades.ModuleID WHERE
(((Classes.Year)=GetYearGroup()) AND ((PupilGrades.SessionID)=GetSession())
AND ((Grades.KeyStage)=GetKeyStage()) AND ((Grades.Type)='E')) GROUP BY
Classes.SubjectID ORDER BY Classes.SubjectID PIVOT PupilGrades.Eff

Any ideas on why the first crosstab query fails & how to fix it would be
much appreciated

 

[Gary Walter]

I am using the following crosstab query as part of code to create an Excel
spreadsheet from Access:

TRANSFORM Count(PupilGrades.Att) AS CountOfAtt SELECT Subjects.SubjectID
FROM (Subjects INNER JOIN (PupilGrades INNER JOIN ClassModules ON
PupilGrades.ModuleID = ClassModules.ModuleID) ON Subjects.SubjectID =
ClassModules.SubjectID) INNER JOIN Classes ON ClassModules.ClassID =
Classes.ClassID WHERE (((PupilGrades.Att) <> '') AND
((Classes.Year)=GetYearGroup()) AND
((PupilGrades.SessionID)=GetSession()))
GROUP BY Subjects.SubjectID ORDER BY Subjects.SubjectID PIVOT
PupilGrades.Att
The query works fine on its own but when used as part of the code module,
I
get Access error 3104
"Can't specify fixed column heading N/A in a crosstab query more than
once"

I have tried variations on the WHERE items but with similar results i.e.
the
query works on its own but not as part of the form code.

<snip>

Hi ridders,

No immediate insight, only questions -- sorry...

Not sure what you mean by "used as part of the code module..."

you have this saved query above that works,
but does not work when you...

1) write out the SQL in code

2) reference the saved query in code

might help if you provide code?

It looks like other query that works does not
apply a WHERE filter on the PIVOT field.

Was one variation?
(if eliminate Null/ZLS in totals part of query,
then no need to reuse PIVOT field
in TRANSFORM)

TRANSFORM Count(*) AS CountOfAtt
SELECT Subjects.SubjectID
FROM
(Subjects
INNER JOIN
(PupilGrades
INNER JOIN
ClassModules
ON
PupilGrades.ModuleID = ClassModules.ModuleID)
ON
Subjects.SubjectID = ClassModules.SubjectID)
INNER JOIN
Classes
ON
ClassModules.ClassID = Classes.ClassID
WHERE
(
(Len(Trim((PupilGrades.Att) & '')) > 0)
AND
((Classes.Year)=GetYearGroup())
AND
((PupilGrades.SessionID)=GetSession())
)
GROUP BY
Subjects.SubjectID
ORDER BY
Subjects.SubjectID
PIVOT
PupilGrades.Att;

Where does 'N/A' come from?

Are some Att values 'N/A'?

Is it a PIVOT column in the saved query
that works?

good luck,

gary

 

[ridders]

Hi Gary

Not sure what you mean by "used as part of the code module..."

Sorry - I meant form code

you have this saved query above that works,
but does not work when you...

1) write out the SQL in code
2) reference the saved query in code
Correct

might help if you provide code?

Will do so at end but its very long!

It looks like other query that works does not
apply a WHERE filter on the PIVOT field.

The working queries reference a different field in the table with on
possible repeated headings!

Was one variation?
(if eliminate Null/ZLS in totals part of query,
then no need to reuse PIVOT field
in TRANSFORM)

Sorry - don't understand this comment

TRANSFORM Count(*) AS CountOfAtt
SELECT Subjects.SubjectID
FROM
(Subjects
INNER JOIN
(PupilGrades
INNER JOIN
ClassModules
ON
PupilGrades.ModuleID = ClassModules.ModuleID)
ON
Subjects.SubjectID = ClassModules.SubjectID)
INNER JOIN
Classes
ON
ClassModules.ClassID = Classes.ClassID
WHERE
(
(Len(Trim((PupilGrades.Att) & '')) > 0)
AND
((Classes.Year)=GetYearGroup())
AND
((PupilGrades.SessionID)=GetSession())
)
GROUP BY
Subjects.SubjectID
ORDER BY
Subjects.SubjectID
PIVOT
PupilGrades.Att;

Tried it with same result as my original SQL

Where does 'N/A' come from?

Its one of the possible grade values for PupilGrades.Att field

Are some Att values 'N/A'? 'Yes

Is it a PIVOT column in the saved query
that works? Yes

Here is the complete code for the ExportToExcel sub:
Apologies for the length...!
-------------------------------------------------------
Sub ExportToExcel()

On Error GoTo ExportToExcel_err

Dim strFilename As String
Dim strAlias As String
Dim rst, rst2 As Recordset
Dim ExApp As Object
Dim MyRange As Object
Dim stFilename1, stFilename2 As String
Dim strsql, strSQLx, strSQLLabels As String
Dim intRows, iCount, icount2, iFields As Integer
Dim ColCount1, ColCount2, ColCount3, ModuleCount As Integer
Dim varResults
Dim FldName(50)
ExcelHeader = strHeader & strSession

Select Case strCallForm

Case "IntAtt"

strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'A') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Points DESC, Grades.Order;"


'New SQL works fine as query but causes error 3104 here
strRecordSource = "TRANSFORM Count(PupilGrades.Att) AS CountOfAtt" & _
" SELECT Subjects.SubjectID" & _
" FROM (Subjects INNER JOIN (PupilGrades INNER JOIN ClassModules" & _
" ON PupilGrades.ModuleID = ClassModules.ModuleID) ON
Subjects.SubjectID = ClassModules.SubjectID)" & _
" INNER JOIN Classes ON ClassModules.ClassID = Classes.ClassID" & _
" WHERE (((PupilGrades.Att) <> '') AND
((Classes.Year)=GetYearGroup()) AND ((PupilGrades.SessionID)=GetSession()))"
& _
" GROUP BY Subjects.SubjectID" & _
" ORDER BY Subjects.SubjectID" & _
" PIVOT PupilGrades.Att"

'Alternative SQL by Gary Walter from Access Newsgroup
'also works fine as query but causes error 3104 here
'strRecordSource = "TRANSFORM Count(*) AS CountOfAtt" & _
" SELECT Subjects.SubjectID" & _
" FROM Classes INNER JOIN (Subjects INNER JOIN (PupilGrades" & _
" INNER JOIN ClassModules ON PupilGrades.ModuleID = ClassModules.ModuleID)"
& _
" ON Subjects.SubjectID = ClassModules.SubjectID) ON Classes.ClassID =
ClassModules.ClassID" & _
" WHERE (((Len(Trim(([PupilGrades].[Att]) & '')))>0) AND
((Classes.Year)=GetYearGroup())" & _
" AND ((PupilGrades.SessionID)=GetSession()))" & _
" GROUP BY Subjects.SubjectID" & _
" ORDER BY Subjects.SubjectID" & _
" PIVOT PupilGrades.Att"


'Original SQL causes duplicate grades run as query e.g. N/A & also fails
here with error 3104
'strRecordSource = "TRANSFORM Count(PupilGrades.Att) AS CountOfAtt" & _
" SELECT Subjects.SubjectID" & _
" FROM (Grades INNER JOIN (Subjects INNER JOIN (PupilGrades INNER JOIN
ClassModules" & _
" ON PupilGrades.ModuleID = ClassModules.ModuleID) ON Subjects.SubjectID =
ClassModules.SubjectID)" & _
" ON Grades.Code = PupilGrades.Att) INNER JOIN Classes ON
ClassModules.ClassID = Classes.ClassID" & _
" WHERE (((Classes.Year)=GetYearGroup()) AND
((PupilGrades.SessionID)=GetSession())" & _
" AND ((Grades.KeyStage)=GetKeyStage()) AND ((Grades.Type)='A'))" & _
" GROUP BY Subjects.SubjectID, Grades.KeyStage, Grades.Type" & _
" ORDER BY Subjects.SubjectID" & _
" PIVOT PupilGrades.Att"


Case "IntEff"
strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'E') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Points DESC, Grades.Order;"

'this works fine
strRecordSource = "TRANSFORM Count(PupilGrades.Eff) AS CountOfEff" & _
" SELECT Classes.SubjectID" & _
" FROM (Classes INNER JOIN ClassModules ON Classes.ClassID =
ClassModules.ClassID)" & _
" INNER JOIN (Grades INNER JOIN PupilGrades ON Grades.Code =
PupilGrades.Eff)" & _
" ON ClassModules.ModuleID = PupilGrades.ModuleID" & _
" WHERE (((Classes.Year)=GetYearGroup()) AND
((PupilGrades.SessionID)=GetSession()) AND ((Grades.KeyStage)=GetKeyStage())
AND ((Grades.Type)='E'))" & _
" GROUP BY Classes.SubjectID" & _
" ORDER BY Classes.SubjectID" & _
" PIVOT PupilGrades.Eff"

Case "IntPG"
strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'PG') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Order;"

'This works fine
strRecordSource = "TRANSFORM Count(PupilGrades.PGGrade) AS CountOfPGGrade" & _
" SELECT Classes.SubjectID" & _
" FROM (Classes INNER JOIN ClassModules ON Classes.ClassID =
ClassModules.ClassID)" & _
" INNER JOIN (Grades INNER JOIN PupilGrades ON Grades.Code =
PupilGrades.PGGrade)" & _
" ON ClassModules.ModuleID = PupilGrades.ModuleID" & _
" WHERE (((Classes.Year)=GetYearGroup()) AND
((PupilGrades.SessionID)=GetSession()) AND ((Grades.KeyStage)=GetKeyStage())
AND ((Grades.Type)='PG'))" & _
" GROUP BY Classes.SubjectID" & _
" ORDER BY Classes.SubjectID" & _
" PIVOT PupilGrades.PGGrade"

Case "IntPGStr"
strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'P2') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Order"

'This works fine
strRecordSource = "TRANSFORM Count(PupilGrades.PGStr1) AS CountOfPGStr1" & _
" SELECT Classes.SubjectID" & _
" FROM (Classes INNER JOIN ClassModules ON Classes.ClassID =
ClassModules.ClassID)" & _
" INNER JOIN (Grades INNER JOIN PupilGrades ON Grades.Code =
PupilGrades.PGStr1)" & _
" ON ClassModules.ModuleID = PupilGrades.ModuleID" & _
" WHERE (((Classes.Year)=GetYearGroup()) AND
((PupilGrades.SessionID)=GetSession()) AND ((Grades.KeyStage)=GetKeyStage())
AND ((Grades.Type)='P2'))" & _
" GROUP BY Classes.SubjectID" & _
" ORDER BY Classes.SubjectID" & _
" PIVOT PupilGrades.PGStr1"

Case "EOYAtt"
strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'A') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Points DESC, Grades.Order"

'SQL causes duplicate grades run as query e.g. N/A & also fails here with
error 3104
strRecordSource = "TRANSFORM Count(ClassRecords.Att) AS CountOfAtt" & _
" SELECT Classes.SubjectID" & _
" FROM (Classes INNER JOIN ClassRecords ON Classes.ClassID =
ClassRecords.ClassID)" & _
" INNER JOIN Grades ON ClassRecords.Att = Grades.Code" & _
" WHERE (((Classes.Year) = GetYearGroup()) And ((Classes.ShowOnReport) =
Yes)" & _
" AND ((Grades.KeyStage) = GetKeyStage())" & _
" AND ((Grades.Type) = 'A'))" & _
" GROUP BY Classes.SubjectID" & _
" PIVOT ClassRecords.Att"

Case "EOYEff"
strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'E') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Order"

'This works fine
strRecordSource = "TRANSFORM Count(ClassRecords.Att) AS CountOfEff" & _
" SELECT Classes.SubjectID" & _
" FROM (Classes INNER JOIN ClassRecords ON Classes.ClassID =
ClassRecords.ClassID)" & _
" INNER JOIN Grades ON ClassRecords.Eff = Grades.Code" & _
" WHERE (((Classes.Year) = GetYearGroup()) And ((Classes.ShowOnReport) =
Yes)" & _
" AND ((Grades.KeyStage) = GetKeyStage())" & _
" AND ((Grades.Type) = 'E'))" & _
" GROUP BY Classes.SubjectID" & _
" PIVOT ClassRecords.Eff"

Case "EOYCr"
strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'CR') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Points DESC, Grades.Order"

'SQL causes duplicate grades run as query e.g. N/A & also fails here with
error 3104
strRecordSource = "TRANSFORM Count(ClassRecords.Att) AS CountOfCRGrade" & _
" SELECT Classes.SubjectID" & _
" FROM (Classes INNER JOIN ClassRecords ON Classes.ClassID =
ClassRecords.ClassID)" & _
" INNER JOIN Grades ON ClassRecords.CRGrade = Grades.Code" & _
" WHERE (((Classes.Year) = GetYearGroup()) And ((Classes.ShowOnReport) =
Yes)" & _
" AND ((Grades.KeyStage) = GetKeyStage())" & _
" AND ((Grades.Type) = 'CR'))" & _
" GROUP BY Classes.SubjectID" & _
" PIVOT ClassRecords.CRGrade"

End Select
Debug.Print strRecordSource

'get the column headers for the grades and determine the order - by points
for Att,CRGrade
' and by 'Order' for other grades
Dim VarIn As String
VarIn = ""
Set rst2 = mydb.OpenRecordset(strSQL1)
If Not (rst2.BOF Or rst2.EOF) Then
rst2.MoveFirst
Do Until rst2.EOF
VarIn = VarIn & "'" & rst2!Code & "', "
rst2.MoveNext
Loop
rst2.Close
VarIn = Left(VarIn, Len(VarIn) - 2)
End If

strRecordSource = strRecordSource & " In(" & VarIn & ")"

'get the data from the selected fields and field names
Set rst = CurrentDb.OpenRecordset(strRecordSource)
If rst.BOF Or rst.EOF Then
MsgBox "There are no records"
rst.Close
Exit Sub
End If


'Get the filename for the Excel Spreadsheet
stFilename1 = MSA_SimpleGetSaveFileName
If stFilename1 = "" Then Exit Sub
If right(stFilename1, 4) <> ".xls" Then
stFilename1 = stFilename1 & ".xls"
End If
strText1 = stFilename1

stFilename2 = StripPath(stFilename1)
strFilename = CurrentDBDir & "GradeDistributions.xls"

'defineExcel objects
Set ExApp = CreateObject("Excel.Application")
ExApp.Visible = True
ExApp.WorkBooks.Open strFilename
ExApp.WorkBooks("GradeDistributions.xls").SaveAs (stFilename1)


iCount = 0
With rst
.MoveLast
.MoveFirst
intRows = .RecordCount
ColCount1 = .Fields.Count
For iCount = 0 To ColCount1 - 1
FldName(iCount) = rst.Fields(iCount).Name
Next iCount

varResults = rst.GetRows(intRows + 1)

'remove any null values from the array
For icount2 = 0 To intRows - 1
For iCount = 0 To ColCount1 - 1
varResults(iCount, icount2) = Nz(varResults(iCount, icount2), " ")
Next iCount
Next icount2
.Close
End With

'remove unwanted columns
' ExApp.Columns(5).Select
' ExApp.Selection.Delete Shift:=-4159
' ExApp.Columns(2).Select
' ExApp.Selection.Delete Shift:=-4159

'populate sreadsheet with data
Set MyRange = ExApp.activesheet.cells(3, 1).Resize(intRows, ColCount1)
MyRange.FormulaArray = ExApp.Transpose(varResults)


'add field names
Set MyRange = ExApp.activesheet.cells(2, 1).Resize(1, ColCount1)
MyRange.FormulaArray = FldName

ExApp.Range("A1").SELECT

'add header
Set MyRange = ExApp.activesheet.cells(1, 1)
MyRange.FormulaArray = "Grade distributions by Subject: " & ExcelHeader



ExportToExcel_Exit:
On Error Resume Next
ExApp.ActiveWorkbook.Close True
ExApp.WorkBooks.Close
ExApp.Quit
Set ExApp = Nothing

Dim stmessage As String
stmessage = "Do you want to use the Excel file now?"
If MsgBox(stmessage, vbYesNo) = vbYes Then
DoCmd.Close acForm, "GradesByFaculty"
Dim RetVal
strText1 = "EXCEL.EXE """ & stFilename1 & """"
RetVal = Shell(strText1, vbNormalFocus)
End If

Exit Sub

ExportToExcel_err:
MsgBox "Error # " & err.Number & " " & vbNewLine & err.Description & "
", vbExclamation, "Crosstab query error"
'Resume ExportToExcel_Exit

End Sub

 

[Gary Walter]

when you run the working query,
do you see more than one column
that has 'N/A' ?

WAG...but did you try trimming PIVOT

.....
PIVOT
Trim(PupilGrades.Att);

 

[ridders]

Hi again

I've fixed the problem thanks
There were 2 problems only one of which I had solved earlier:

I had corrected the SQL for strRecordSource which eliminated duplicate
counting of grades such as N/A

However I hadn't looked at code for strSQL1.
By amending this to SELECT DISTINCT and removing ORDER BY, I fixed the
problem completely

Updated code for IntAtt below together with original code
Similar changes also work for other cases which caused problems before


Case "IntAtt"
'Amended SQL works fine - solves error 3104 problem
strSQL1 = "SELECT DISTINCT Grades.Code" & _
" FROM Grades" & _
" WHERE (((Grades.Type)='A') AND ((Grades.KeyStage)=GetKeyStage()));"

'New SQL works fine
strRecordSource = "TRANSFORM Count(PupilGrades.Att) AS CountOfAtt" & _
" SELECT Subjects.SubjectID" & _
" FROM (Subjects INNER JOIN (PupilGrades INNER JOIN ClassModules" & _
" ON PupilGrades.ModuleID = ClassModules.ModuleID) ON
Subjects.SubjectID = ClassModules.SubjectID)" & _
" INNER JOIN Classes ON ClassModules.ClassID = Classes.ClassID" & _
" WHERE (((PupilGrades.Att) <> '') AND
((Classes.Year)=GetYearGroup()) AND ((PupilGrades.SessionID)=GetSession()))"
& _
" GROUP BY Subjects.SubjectID" & _
" ORDER BY Subjects.SubjectID" & _
" PIVOT PupilGrades.Att"

'==============================
Original code was

'Original SQL caused repeated columns (error 3104)
'strSQL1 = "SELECT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'A') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Points DESC, Grades.Order;"

'Original SQL causes duplicate grades e.g. N/A
'strRecordSource = "TRANSFORM Count(PupilGrades.Att) AS CountOfAtt" & _
" SELECT Subjects.SubjectID" & _
" FROM (Grades INNER JOIN (Subjects INNER JOIN (PupilGrades INNER JOIN
ClassModules" & _
" ON PupilGrades.ModuleID = ClassModules.ModuleID) ON Subjects.SubjectID =
ClassModules.SubjectID)" & _
" ON Grades.Code = PupilGrades.Att) INNER JOIN Classes ON
ClassModules.ClassID = Classes.ClassID" & _
" WHERE (((Classes.Year)=GetYearGroup()) AND
((PupilGrades.SessionID)=GetSession())" & _
" AND ((Grades.KeyStage)=GetKeyStage()) AND ((Grades.Type)='A'))" & _
" GROUP BY Subjects.SubjectID, Grades.KeyStage, Grades.Type" & _
" ORDER BY Subjects.SubjectID" & _
" PIVOT PupilGrades.Att"
'==============================

Thanks again for your thoughts on this

 

[Gary Walter]

sorry...forget WAG...its in this part of code!!

'get the column headers for the grades and determine the order - by points
for Att,CRGrade
' and by 'Order' for other grades
Dim VarIn As String
VarIn = ""
Set rst2 = mydb.OpenRecordset(strSQL1)
If Not (rst2.BOF Or rst2.EOF) Then
rst2.MoveFirst
Do Until rst2.EOF
VarIn = VarIn & "'" & rst2!Code & "', "
rst2.MoveNext
Loop
rst2.Close
VarIn = Left(VarIn, Len(VarIn) - 2)
End If

strRecordSource = strRecordSource & " In(" & VarIn & ")"

I bet if you do a Debug.Print here, you'll see your extra 'N/A'

change your strSQL1's to include DISTINCT

strSQL1 = "SELECT DISTINCT Grades.Code" & _
" From Grades" & _
" WHERE (((Grades.Type) = 'E') And ((Grades.KeyStage) = GetKeyStage()))" & _
" ORDER BY Grades.Points DESC, Grades.Order;"

 

[Douglas J. Steele]

PMFJI. Glad to see that you solved your problem, but I thought I'd point
something out about your code.

You indicated that you've got:

Dim strFilename As String
Dim strAlias As String
Dim rst, rst2 As Recordset
Dim ExApp As Object
Dim MyRange As Object
Dim stFilename1, stFilename2 As String
Dim strsql, strSQLx, strSQLLabels As String
Dim intRows, iCount, icount2, iFields As Integer
Dim ColCount1, ColCount2, ColCount3, ModuleCount As Integer

That probably doesn't do what you think it does.

For example, there are two issues with:

Dim rst, rst2 As Recordset

First, that only declares rst2 as a Recordset. VBA doesn't allow
short-circuiting, so rst is actually declared as a Variant. Second,
"Recordset" exists as an object in more than one library (both DAO and ADO).
Since you're using DAO, you really should use:

Dim rst As DAO.Recordset, rst2 As DAO.Recordset

Because of point 1 above,

Dim stFilename1, stFilename2 As String
Dim strsql, strSQLx, strSQLLabels As String
Dim intRows, iCount, icount2, iFields As Integer
Dim ColCount1, ColCount2, ColCount3, ModuleCount As Integer

should be

Dim stFilename1 As String, stFilename2 As String
Dim strsql, strSQLx As String, strSQLLabels As String
Dim intRows As Integer, iCount As Integer, icount2 As Integer, iFields As
Integer
Dim ColCount1 As Integer, ColCount2 As Integer, ColCount3 As Integer,
ModuleCount As Integer

Finally, you're using a variable strRecordSource throughout your code, but
you haven't declared it (at least, not in what you showed). Perhaps you've
declared it at the module level instead, but it could also indicate that you
haven't set VBA up to require declaration of all variables. Look at the top
of the module. Does the line "Option Explicit" appear as one of the top
couple of lines? If not, go into Tools | Options and look on the Module tab.
The "Require Variable Declaration" check box should be selected.

 

[ridders]

Hi Doug

Thanks for your comments

-------------------------------
First...
Dim rst, rst2 As Recordset
First, that only declares rst2 as a Recordset. VBA doesn't allow
short-circuiting, so rst is actually declared as a Variant.

Thanks - I inherited the original database & am gradually working through &
fixing errors. I had wondered if such shortcuts were OK. As they seem to work
i've never changed them.
Presumably fixing all such issues should make the database faster / more
reliable

----------------------------------------
Second,

"Recordset" exists as an object in more than one library (both DAO and ADO).
Since you're using DAO, you really should use:

Dim rst As DAO.Recordset, rst2 As DAO.Recordset

Again should this improve the performance of the database?
----------------------------------

Finally, you're using a variable strRecordSource throughout your code, but
you haven't declared it (at least, not in what you showed). Perhaps you've
declared it at the module level instead, but it could also indicate that you
haven't set VBA up to require declaration of all variables. Look at the top
of the module. Does the line "Option Explicit" appear as one of the top
couple of lines?

Yes I always do this though there are some items in original database
without Option Explicit - again i'm gradually working through & fixing any
errors arising when Option Explicit is added to existing code.
-----------------------------------------------

If not, go into Tools | Options and look on the Module tab.

The "Require Variable Declaration" check box should be selected.

I'm using Access 2003 & don't have a Module tab in Tools ¦ Options.
Can I enforce this in some other way in Access 2003?

 

[ridders]

Hi Gary

Not sure if you wrote this before I fixed the problem

1. There was only one column marked N/A

2. No - didn't try trimming PIVOT - I can't see why this would work

3. WAG? I didn't know this had a meaning unrelated to footballers wives &
girlfriends! Thought I'd look it up...

Acronym Definition
WAG Wild Ass Guess
WAG Gambia (international vehicle registration)
WAG Walgreen Co (stock symbol)
WAG Washington Avenue Grill (White Rock, BC, Canada)
WAG Waste Area Grouping
WAG Water Alternating Gas (water/gas injection)
WAG Watershed Advisory Group
WAG Watershed Assistance Grant
WAG Weapons Attack Guide
WAG Web Advisory Group
WAG Welfare for Animals Guild (Sequim, Washington)
WAG Wellsville, Addison, and Galeton Railroad Corporation
WAG Welsh Assembly Government
WAG What A Guy
WAG Wide Area Grid
WAG Widely Attended Gathering
WAG Wireless Access Gateway
WAG Wireless Application Gateway
WAG Wireless Athens Group
WAG Wise Ass Grin
WAG Wives and Girlfriends (of the English Football team)
WAG Wolkswagen Audi Group
WAG Woman Aggie (Texas A&M University)
WAG Women's Artistic Gymnastics
WAG World Area Grid
WAG Worldwide Analysis Group (wagsys.com)

Presumably Wild Ass guess?

Cheers

 

[Douglas J. Steele]

Hi Doug

Thanks - I inherited the original database & am gradually working through
&
fixing errors. I had wondered if such shortcuts were OK. As they seem to
work
i've never changed them.
Presumably fixing all such issues should make the database faster / more
reliable

More reliable, yes. I doubt it will make any difference in speed (and if it
does, it'll be so slight you'd never notice it).

Second,

Again should this improve the performance of the database?

If it's working, then perhaps you've removed the reference to ADO. If you
were to copy the code into another database where you hadn't deleted the
reference to ADO, you'd find that the code wouldn't work, since ADO is
higher in the order than DAO so takes precedence.

I'm using Access 2003 & don't have a Module tab in Tools ¦ Options.
Can I enforce this in some other way in Access 2003?

Sorry, the name of the tab is Editor in Access 2003 (and you have to be in
the VB Editor when selecting Tools | Options)

 

[ridders]

Hi again

I've now gone thorugh all 250 or so forms / reports & modules & added Option
Explicit wherever it was missing! Thankfully very few compile errors had to
be fixed though there are lots of places where I need to redefine variables
correctly (as in your previous post).

I also found the Editor Options in VB & yes it was already checked - pity it
doesn't work retrospectively!

I have both DAO & ADO references checked but DAO is higher so it is taking
precedence. My understanding was that was the correct order..am I wrong on
this?

 

[Douglas J. Steele]

You can't really say there's a "correct order". The order is whatever you
want.

If you have both references, you should "disambiguate" your declarations,
since objects with the same names exist in the 2 models. For example, to
ensure that you get a DAO recordset, you'll need to use Dim rsCurr as
DAO.Recordset, and to guarantee an ADO recordset, you'd use Dim rsCurr As
ADODB.Recordset.

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset