Mocrosoft Access Macro Error

Jeffrey Smith
Jeffrey Smith used Ask the Experts™
on
Hello,

I am a novice learner picking off where one co worker left off with a Mircrosoft Access Database. When attempting to run a Macro, I am receiving a Run Time error of 3251. Operation is not supported for this type of object. It errors at Mytable2.Seek "=", ss. Any help is greatly appreciated!!

Public Function prrtf()
       Dim MyDb As Database, MyWorkSpace As Workspace
       Dim Mytable As Recordset, Mytable2 As Recordset
       Dim xsw As String, lastemp As String, emp As String, FIL1 As String
       Dim yr As Integer, per As Integer, mth As String, hrs As Double, nmth As Integer, nyr As Integer
       Dim fyr As Integer, gross As Double, ss As String, lastss As String, ln As String
       Set MyWorkSpace = DBEngine.Workspaces(0)
       Set MyDb = CurrentDb()
       'Set MyDb = MyWorkSpace.Databases(0)
       ' Open Tables
       Set Mytable = MyDb.OpenRecordset("NewAlldata")
      'Mytable.Index = "SSNandPeriod"
       Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")
       'Mytable2.Index = "SSN"
       xsw = " "
       lastemp = " "
       lastss = " "
       nmth = 0
       nyr = 0
       'FIL1$ = "O:\DATA_\ACCESS\RETDWNLD\names"
       FIL1$ = "O:\Data\RETIRED\retdwnld\names"
       Open FIL1$ For Input As #1
mainnm:
       If EOF(1) Then GoTo finalnm
       Line Input #1, ln
       ss = Mid$(ln, 46, 3) + Mid$(ln, 50, 2) + Mid$(ln, 53, 4)
       Mytable2.Seek "=", ss
       If Mytable2.nomatch Then GoTo nextnm
       GoTo mainnm
nextnm:
       'MsgBox "name = " + Trim(Mid$(ln, 1, 30)) + "," + Trim(Mid$(ln, 31, 15))
       'MsgBox "ss = " + ss
       Mytable2.AddNew
       Mytable2![Name] = Trim(Mid$(ln, 1, 30)) + "," + Trim(Mid$(ln, 31, 15))
       Mytable2![Last Name] = Mid$(ln, 1, 30)
       Mytable2![First Name] = Mid$(ln, 31, 15)
       Mytable2![Address Line 1] = Mid$(ln, 61, 30)
       Mytable2![City] = Mid$(ln, 91, 17)
       Mytable2![State] = Mid$(ln, 109, 2)
       Mytable2![Zip] = Mid$(ln, 111, 5)
       Mytable2![Emplid] = ss
       Mytable2![SSN] = ss
       Mytable2.update
       GoTo mainnm
finalnm:
       Close #1
       Mytable2.Close
       'FIL1$ = "O:\DATA_BMH\ACCESS\RETDWNLD\prrtf"
       FIL1$ = "O:\Data\RETIRED\retdwnld\prtrf"
       Open FIL1$ For Input As #1
mainp:
       If EOF(1) Then GoTo finalp
       Line Input #1, ln
       ss = Left$(ln, 9)
       yr = Val(Mid$(ln, 10, 4))
       per = Val(Mid$(ln, 14, 2))
       hrs = Val(Mid$(ln, 23, 1) + Mid$(ln, 16, 7))
       gross = Val(Mid$(ln, 33, 1) + Mid$(ln, 24, 9))
       Mytable.Seek "=", ss, yr, per
       If Mytable.nomatch Then GoSub addtotblp: GoTo mainp
       Mytable.Edit
       Mytable![Hours Mtd] = hrs / 100
       Mytable![Gross Mtd] = gross / 100
       Mytable.update
       GoTo mainp
addtotblp:
       Mytable.AddNew
       If per < 4 Then Mytable![Year] = (yr - 1) Else Mytable![Year] = yr
       Mytable![Period] = per
       Mytable![Hours Mtd] = hrs / 100
       Mytable![Gross Mtd] = gross / 100
       Mytable![Emplid] = ss
       Mytable![SSN] = ss
       Mytable![FISCAL YEAR] = yr
       Mytable![Reason] = "   "
       Select Case per
          Case 1
             mth = "October"
          Case 2
             mth = "November"
          Case 3
             mth = "December"
          Case 4
             mth = "January"
          Case 5
             mth = "February"
          Case 6
             mth = "March"
          Case 7
             mth = "April"
          Case 8
             mth = "May"
          Case 9
             mth = "June"
          Case 10
             mth = "July"
          Case 11
             mth = "August"
          Case 12
             mth = "September"
       End Select
       Mytable![Month] = mth
       Mytable.update
       Return

finalp:
       Close
       Mytable.Close
finalend:
End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
quick try, 2 suggestions:

1. try check the References if there's any Library missing out there?
2. try to declare the recordset as below instead:

Dim Mytable As DAO.Recordset, Mytable2 As DAO.Recordset

Open in new window

Author

Commented:
Hey Ryan,

I updated the code to reflect your recommendation of Dim Mytable As DAO.Recordset & Dim Mytable2 As DAO.Recordset but still receiving the an error but now the error reads object variable or with block variable not set for line "Mytable2.Seek "=", ss"

Public Function prrtf()
       Dim MyDb As Database
       ', MyWorkSpace As Workspace
       Dim Mytable As DAO.Recordset
       Dim Mytable2 As DAO.Recordset
       Dim xsw As String, lastemp As String, emp As String, FIL1 As String
       Dim yr As Integer, per As Integer, mth As String, hrs As Double, nmth As Integer, nyr As Integer
       Dim fyr As Integer, gross As Double, ss As String, lastss As String, ln As String
       'Set MyWorkSpace = DBEngine.Workspaces(0)
       Set MyDb = CurrentDb()
       'Set MyDb = MyWorkSpace.Databases(0)
       ' Open Tables
       Set Mytable = MyDb.OpenRecordset("NewAlldata")
       'Set Mytable = MyDb.OpenRecordset("NewAlldata")
       'Mytable.Index = "SSNandPeriod"
       'Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")
       'Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")
       'Mytable2.Index = "SSN"
       xsw = " "
       lastemp = " "
       lastss = " "
       nmth = 0
       nyr = 0
       'FIL1$ = "O:\DATA_BMH\ACCESS\RETDWNLD\names"
       FIL1$ = "O:\Data\RETIRED\retdwnld\names"
       Open FIL1$ For Input As #1
mainnm:
       If EOF(1) Then GoTo finalnm
       Line Input #1, ln
       ss = Mid$(ln, 46, 3) + Mid$(ln, 50, 2) + Mid$(ln, 53, 4)
       Mytable2.Seek "=", ss
       If Mytable2.nomatch Then GoTo nextnm
       GoTo mainnm
nextnm:
       'MsgBox "name = " + Trim(Mid$(ln, 1, 30)) + "," + Trim(Mid$(ln, 31, 15))
       'MsgBox "ss = " + ss
       Mytable2.AddNew
       Mytable2![Name] = Trim(Mid$(ln, 1, 30)) + "," + Trim(Mid$(ln, 31, 15))
       Mytable2![Last Name] = Mid$(ln, 1, 30)
       Mytable2![First Name] = Mid$(ln, 31, 15)
       Mytable2![Address Line 1] = Mid$(ln, 61, 30)
       Mytable2![City] = Mid$(ln, 91, 17)
       Mytable2![State] = Mid$(ln, 109, 2)
       Mytable2![Zip] = Mid$(ln, 111, 5)
       Mytable2![Emplid] = ss
       Mytable2![SSN] = ss
       Mytable2.update
       GoTo mainnm
finalnm:
       Close #1
       Mytable2.Close
       'FIL1$ = "O:\DATA_BMH\ACCESS\RETDWNLD\prrtf"
       FIL1$ = "O:\Data\RETIRED\retdwnld\prtrf"
       Open FIL1$ For Input As #1
mainp:
       If EOF(1) Then GoTo finalp
       Line Input #1, ln
       ss = Left$(ln, 9)
       yr = Val(Mid$(ln, 10, 4))
       per = Val(Mid$(ln, 14, 2))
       hrs = Val(Mid$(ln, 23, 1) + Mid$(ln, 16, 7))
       gross = Val(Mid$(ln, 33, 1) + Mid$(ln, 24, 9))
       Mytable.Seek "=", ss, yr, per
       If Mytable.nomatch Then GoSub addtotblp: GoTo mainp
       Mytable.Edit
       Mytable![Hours Mtd] = hrs / 100
       Mytable![Gross Mtd] = gross / 100
       Mytable.update
       GoTo mainp
addtotblp:
       Mytable.AddNew
       If per < 4 Then Mytable![Year] = (yr - 1) Else Mytable![Year] = yr
       Mytable![Period] = per
       Mytable![Hours Mtd] = hrs / 100
       Mytable![Gross Mtd] = gross / 100
       Mytable![Emplid] = ss
       Mytable![SSN] = ss
       Mytable![FISCAL YEAR] = yr
       Mytable![Reason] = "   "
       Select Case per
          Case 1
             mth = "October"
          Case 2
             mth = "November"
          Case 3
             mth = "December"
          Case 4
             mth = "January"
          Case 5
             mth = "February"
          Case 6
             mth = "March"
          Case 7
             mth = "April"
          Case 8
             mth = "May"
          Case 9
             mth = "June"
          Case 10
             mth = "July"
          Case 11
             mth = "August"
          Case 12
             mth = "September"
       End Select
       Mytable![Month] = mth
       Mytable.update
       Return

finalp:
       Close
       Mytable.Close
finalend:
End Function
Ryan ChongSoftware Team Lead

Commented:
but now the error reads object variable or with block variable not set for line "Mytable2.Seek "=", ss"

try un-comment the line:

 'Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")

Open in new window


to:

 Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks Ryan, I added that code but now it is erroring at  Mytable2.AddNew

mainnm:
       If EOF(1) Then GoTo finalnm
       Line Input #1, ln
       ss = Mid$(ln, 46, 3) + Mid$(ln, 50, 2) + Mid$(ln, 53, 4)
        Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")
       'Mytable2.Seek "=", ss
       If Mytable2.nomatch Then GoTo nextnm
       GoTo mainnm
nextnm:
       'MsgBox "name = " + Trim(Mid$(ln, 1, 30)) + "," + Trim(Mid$(ln, 31, 15))
       'MsgBox "ss = " + ss
       Mytable2.AddNew
Ryan ChongSoftware Team Lead

Commented:
can you attached a sample access file here so that we can investigate the issue?

pls mask or delete your confidential data before attached here.

Author

Commented:
Ryan,

Here is the script, thanks.

-Jeffrey

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial