I am trying to build a table based on the values in another one. At
the same time I have to parse out the multi values in their text field
to make multi records out of one value.

Dim rs As Recordset
Set Rs = db.OpenRecordset("sheettesting")
Dim rst As Recordset
Set rst = db.OpenRecordset("tblTEST")

Rs.MoveFirst
Do While Not Rs.EOF


If Not IsNull(Rs!valuetest) Then newstr = Rs! valuetest
posit = InStr(newstr, "/")

If posit > 0 Then

parse1 = Mid(newstr, 1, posit - 1)
parse2 = Mid(newstr, posit + 1, Len(newstr) - Len(parse1))

With rst
.AddNew
!ID = Rs!idnum
!stringtest = parse1
.Update

' later I will loop this for multi values in the string going to
parse2 ….etc


Else

With rst
.AddNew
!ID = Rs!idnum
! stringtest = newstr
.Update
End If


Rs.MoveNext

Loop
Rs.Close
rst.Close


it stops at ELSE saying else without if
I am not sure about with rst and .update if they somehow mess up the
if else statement.

Can this type of open 1 read it write to another work this way. I
don't see why not.

Ok since I was updating these records and creating multible records
based on the values in 1 record this is what I did.


Rs.MoveFirst
Do While Not Rs.EOF


If Not IsNull(Rs!values) Then
newstr = Rs!values
tmpid = Rs!id

Do While Len(newstr) > 0
posit = InStr(newstr, "/")
If posit > 0 Then

parse1 = Mid(newstr, 1, posit - 1)
parse2 = Mid(newstr, posit + 1,
Len(newstr) - Len(parse1))

antivalue = parse1
newstr = parse2

Call antiupdater(tmpid, antivalue)
Else
antivalue = newstr
newstr = ""

Call antiupdater(tmpid, antivalue)

End If
Loop
End If



Rs.MoveNext
Loop
Rs.Close
End Sub




Private Function antiupdater(ider As Integer, antival As String) As
String

Dim db As Database
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("tblnewTEST")
Dim tmpid As Integer
Dim antivalue As String



With rst
rst.AddNew
!idnum = ider
!antibiotics = antival
rst.Update

End With
rst.Close
End Function



this seemed to work fine so I am worried I must have done something
wrong LOL

anyway I was trying to do too much, I am just going to copy this code
and use it to update the other section instead of trying to update 2
tables based on multi values found in 2 different variables.

I guess I was just trying to do too much and nothing looked right.
Does this make since to anyone else?