Run time error in VBA Code

  • Thread starter Thread starter BusyProfessorFromBremen
  • Start date Start date
B

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
 
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"
 
Back
Top