update table

[DTSFreak]

Hi,

I've this code which i want to update a table. The thing is that i don't get it to work how i want it. I've tried to use a dao-based recordset and loop through the recordset, but then my app hangs.

Can some help me adjust the code to update a table?

code: Public sub Conversion()
Dim oRE As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection
Dim strDescription As String

strDescription = <should be a field of a recordset>

Set oRE = CreateObject("VBScript.RegExp")

oRE.Pattern = "^(?[a-z][^]+)\s+)?(?[0-9.]{5,12})\s+)?(.*)$"

oRE.IgnoreCase = True ' Set case insensitivity.
oRE.Global = False ' Set global applicability.
Set oMatches = oRE.Execute(strDescription) ' Execute search.
If oMatches.Count > 0 Then
On Error Resume Next
Debug.Print "Transfercode:" & oMatches(0).SubMatches(0)
Debug.Print "Acct Number:" & oMatches(0).SubMatches(1)
Debug.Print "Description:" & oMatches(0).SubMatches(2)
Else
Debug.Print "Description:" & strDescription
End If

Set oMatches = Nothing
Set oRE = Nothing

End Function

 

[RoyVidar]

All I see, is some regexp thingies, where's the recordsetstuff, what errormessage are you getting, at which line...

Roy-Vidar

 

[DTSFreak]

I already removed the line, i was hoping someone could make it right. the one i was using was not very readable.

 

[RoyVidar]

Erm - what is the question, really?

Some basic insert?

[tt]strsql = "insert into mytable (field1, field2, field3) " & _
"values (" & somenumeric & ", '" & sometextvar & "', #" & _
somedatetimevar & "#)"
yourconnection.execute strsql,,adcmdtext[/tt]

or what - it's a bit confusing seing some code totally unrelated to the question, and trying to guess what you're trying to do... - better post what you're working on, what errors you're getting, which line... (didn't I alredy say something along those lines? better put in a reference to one of the "how to get the most out of the membership" faqs too, then faq181-2886, especially #14 - I'm of the opininon this is slightly on the short side...)

Roy-Vidar

 

[DTSFreak]

Hi Roy,

Sorry, here's the code:
Dim oRE As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection
Dim rsR As DAO.Recordset
Set oRE = CreateObject("VBScript.RegExp")

oRE.Pattern = "^(?[a-z][^ ]+)\s+)?(?[0-9.]{5,12})\s+)?(.*)$"


oRE.IgnoreCase = True ' Set case insensitivity.
oRE.Global = False ' Set global applicability.

Set rsR = CurrentDb.OpenRecordset("mytable")

'Iterate through records in recordset
With rsR
Do Until rsR.EOF
'Execute search using current Description field
If Not IsNull(.Fields("field0").Value) Then
Set oMatches = oRE.Execute(.Fields("field0").Value)
If oMatches.Count > 0 Then
.Edit
.Fields("field1").Value = oMatches(0).SubMatches(0)
.Fields("field2").Value = oMatches(0).SubMatches(1)
.Fields("field3").Value = oMatches(0).SubMatches(2)
.Update
End If
End If
Loop
End With

rsR.Close
Set rsR = Nothing
Set oRE = Nothing


the problem is when i run this code only the first and last records gets updated, but before that the app will hang and cause a 100% cpu

 

[RoyVidar]

Try adding a

[tt]rsR.MoveNext[/tt]

just before the Loop statement ;-)

Roy-Vidar

 

[DTSFreak]

Hi Roy,

It worked, however i was wondering if you have a ado-based recordset solution for my problem.