If .,AND ... Then failure
 

I have a Spreadsheet for Staff to record their Voice Messages &
Followups. They use UserForms to input the initial data, which could
be from either of two sources Employers or Claimant. Once they have
the record on the spreadsheet they can update that record, with
additional data and then Update that record to a Database(elsewhere).

My problem now is if they get a call from the employer and post the
record, which uses the Claimants SSN as the search criteria to Update
the Recod on the Database; THEN a Claimant calls in and we attempt to
create a new record. Since the SSN are the same the database attempts
to update the original record instead of adding a destict record (one
for the Employer and one for the Claimant).

Here is a protion of the Code:

LG = Range("F" & 1).Value
Qry = "LOG" & LG ' sets the Query

R = ActiveCell.Row "This row has the data to be Added or Updated

FDate = Range("C" & R).Value ' Initial Date of Call
FSSN = Range("F" & R).Value ' The Claimants SSN
FFrom = Range("E" & R).Value ' Call from one of two (Employer or
Claimant)
adjlog = "\\fld035728\Team\AdjLog.mdb"
Set dbs = OpenDatabase(adjlog)
Set qd = dbs.CreateQueryDef(Qry)
qd.SQL = "SELECT * FROM TEntry " _
& "WHERE Login='" & LG & "';"
Set rs = dbs.OpenRecordset(Qry)

notfound = False
rs.MoveFirst
Do
If FSSN = rs.Fields("SSN") Then
notfound = True
rs.Edit
rs.Fields("Complete") = Range("A" & R).Value
rs.Fields("Login") = Range("F" & 1).Value
rs.Fields("From") = Range("E" & R).Value
rs.Fields("Date") = Range("C" & R).Value
rs.Fields("Time") = Range("D" & R).Value
rs.Fields("SSN") = Range("F" & R).Value
...... etc.
Note: if the record is not found then it moves the rs. Add function,
etc.

I want the If statment to be like:
If FSSN = rs.Fields("SSN") AND FDate = rs.("Date")AND _
FFrom = rs.("From")Then

But, this fails. and it ADDs a new record everytime. The If statement
with one item works as is listed above in the code, but I need to add
items to narrow the search? Thanks

If .,AND ... Then failure
 

Pete,

First check and make sure you have spaces where they need to be
If FSSN = rs.Fields("SSN") AND FDate = rs.("Date")AND _
FFrom = rs.("From")Then
s/b
If FSSN = rs.Fields("SSN") AND FDate = rs.("Date") AND _
FFrom = rs.("From") Then

Next you'll need to verify that the date doesn't have minutes/seconds in
order to get an exact match (you can check this by formatting as a number
with decimals - you don't want decimals); and make sure that you get an
exact match on the "From".

To double check - you might want to break up your if's and test...
If FSSN = rs.Fields("SSN") then
MsgBox "SSN match"
If FDate = rs.("Date") then
MsgBox "Date match"
If FFrom = rs.("From") then
MsgBox "From match"
--
sb
"Pete T" wrote in message
om...
I have a Spreadsheet for Staff to record their Voice Messages &
Followups. They use UserForms to input the initial data, which could
be from either of two sources Employers or Claimant. Once they have
the record on the spreadsheet they can update that record, with
additional data and then Update that record to a Database(elsewhere).

My problem now is if they get a call from the employer and post the
record, which uses the Claimants SSN as the search criteria to Update
the Recod on the Database; THEN a Claimant calls in and we attempt to
create a new record. Since the SSN are the same the database attempts
to update the original record instead of adding a destict record (one
for the Employer and one for the Claimant).

Here is a protion of the Code:

LG = Range("F" & 1).Value
Qry = "LOG" & LG ' sets the Query

R = ActiveCell.Row "This row has the data to be Added or Updated

FDate = Range("C" & R).Value ' Initial Date of Call
FSSN = Range("F" & R).Value ' The Claimants SSN
FFrom = Range("E" & R).Value ' Call from one of two (Employer or
Claimant)
adjlog = "\\fld035728\Team\AdjLog.mdb"
Set dbs = OpenDatabase(adjlog)
Set qd = dbs.CreateQueryDef(Qry)
qd.SQL = "SELECT * FROM TEntry " _
& "WHERE Login='" & LG & "';"
Set rs = dbs.OpenRecordset(Qry)

notfound = False
rs.MoveFirst
Do
If FSSN = rs.Fields("SSN") Then
notfound = True
rs.Edit
rs.Fields("Complete") = Range("A" & R).Value
rs.Fields("Login") = Range("F" & 1).Value
rs.Fields("From") = Range("E" & R).Value
rs.Fields("Date") = Range("C" & R).Value
rs.Fields("Time") = Range("D" & R).Value
rs.Fields("SSN") = Range("F" & R).Value
...... etc.
Note: if the record is not found then it moves the rs. Add function,
etc.

I want the If statment to be like:
If FSSN = rs.Fields("SSN") AND FDate = rs.("Date")AND _
FFrom = rs.("From")Then

But, this fails. and it ADDs a new record everytime. The If statement
with one item works as is listed above in the code, but I need to add
items to narrow the search? Thanks