Looping through a recordset

[DevTycoon]

Sorry for the late night post. I am midnight coding and need rest. After work tomorrow I will check post and keep truckin' on this project. Also sorry for code format. I had to manually type the code because the machine I am coding on is not connected to the internet.

I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track.

http://www.accessallinone.com/looping-through-a-recordset/

My question is a followup to a thread that was opened on this forum:

http://www.access-programmers.co.uk/forums/showthread.php?t=192793

I want to do the following:

1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format
2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS)
3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?)
4) Use an AND statment with an if statment (how to do this with correct syntax)

sub loopandfix()

on error goto errorhandler:

strSQL = "tblUnionQueryResults"   'table was created from a union query but has inconsistant naming conventions for the fleet location name

with rs
  If not .bof and .eof then
      .movelast
      .movefirst
              while (not .eof)

                       If rs.fields![fleetlocation] = "string resembling an inconsistency" then
                        rs.edit
                        rs.fields![fleetlocation] = "new value"
                        rs.update

                       end if  


'#####another scenario where the if statment also has an AND statment

                       if instr(1,rs.fields![fleetlocation],"string to match")>0 AND instr(1,rs.fields![fleetlocation],"#")>0 then
                           
                        rs.edit
                        rs.fields![fleetlocation] = "new value that will not add a # to fleet location "
                        rs.update

                       else
' no # in the fleet location name 
'extract the text in the middle that causes naming convention inconsistency
'add a "#" before the fleet location number after fleet location name
                      end if
errorhandler:
error handling code

end sub

Thanks for any help!

 

[DavidAtWork]

For each record in your recordset, you can certainly assign the value of rs.fields![fleetlocation] to a variable (providing it's Not NULL) and by the looks of it a string variable seems appropriate. Then do your logical tests on the value of the variable, but when you come to edit/update, you will still need to refer back to rs.fields![fleetlocation]

David

 

[ZikO]

2) In order to update a table via recordset, I think you use have to run method .Edit. Then after you are done you need to finish by running .Update. Once you have done both, the table should be updated.

3) I use rs![field name] to refer to a field name in a recordset. If I need to parametrise it I simply use a string or string variable, such as strName = "myField", and then expression: rs("myField") or rs(strName).

4)

If expression1 And expression2 And ... And ExpressionN Then
    ' Code when all statements are True
Else
    ' Code, otherwise
End If

 

[vbaInet]

There are times when recordsets are required but this isn't one of them. If you're trying to learn how to use recordsets then we can guide you but if your aim is to use recordsets to accomplish your objective (based on what I see in the code), then recordsets is not the way to go.

I'll point out some things:

1. You're trying to update a Union query - a Union query is not updateable. Try building a union query and see for yourself.
2. Create a SELECT query to gather all the anomalies
3. Create a query to update all the records that contain the anomalies.

It's as simple as 2 and 3. You can fire query 3 in code if you wish.

 

[stopher]

As vbaInet says, this should be done using an SQL query. No need to go to the trouble of using recordsets.

Nevertheless, one point to add is that rs.fields![fleetlocation] is already a variable albeit an object variable. Since you are using WITH then you can refer to the field simply as ![fleetlocation]. See here for example. Not sure why your link is using the With construct then doesn't make use of it!

@vabInet: I think the OP is refering to a table created by a union query (might be wrong) - so I'm guessing a temp table ??

 

[vbaInet]

@vabInet: I think the OP is refering to a table created by a union query (might be wrong) - so I'm guessing a temp table ??

You might be right there stopher! I made an assumption that the OP is using "table" and "query" interchangeably.

 

[DevTycoon]

As vbaInet says, this should be done using an SQL query. No need to go to the trouble of using recordsets.

Nevertheless, one point to add is that rs.fields![fleetlocation] is already a variable albeit an object variable. Since you are using WITH then you can refer to the field simply as ![fleetlocation]. See here for example. Not sure why your link is using the With construct then doesn't make use of it!

@vabInet: I think the OP is refering to a table created by a union query (might be wrong) - so I'm guessing a temp table ??

Yes, the table was made from a union query. For some reason the data was given to me for a date range (7 months) by monthly reports. I just merged it all into one table. Then I noticed the unique Identifier was a string for the fleet location name .

I need to get the names to a value that will match with another table I have that will assign them a fleet identity that is a unique number. There is also a chance that this will not be the only time I need to do this so I figured that I would make an arsenal of logic statements that would tackle any combination of naming convention. and could be run at any time.

Also, you say this is not an appropriate application for record sets? I guess I don't understand how I would have 7+ different logic statements (If this is true do this) if I am only using SQL update query. Pardon my nub e understanding but how could my code be arranged to use SQL update for many combinations of incorrect naming conventions. I guess a better question would be ... how would I set up my logic test expression to run a specific SQL update that would tackle all my different combinations of naming convention challenges. I have kept the combinations out of the context of my questions because I need feedback on my logic structure while i figure out how to manipulate the strings using text functions to get my desired results. Text manipulation is not one of my strongpoints.

Thanks for the thread participation so far!!!

 

[vbaInet]

So now that you have the logic laid out in your code, you can look to translating that into a query.

Start with one IF block get the SELECT statement working in a query. Do the other IF block, get that working in a query too, then move on to converting each one of them into an UPDATE query. You have everything nicely laid out already.

The Criteria row in a query is used to filter data based on what's entered. Let us know if you need more help, but first give it a go.

 

[DevTycoon]

I am going to give this a go and open a new thread if I need help still. My project has taken a turn and this part of it got me in the weeds a little bit with my deadline. I appreciate the help and will try to followup with this if my project timeline permits.


Your comments are much appreciated!

 

[vbaInet]

No problem!