Trouble Using an Array to goto ID number

firebug88

New Member
Joined
Aug 6, 2015
Messages
14
So I have a string called idstring which contains numbers (Auto ID numbers) in the format: 5 6 7 8 9 10 .... I put this string into an array using the split function. I then want to use this array to find each row with that specific ID and change a field in that row. My code currently is incomplete as im not sure how to goto that ID number

Code:
set dbs = currentdb
dim rst as recordset
set rst = dbs.openrecordset ("table1")

idarray=Split(idstring)      'idstring is previously defined earlier in the code
rst.movefirst
For x = LBound(idarray)) To UBound(idarray)
       rst.findfirst [ID] = idarray(x)          'This is where im getting the error 
       rst.edit
       rst("field 1") = "sometext"
Next x

The error i get is: error 91: object variable or with block variable not set

Is this the best method or is there some other cmd that i dont know about?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
you dont need an array. The table is the 'array'.
This looks like Excel methodology.
Here in Access-land you just need to run a query. Queries edit and modify the data. (no more arrays)
 
Upvote 0
unfortunatly this answer does not help for two reasons. First you dont give me an example of an alternative that would point me in the right direction and secondly, I do not want to have to run 65K queries, which would be a lot less efficient. I hope someone can still try to shed some light on my original question.
 
Upvote 0
So I have a string called idstring which contains numbers (Auto ID numbers) in the format: 5 6 7 8 9 10 .... I put this string into an array using the split function. I then want to use this array to find each row with that specific ID and change a field in that row. My code currently is incomplete as im not sure how to goto that ID number

Code:
set dbs = currentdb
dim rst as recordset
set rst = dbs.openrecordset ("table1")

idarray=Split(idstring)      'idstring is previously defined earlier in the code
rst.movefirst
For x = LBound(idarray)) To UBound(idarray)
       rst.findfirst [ID] = idarray(x)          'This is where im getting the error 
       rst.edit
       rst("field 1") = "sometext"
Next x

The error i get is: error 91: object variable or with block variable not set

Is this the best method or is there some other cmd that i dont know about?

Yes there are probably much better ways. In Access you general use a filtered query and and avoid the find first. It is very slow. Way to may records get read with find first,. Potentially the enter table every time.. I have built 1000's of datasbes for clietns and a full blown accouitng systemin Access have have used First First myself less that 10 times. I remove it from others database every time I see it with improves performance.

To be abe to give you any specific helpful tips we will need sto see all the code related tot eh execution of the process..
 
Upvote 0
I do not want to have to run 65K queries, which would be a lot less efficient. I hope someone can still try to shed some light on my original question.

A well design database runs queries very efficiently. Queries and SQL is the core to using an Relational Database. Even with all the flavors of SQL Server.

In every Access based Application (a database complete with all the forms, reports, and queries) has more queries than all the other objects combines.



I hope someone can still try to shed some light on my original question.

To do that we need to see all related code.
 
Upvote 0
Thanks for the feedback HiTechCoach. Unfortunatly I cannot post my databse or code but i can try and describe my procedure and hopefully that is enough informaiton for an example.

In a nut shell, I have a table with 69K rows that i want to edit a field based on 4 other fields. Sample code on this:

Code:
rst.movefirst
do until rst.eof
   strdum1 = field1 + field2 + field3 + field4
   counter = 0
   idstring = ""
   set rst3 = same as rst basically
   rst3.movefirst
   do until rst3.eof
      strdum2 = field1 + field2 + field3 + field4
      if strdum1 = strdum2 then
         counter = counter + 1
         If len(idstring) < 1 then
            idstring = field6
         else
            idstring = idstring & "" & field6
         end if
      end if
   rst3.movenext
   loop

if counter > 20 then
   idarray = split(idstring)
   for x = lbound(idarray) to ubounf(idarray)
      do until rst2.eof
         if rst2 ID = int(idarray(x)) then
            rst2.edit
            rst2.field7 = somevalue
         end if
     loop
   next x
end if
This code works and is running but its very slow and gives me error messages on running out of system resources (prob for good reason).
 
Upvote 0
Do need to see you actual database but the actual code is really critical. If you can't then it will be very difficult to help you in an open forum. You might consider hiring someone who will sign a NDA.

First, At the top of the code module (usually the second line) do you have Option Explicit? If not then you really need to add it.


I do not see any DIM statements that define the data types. These are import to see.
Where does field1 + field2 + field3 + field4 come from?
Where is rst opened?

Unless I can see all the code I really can't help you.

I still think this can be done must more efficiently with an Update query.

TIP: Using an Update Query is faster that using a recordset.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top