Update records from unbound fields

[lachesis]

Does anyone know an easy way to update records from unbound fields??

Obviously I want to perform all the niceties before Updating: validate data, check for duplicates etc.

Also I have to be able to update existing records to new values, as well as Add New Records (ensuring they are not dupes).

Cheers

 

[shreked]

lachesis

here is an option

dim dbs as database
dim rst as recordset

set dbs = currentdb()

set rst = dbs.OpenRecordset("SELECT * FROM YourTable WHERE FieldName = '" & me.UnboundTextBox & "'&quot
'test if it a new record if the recordset is EOF then ADD else UPDATE
if rst.eof then
Set rst = dbs.OpenRecordset(("SELECT * FROM YourTable&quot
with rst
.addnew
!FieldName = Me.UnboundTextBox
.update
End With
rst.close
Else
with rst
.edit
!FieldName = Me.UnboundTextBox
.update
End With
rst.close
end if


See how this goes if you need more info let me know

 

[lachesis]

Thanks I was after the methods in the end/end with style.

However I have a deeper problem with the unbound fields. There are two of them: txtrcode and txtrname.

When a user edits an existing record (click an 'Edit' button) I draw the existing rcode, rname values into their respective unbound fields. The user then has the options to edit one or both of the values, however once they hit the 'Update' button I want to validate both values in txtrcode, txtrname against the current records.

If there is a dupe in one (or both) fields the user has to change the value of one (or both) fields. These values must then be re-tested for dupes.

So lets say I edit 731, Name1 and change the values to 732, Name1. But '732' already exist in the db, how do I force the user back to change the rcode to something other than 732?

cheers

 

[lachesis]

bump.