2013 findfirst not found 2

[PizMac]

I have converted a 2003 mdb to a 2013 accdb and now my modules won't compile. I get (for findfirst) - "method or data member not found". Do I need new reference libraries or something?

code below:-
mySQL = "SELECT * FROM [5-4AveSchoolBySector] ORDER BY [SID], [Sect];"
Set myPercs = myDB.OpenRecordset(mySQL)
MyInput.MoveFirst
Do
mySID = MyInput!sid
mySQL = "SELECT * FROM [5SchoolSectors] WHERE Sid = " & mySID & ";"
Set MyOutput = myDB.OpenRecordset(mySQL)
MyOutput.MoveFirst
Do
mySect = MyOutput!sect
myPercs.FindFirst "SID = " & mySID & " and Sect = '" & mySect & "'

 

[missinglinq]

How do you have myPercs dimmed at the top of the sub? I suspect that it should be

Dim myPercs As DAO.Recordset

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[PizMac]

yes - myPercs As Recordset - I didn't want to copy the entire sub - it's just a problem with the .findfirst and it works in Access 2003 - but here is the entire function if it helps...

Function MissingSects()
Dim MyOutput As Recordset, MyInput As Recordset, myPercs As Recordset
Dim mySID As Integer, myExtra As Variant
Set myDB = CurrentDb()
mySQL = "DELETE * from 5SchoolSectors;"
DoCmd.SetWarnings 0
DoCmd.RunSQL mySQL
Set MyOutput = myDB.OpenRecordset("5SchoolSectors")
Set myPercs = myDB.OpenRecordset("5-2SectorPercs")
' add 1 rec for each big SID/sector combination with default percs
Set MyInput = myDB.OpenRecordset("5-5aBigSchoolsTotPercs")
MyInput.MoveFirst
Do
mySID = MyInput!sid
myPercs.MoveFirst
Do
MyOutput.AddNew
MyOutput!sid = mySID
MyOutput!sect = myPercs!sect
MyOutput!Perc = myPercs!Perc
MyOutput.Update
myPercs.MoveNext
Loop While Not myPercs.EOF
MyInput.MoveNext
Loop While Not MyInput.EOF

' now -for those SIDS that don't add to 100% do something!


mySQL = "SELECT * FROM [5-4AveSchoolBySector] ORDER BY [SID], [Sect];"
Set myPercs = myDB.OpenRecordset(mySQL)
MyInput.MoveFirst
Do
If MyInput!SumOfPerc < 1 Then
' set missing sects to zero and apportion over the rest
mySID = MyInput!sid
myExtra = 1 - MyInput!SumOfPerc
mySQL = "SELECT * FROM [5SchoolSectors] WHERE Sid = " & mySID & ";"
Set MyOutput = myDB.OpenRecordset(mySQL)
MyOutput.MoveFirst
Do
myPercs.FindFirst "SID = " & mySID & " and Sect = '" & MyOutput!sect & "'"
MyOutput.Edit
If myPercs.NoMatch Then
' missing sect
MyOutput!Perc = 0
Else
' allocate missi ng over here
MyOutput!Perc = MyOutput!Perc + (MyOutput!Perc / MyInput!SumOfPerc * myExtra)
End If
MyOutput.Update
MyOutput.MoveNext

Loop While Not MyOutput.EOF


End If


MyInput.MoveNext
Loop While Not MyInput.EOF

End Function

 

[dhookom]

PizMac, please use TGML Code tags to make your code more readable. Also, consider following missinglinq's advice of being more explicit by specifying DAO.

Duane
Hook'D on Access
MS Access MVP

 

[PizMac]

Thanks both - inserting the magic DAO. cured the problem - my code is ancient. and - what are TGML Code tags?

 

[dhookom]

The Reply to This Thread box/input area has formatting icons very much like working in Word and other software that allows you to format your text. Just select the text to be formatted and click an icon. You can hover over any icon to see what affect it will have on your text. I like to also change the font color and highlight comments to add to the readability.

Always preview your posts prior to submitting.

Function MissingSects()
    Dim MyOutput As DAO.Recordset, MyInput As DAO.Recordset, myPercs As DAO.Recordset
    Dim mySID As Integer, myExtra As Variant
    Set myDB = CurrentDb()
    mySQL = "DELETE * from 5SchoolSectors;"
    DoCmd.SetWarnings 0
    DoCmd.RunSQL mySQL
    Set MyOutput = myDB.OpenRecordset("5SchoolSectors")
    Set myPercs = myDB.OpenRecordset("5-2SectorPercs")
[COLOR=#FF0000]    [highlight #FCE94F]' add 1 rec for each big SID/sector combination with default percs[/highlight][/color]
    Set MyInput = myDB.OpenRecordset("5-5aBigSchoolsTotPercs")
    MyInput.MoveFirst

Duane
Hook'D on Access
MS Access MVP