UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> First Attempt At Looping Through Dao Recordset, Any Version    
 
   
stephenAA5
post Jul 11 2018, 04:49 PM
Post#1



Posts: 273
Joined: 8-October 09



And it ain't workin'. Need to loop through a few thousand records and update a field for each record with a calculated field. I know the calculation part works, because it current is in use on a one at a time basis. The loop doesn't work at all. The good news is that it doesn't blow up. The bad is that it doesn't blow up because it doesn't do anything.

Thank you!

##### Begin
Dim str1 As String
Dim str2 As String
Dim i1 As Integer
Dim i2 As Integer
Dim strResult As String
Dim strSQL As String

Dim rs As DAO.Recordset
Dim db As DAO.Database

strSQL = "SELECT * FROM employees"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'establish cryto
str1 = "0123456789,./:@\abcdefghijklmnopqrstuvwxyz_ ABCDEFGHIJKLMNOPQRSTUVWXYZ"
str2 = "3ak8ufv7elnxm0owpq9s.z4j)/y1dc5b@gi2:t\h6,r-ABCDEFGHIJKLMNOPQRSTUVWXYZ"

With rs
.MoveFirst

Do Until .EOF 'for each record in the table, convert the value in eename using the crypto above
For i1 = 1 To Len([eename])
i2 = InStr(1, str1, Mid([eename], i1, 1), 0)
If i2 > 0 Then
strResult = strResult & Mid(str2, i2, 1)
Else
strResult = strResult & Mid([eename], i1, 1)
End If
Next i1
devicesetting1 = strResult 'enter the calculated value into the devicesetting column
.MoveNext
Loop

.Close
End With
ExitSub:
Set rs = Nothing
Exit Sub

##### End
Go to the top of the page
 
stephenAA5
post Jul 11 2018, 05:20 PM
Post#2



Posts: 273
Joined: 8-October 09



OK, so after a few hours of dorking with it, this works. The key was in the .edit .update and the use of ! But what it won't do is close the darn form. Any ideas what I'm not getting right?

#######Begin
On Error GoTo ErrorHandler

Dim str1 As String
Dim str2 As String
Dim i1 As Integer
Dim i2 As Integer
Dim strResult As String
Dim strSQL As String

Dim rs As DAO.Recordset
Dim db As DAO.Database

strSQL = "SELECT eename, devicesetting1 FROM employees;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'establish cryto
str1 = "0123456789,./:@\abcdefghijklmnopqrstuvwxyz_ ABCDEFGHIJKLMNOPQRSTUVWXYZ"
str2 = "3ak8ufv7elnxm0owpq9s.z4j)/y1dc5b@gi2:t\h6,r-ABCDEFGHIJKLMNOPQRSTUVWXYZ"

With rs

.MoveFirst

Do Until .EOF 'for each record in the table, convert the value in eename using the crypto above
strResult = ""
.Edit
For i1 = 1 To Len(!eename)
i2 = InStr(1, str1, Mid(!eename, i1, 1), 0)
If i2 > 0 Then
strResult = strResult & Mid(str2, i2, 1)
Else
strResult = strResult & Mid(!eename, i1, 1)
End If
Next i1
!devicesetting1 = strResult 'enter the calculated value into the devicesetting column
.update
.MoveNext
Loop

.Close
End With
DoCmd.Close acForm, "Testing" ' DOES NOT WORK AT ALL

ExitSub:
Set rs = Nothing
Exit Sub
ErrorHandler:
Resume ExitSub


Go to the top of the page
 
theDBguy
post Jul 11 2018, 05:37 PM
Post#3


Access Wiki and Forums Moderator
Posts: 73,438
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Is it necessary to use a recordset? I think maybe you can do the same thing using an UPDATE query.

Just my 2 cents...
Go to the top of the page
 
stephenAA5
post Jul 11 2018, 05:45 PM
Post#4



Posts: 273
Joined: 8-October 09



I couldn't figure out how to do the query, and was able to get this to work.
Go to the top of the page
 
theDBguy
post Jul 11 2018, 05:58 PM
Post#5


Access Wiki and Forums Moderator
Posts: 73,438
Joined: 19-June 07
From: SunnySandyEggo


As long as it did. Congratulations! Good luck with your project.
Go to the top of the page
 
stephenAA5
post Jul 11 2018, 06:00 PM
Post#6



Posts: 273
Joined: 8-October 09



The calc works, but closing the form when it's done doesn't and I have no idea why that would be the case.
Go to the top of the page
 
theDBguy
post Jul 11 2018, 06:02 PM
Post#7


Access Wiki and Forums Moderator
Posts: 73,438
Joined: 19-June 07
From: SunnySandyEggo


Where are you executing this code? If on the same form you’re trying to close, then maybe there’s a conflict where it can’t close itself because some code is still running in memory.
Go to the top of the page
 
projecttoday
post Jul 11 2018, 08:45 PM
Post#8


UtterAccess VIP
Posts: 10,148
Joined: 10-February 04
From: South Charleston, WV


Try Docmd.Close acForm, Me.Name.

I suggest UPDATE employees SET eename = yourfunction(eename). Put the text manipulation in yourfunction.

Go to the top of the page
 
stephenAA5
post Jul 14 2018, 11:57 AM
Post#9



Posts: 273
Joined: 8-October 09



This form closing problem was resolved, but I'm honestly not sure how. And given my deadline, I'll sort it out later. Thank you all for your input!
Go to the top of the page
 
theDBguy
post Jul 14 2018, 05:01 PM
Post#10


Access Wiki and Forums Moderator
Posts: 73,438
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it working. Good luck with your project.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th October 2018 - 07:45 AM