Private Sub Command0_Click()
Dim data As String, count As Integer, count2 As Integer, Start As Integer, finish As Integer, rstable As Object
Dim recount As Integer, innerstring As Integer, start2 As Integer, records As Integer, rs As Object, x As Integer
Dim first As Integer, fieldcount As Integer, i As Integer
On Error GoTo errorcatch
Set rstable = CurrentDb.OpenRecordset("Data")
Set rs = CurrentDb.OpenRecordset("Sheet2")
fieldcount = rs.Fields.count
rs.MoveLast
recount = rs.RecordCount
rs.MoveFirst
For records = 1 To 1 'recount
x = x + 1
rstable.AddNew
With rs
For i = 1 To fieldcount - 1
If Not IsNull(.Fields(i)) Then
data = .Fields(i)
If i = 1 Then
start2 = InStr(1, data, ")")
Start = InStr(1, data, "- ")
finish = InStr(Start, data, " ")
rstable.Company = Right(data, Len(data) - finish)
rstable.[County/City] = Mid(data, start2 + 1, finish - 1)
End If
If i = 2 Then rstable.Address = data
If Left(data, 5) <> "PHONE" Then rstable.Address = data
If Left(data, 5) = "PHONE" Then rstable.PHONE = Right(data, 11)
If Left(data, 3) = "FAX" Then rstable.FAX = Right(data, 11)
If Left(data, 5) = "EMP: " Then rstable.EMPLOYEES = Right(data, Len(data) - 5)
If Left(data, 5) = "SIC: " Then rstable.SIC = Right(data, Len(data) - 5)
If Left(data, 4) = "HQ: " Then rstable.[HQ:] = Right(data, Len(data) - 4)
If Left(data, 5) = "WEB: " Then rstable.WEB = Right(data, Len(data) - 5)
If Left(data, 6) = "SALES " Then rstable.SALES = Right(data, Len(data) - 6)
If Left(data, 7) = "SQ FT: " Then rstable.[SQ FT] = Right(data, Len(data) - 7)
innerstring = InStr(1, data, "P.O. BOX")
If innerstring <> 0 Then rstable.PO = Right(data, Len(data) - (innerstring + 8))
End If
Next i
End With
rstable.Update
rstable.Bookmark = rstable.LastModified
rs.MoveNext
Next records
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing
MsgBox "added " & x & " records"
Exit Sub
errorcatch:
MsgBox records & " " & i & " " & x & " " & Err.Description & " " & fname
End Sub