Public Function procUpdateQry_3a(sn$, rw%, bldgid$, leasid$, suitid$)
Dim rs, sqlstr$, conn As ADODB.Connection, ptbl$, tbl$, fld$, tdel%, ptbl2$, t%, tt%, nxttbl$, lsttbl$
Dim i%, ii%, endcol%
Dim sqlins1$, sqlins2$, addrec%
Dim ok%
'ptbl is first MRI table specified on sheet
'tbl--the MRI table ref from the current col is compared to this
'nxttbl is the col ref for the column with the next unmatching table (the next table to query)
'
Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.LockType = adLockPessimistic
Sheets(sn).Select
Set conn = OpenConnection()
If sn = "Lease Info" Then
Range("E" & Trim(Str(rw))).Activate
Else
Range("D" & Trim(Str(rw))).Activate
End If
ptbl = Left(Cells(4, ActiveCell.Offset(0, i).Column).Value, InStr(1, Cells(4, ActiveCell.Offset(0, i).Column), ".") - 1)
'If leasid = "003622" Then Stop
Debug.Print ptbl
If Range("BA" & Trim(Str(rw))).Value = "Del" Then
sqlstr = "DELETE FROM " & ptbl & " WHERE BLDGID='" & Trim(bldgid) & "' AND LEASID='" & Trim(leasid) & "'"
rs.Open sqlstr, conn
Else
endcol = False: addrec = False
Do While Not endcol
'At start of sheet ptbl contains first MRI Table ref
If ptbl = "SSQF" Then
sqlstr = "SELECT * FROM " & ptbl & " WHERE BLDGID='" & Trim(bldgid) & "' AND SUITID='" & Trim(suitid) & "' AND SQFTTYPE='" & Range("I" & Trim(Str(rw))) & "' AND EFFDATE='" & Range("AE" & Trim(Str(rw))) & "'"
Else
sqlstr = "SELECT * FROM " & ptbl & " WHERE BLDGID='" & Trim(bldgid) & "' AND LEASID='" & Trim(leasid) & "'"
End If
rs.Open sqlstr, conn
If rs.BOF And rs.EOF Then
''record count=0 therefore data for this table must be inserted...
'sqlins1 = "INSERT INTO " & ptbl & "(BLDGID, LEASID, "
'sqlins2 = " VALUES('" & Trim(bldgid) & "','" & Trim(leasid) & "'"
'create insert query
'find next tableref
'set ptbl, ii
' Stop
'Exit Do
ok = True
If UCase(ptbl) = "CPII" Then
If Len(Trim(Range("D" & Trim(Str(rw))))) = 0 Then
ok = False
End If
End If
If UCase(ptbl) = "PTWI" Then
If Len(Trim(Range("P" & Trim(Str(rw))))) = 0 Then
ok = False
End If
End If
If ok Then
rs.AddNew
addrec% = True
rs("bldgid") = bldgid
If UCase(ptbl) <> "SSQF" Then
rs("leasid") = leasid
'rs("sqfttype") = Trim(Range("I" & Trim(Str(rw))))
End If
If UCase(ptbl) = "SSQF" Then
rs("sqfttype") = Trim(Range("I" & Trim(Str(rw))))
End If
If UCase(ptbl) = "LATERMS" Or UCase(ptbl) = "LATERMS2" Or UCase(ptbl) = "LATERMS3" Or Left(UCase(ptbl), 2) = "LA" Or UCase(ptbl) = "SSQF" Then
rs("suitid") = suitid
End If
If UCase(ptbl) = "SSQF" Then
(I get the error here) rs("effdate") = Range("AE" & Trim(Str(rw)))
End If
Else
Exit Function
End If
End If