473,322 Members | 1,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Object required error in an adaption of vba code

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

2 2397
Stewart Ross
2,545 Expert Mod 2GB
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
Oxydo
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

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

Similar topics

3
by: News Groups | last post by:
Dear Group I am trying to use the following code to refer to a form and control on my form. I am supposed to be using VBSCRIPT for this code. I always get an "object required: 'document' " error:...
1
by: contact | last post by:
Hi, I'm trying to use the following to replace a text string in all the links on a page: var arrLinks = document.links; for(var LinkIterator = 0; LinkIterator < arrLinks.length; LinkIterator++)...
7
by: google | last post by:
I am trying to use the following ASP code to examine the file names in a folder: Dim fso, f, fl, s, fs Set fso = CreateObject("Scripting.FileSystemObject") Set f =...
2
by: RICHARD BROMBERG | last post by:
I am using Access 200. I have a form which includes text boxes L1, L2 and L3 And a table tblGrades with number fields L1, L2 and L3
5
by: formatt | last post by:
Hi can anyone help with this little problem. I have a simple script to swap between two divs on a page. It works perfectly in Firefox & Safari & Opera. It fails in IE 6 giving Object Required...
4
by: tcnjdeluca | last post by:
Hello I am sorry if this is a really novice question, but I am virtually clueless when it comes to Javascript. I have a page that is running the "leightbox" script. I have modified the script as...
6
by: jatin32 | last post by:
Hi, I have below code on Form_Load() , I get one time error when I open this form. 424 Object required, please help ASAP. Option Compare Database Private Sub Form_Load()
5
by: ash2009 | last post by:
Hi, I am getting an object required error on IE6 Line: 1826 Char:3 Code:0 When I do view Source on my browser: here's what I see for line 1826: function navBarGetMousePosition(e){
2
by: Sneha Bele | last post by:
Hi, I am beginner in Visual Basic. I am doing a simple ADODC project using MS Acces. In that when I add new record to the database it gives me error 'Object Required'. I tried the same code onj...
0
by: ibrahim Nohbala | last post by:
Hello. I've written a small program in MS Access 2007 that calculates how many goods left in stock in 3 different units. For example, I have a product that has 3 units of measurement: gr,kg and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.