go to existing record

[joemeshuggah]

i have a form in access that associates an owner to a device. i am trying to get the form to behave so that if i try to enter a new record for an existing owner, it tells me that there is already a record for that owner (in which case i would be forced to go to that record and make updates rather than inserting a new record). the code below works fine...but when i go the existing record to make updates, the same test is run and i cannot make the updates. how do i get around this?

' Query to see if there are any records with the OWNER entered
Set rsResult = dbsCurrent.OpenRecordset _
("select count(*) as CNT from (select * from tblCurrOWNER where OWNER = '" & Me.OWNER & "') as tbl")
Result = rsResult![CNT]

'If there are, deny transaction
If Result <> 0 Then
MsgBox "The OWNER already has a current record. This change will not be made."
Me.Undo
rsResult.Close
GoTo EndIt
End If

 

[jackd]

In your If statement, you only deal with <>0. In fact you close the recordset.

eg: your code logic:
If record exists
Close and exit

You could have

If record exists
Show message "a record already exists"
else (record doesn't exist)
create a record
Endif

I'm not clear on Update the record???

Do you have tables for People , Devices, a junction table for OwnsDevice?
Can a person own multiple devices?