By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Object required error in an adaption of vba code

P: 7
Hello all,

As you may recognize in the following code i've tried to merge 2 example pieces of code into one. I'm getting an error message stating an object is required on the line;

Expand|Select|Wrap|Line Numbers
  1. For lngColumn =0 to rst.Fields.Count -1
  2.  
Complete code is as follows;

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub tellijstvest()
  3. Dim qdf As DAO.QueryDef
  4. Dim dbs As DAO.Database
  5. Dim rstTel As DAO.Recordset
  6. Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
  7. ' *** Aantal variabelen worden voorbereid om gegevens te bewaren.
  8. Dim strTemp As String, strSQL As String, strTel As String, strBestandspad As String, strWorksheetnaam As String, strVestiging As String
  9. ' *** Aantal
  10. ' *** De tijdelijke query wordt zExport genoemd.
  11. Const strTempQueryName As String = "zExportTellijst"
  12. ' *** Variabele dbs wordt gevuld met de gegevens van de huidige database
  13. Set dbs = CurrentDb
  14. ' *** Variabele strVestiging wordt hier gevuld, door dit op te vragen bij de gebruiker.
  15. strVestiging = InputBox(Prompt:="Voor welke vestiging?", _
  16.         Title:="Vestigingsnummer")
  17. ' *** Eventueel kan hier een if functie ingevoegd worden om alle vestigingen te doen als het veld leeggelaten wordt.
  18. ' *** Het bestand wordt aangemaakt op de volgende locatie, met vestigingsnummer gevolgd door datum in de bestandsnaam.
  19. strBestandspad = "C:\test\" & strVestiging & Format(Now(), "yyyMMMdd") & ".xls"
  20.  
  21. ' *** onbekende code om de query te initialiseren
  22. strTemp = dbs.TableDefs(0).Name
  23. strSQL = "SELECT * FROM [" & strTemp & "] where 1=0;"
  24. Set qdf = dbs.CreateQueryDef(strTempQueryName, strSQL)
  25. qdf.Close
  26. strTemp = strTempQueryName
  27.  
  28. ' *** alle regels en kolommen met het betreffende vestigingsnummer worden in een recordset opgeslagen
  29. strSQL = "SELECT * from tbl_dbs_test WHERE " & "Ves = " & strVestiging & ";"
  30. Set rstTel = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
  31. ' *** de eerste regel bevat kolomkoppen
  32. blnHeaderRow = True
  33.  
  34. 'hier wordt een excel bestand gemaakt
  35. On Error Resume Next
  36. Set xlx = GetObject(, "Excel.Application")
  37. If Err.Number <> 0 Then
  38.     Set xlx = CreateObject("Excel.Application")
  39.     blnEXCEL = True
  40. End If
  41. Err.Clear
  42. On Error GoTo 0
  43. xlx.Visible = False
  44. ' *** Nieuw werkboek wordt gemaakt, met het vestigingsnummer als naam.
  45. Set xlw = xlx.workbooks.Add
  46. Set xls = xlw.worksheets(1)
  47. xls.Name = strVestiging
  48. ' *** Excel begint in veld A1
  49. Set xlc = xls.range("A1")
  50. ' *** als er gegevens in de recordset staan worden deze naar excel geschreven
  51. If rstTel.EOF = False And rstTel.BOF = False Then
  52. ' *** eerst de header
  53.     For lngColumn = 0 To rst.Fields.Count - 1
  54.         xlc.offset(0, lngColumn).Value = rstTel.Fields(lngColumn).Name
  55. ' *** door naar de volgende
  56.     Next lngColumn
  57.     Set xlc = xlc.offset(1, 0)
  58. xl.copyfromrecordset rstTel
  59. End If
  60. rstTel.Close
  61. Set rstTel = Nothing
  62. dbs.Close
  63. Set xlc = Nothing
  64. Set xls = Nothing
  65. xlw.saveas strBestandspad
  66. xlw.Close False
  67. Set xlw = Nothing
  68. If blnEXCEL = True Then xlx.Quit
  69. Set xlx = Nothing
  70.  
  71. End Sub
  72.  
Anyone know why im getting this error? I'm running access 2003 and Excel 2003.

Kind regards,

Oxydo
Jan 4 '12 #1

✓ answered by Stewart Ross

It is easy to miss names that are not what you have defined - and that at least appears to be what is wrong in this case.

The recordset object variable you have defined and used everywhere else except line 53 is rstTel, but in line 53 you are referring to the non-existent variable rst - hence the error.

The VBA compiler would have warned you about variable rst not being defined if you had an option explicit statement at the top of your module. If you are not in the habit of doing so it is best to have such a statement in all modules, as the compiler then explicitly requires that all variable names have been defined in Dim, Public, Private or Static statements before they are used.

-Stewart

Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
It is easy to miss names that are not what you have defined - and that at least appears to be what is wrong in this case.

The recordset object variable you have defined and used everywhere else except line 53 is rstTel, but in line 53 you are referring to the non-existent variable rst - hence the error.

The VBA compiler would have warned you about variable rst not being defined if you had an option explicit statement at the top of your module. If you are not in the habit of doing so it is best to have such a statement in all modules, as the compiler then explicitly requires that all variable names have been defined in Dim, Public, Private or Static statements before they are used.

-Stewart
Jan 4 '12 #2

P: 7
Stewart,

Thanks for your solution as well as your tip to use option explicit. I've now added this rule and found & solved a few more issues with this sub, thanks for the pointer!

Kind regards,

Oxydo
Jan 4 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.