Update/Append Records through a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a form to allow the user to enter data. However I would like the
form to check the table for a similar record based on one of the values the
user enters and prompt them to overwrite or abort, so they can choose whether
or not they want to overwrite the record. I would prefer to do this using a
button to start the process. I never tried to insert/append/update data from
an unbound form to a table before and not quite sure it is possible without
using a query. Can someone tell me if it is possible and how to go about it
a bit?
 
Hi,


You can append data directly through an SQL statement:

DoCmd.RunSQL "INSERT INTO tableName(f1, f2) VALUES(FORMS!myForm!controlF1,
FORMS!myForm!controlF2)"


or you can use a recordset (with an AddNew, then assign each fields, then
Update).


You can see if there is a record with a given value with a DCount:

if 0 <> DCount("*", "tableName" , "f1=FORMS!myForm!controlF1" ) then
... already present
else
... not in the table
end if


Sure, you can update an existing record, again through a recordset (open it,
find THE record to be updated, with DAO, start an Edit, assign each fields,
Update) or directly:


DoCmd.RunSQL "UPDATE tableName SET f1=FORMS!myForm!controlF1,
f2=FORMS!myForm!ControlF2 WHERE f3=FORMS!myForm!ControlF3"



Note that DoCmd recognize the syntax "FORMS!formName!ControlName", so you
don't need to add (nasty) delimiters, since you don't type "constants" in
the statement. That WON'T work, on the other hand, with CurrentDb.Execute.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top