Run time error in VBA Code

[BusyProfessorFromBremen]

Hi @ all,

I experience a problem by executing a vba algorithm. This should initially
cut two string colums into smaller parts and after all tranfer the
information into other table columns. It was actually running but not
properly. Thus I changed the code until the structure of the logically
aproach was fitting to the scope. Let me forward you the code in consequence.
Please have a decent look on it and tell me your ideas.

The number of the runtime error is 13.

The information should be diverted from a table looking like the following
one:

ID OPTION1 OPTION2
123 3 x KK,GREAT COUNTRY jj TOTAL: 200,
AMOUNT: 20
..
..
..
..


VBA Code:

Option Compare Database

Sub TABLE()

Dim Db As DAO.Database
Dim RS1, RS2 As DAO.Recordset
Dim sSQL As String

sSQL = "select ID, Option1 as OP1, Option2 as OP2 "
sSQL = sSQL & "from Table1 where FIRSTVALUE is not null and SECONDVALUE is
not null"

Set Db = CurrentDb
Set RS1 = Db.OpenRecordset(sSQL, dbOpenDynaset)
Set RS2 = Db.OpenRecordset("TABLE2", dbOpenDynaset)

RS1.MoveFirst
While Not RS1.EOF

MOD_STRING1 = RS1!OP1
mod_string2 = RS1!OP2


If mod_string1 <> "" And mod_string2 <> "" Then
While InStr(mod_string1, "kk") > 0

RS2.AddNew
RS2!ID = RS1!ID
RS2!COUNT = Left(mod_string1, 1) * 1
RS2!AMOUNT = Trim(Mid(mod_string1, InStr(mod_string1, "x") +
1, InStr(mod_string1, "kk") - 2 - InStr(mod_string1, "x") + 1))
RS2!SUM = RS2!COUNT * RS2!AMOUNT


RS2!VALUE2 = Left(mod_string2, InStr(mod_string2, ",") - 1)
RS2!VALUE2 = Right(RS2!VALUE2, Len(RS2!VALUE2) -
InStr(RS2!VALUE2, " "))
RS2!VALUE3 = Trim(Mid(mod_string2, InStr(mod_string2, "x") +
1))


If InStr(RS2!VALUE3, ",") > 0 Then
RS2!VALUE3 = Left(RS2!VALUE3, InStr(RS2!VALUE3, ",") - 1)
End If
RS2.Update

mod_string1 = Trim(Mid(mod_string1, InStr(mod_string1, "jj")
+ 2))
If InStr(mod_string1, ",") > 0 And InStr(mod_string1, ",") <
5 Then
mod_string1 = Trim(Right(mod_string1, Len(mod_string1) -
InStr(mod_string1, ",")))
End If
'mod_string1 = Right(mod_string1, Len(mod_string1) -
InStr(mod_string1, ",") + 1)


If Len(mod_string2) >= 8 Then
mod_string2 = Right(mod_string2, Len(mod_string2) - 8)
End If

If InStr(mod_string2, "TOTAL:") <> 0 Then
mod_string2 = Mid(mod_string2, InStr(mod_string2,
"TOTAL:"))
End If

Wend
End If


RS1.MoveNext
Wend

RS1.Close
Set RS = Nothing

End Sub

Support will be greatly apreciated! Thank you very much in advance.

Yours Sincerely

malte

 

[kc-mass]

Declare your record sets on seperate lines. The way you have it
RS1 is being declared as a variant not a recordset.

Regards

Kevin

"BusyProfessorFromBremen"