back to basics, adding and editing Table records from a Form, proper procdures

[RC]

I have an Access 2002 database with many tables and forms (but just to
keep things simple, let's say the DB has one Table "Table1" and one
Form "Form1").
I have managed to cobble together so much complex code to add records
and edit records I need to step back and ask you all what is the
proper way to do this. The database is DAO. The table has Columns
SerialNumber, CPU, HardDrive and Model. I am trying to use a Form to
fill out the Table values. Each SerialNumber needs the CPU, HardDrive
and Model to be specified. I am using a Form with the Form's record
source set to Table1. On the Form I dragged and dropped the
SerialNumber, CPU, HardDrive and Model fields from the fields list to
the Form.
What is the proper way to add a new record and also what is the proper
way to update an existing record. Should the SerialNumber form field
have a FindFirst set to look for the Serial Number and if the
SerialNumber is not found, then ?? rs.addnew? rs.edit then
rs.Fields("CPU" ) = me.CPU.value? etc., and then rs.update? Most of
the information will be the same from Serial Number to Serial Number
so it would be nice if the Form fields could retain the last value
that was entered in them so that when the next serial number is typed
into the SerialNumber box, you would only need to change whatever is
different not enter a value in each form field again. The table
field/columns are all text. Should I use code like this?
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Me.Recordset
rs.FindFirst "[SerialNumber] = " & Me.SerialNumber Box

If rs.NoMatch Then ??? '(the rs.edit? or rs.addnew?
Me.Bookmark = rs.Bookmark ??? "Do I need to this? Where does it
belong?
rs.edit
rs.Fields("Seri alNumber") = me.SerialNumber .value
rs.Fields("CPU" ) = me.CPU.value
rs.Fields("Hard Drive") = me.HardDrive.va lue
rs.Model("Model ") = me.Model.value
rs.update

what about rs.movelast ??
rs.close
set rs = nothing

Thanks for the help.

[Pieter Linden]

rc*********@yah oo.com (RC) wrote in message news:<3c******* *************** ***@posting.goo gle.com>...

I have an Access 2002 database with many tables and forms (but just to
keep things simple, let's say the DB has one Table "Table1" and one
Form "Form1").
I have managed to cobble together so much complex code to add records
and edit records I need to step back and ask you all what is the
proper way to do this. The database is DAO. The table has Columns
SerialNumber, CPU, HardDrive and Model. I am trying to use a Form to
fill out the Table values. Each SerialNumber needs the CPU, HardDrive
and Model to be specified. I am using a Form with the Form's record
source set to Table1. On the Form I dragged and dropped the
SerialNumber, CPU, HardDrive and Model fields from the fields list to
the Form.
What is the proper way to add a new record and also what is the proper
way to update an existing record. Should the SerialNumber form field
have a FindFirst set to look for the Serial Number and if the
SerialNumber is not found, then ?? rs.addnew? rs.edit then
rs.Fields("CPU" ) = me.CPU.value? etc., and then rs.update? Most of
the information will be the same from Serial Number to Serial Number
so it would be nice if the Form fields could retain the last value
that was entered in them so that when the next serial number is typed
into the SerialNumber box, you would only need to change whatever is
different not enter a value in each form field again. The table
field/columns are all text. Should I use code like this?
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Me.Recordset
rs.FindFirst "[SerialNumber] = " & Me.SerialNumber Box

If rs.NoMatch Then ??? '(the rs.edit? or rs.addnew?
Me.Bookmark = rs.Bookmark ??? "Do I need to this? Where does it
belong?
rs.edit
rs.Fields("Seri alNumber") = me.SerialNumber .value
rs.Fields("CPU" ) = me.CPU.value
rs.Fields("Hard Drive") = me.HardDrive.va lue
rs.Model("Model ") = me.Model.value
rs.update

what about rs.movelast ??
rs.close
set rs = nothing

Thanks for the help.

My guess is that you could probably do most of this by just changing
the default values for the relevant fields right on the form. Doing
all this is probably more trouble than it's worth...
Source: http://www.mvps.org/access/forms/frm0012.htm

Carry current value of a control to new records
Author(s)
Dev Ashish

(Q) How do I carry forward the current value of a control so that
it's automatically entered for all new records?

(A) To use the curent control value for new records, you need to
assign it to the defaultvalue of the control. For example something
like

'******** Code Start **********
const cQuote="""" 'Thats two quotes
me!Control.Defa ultValue = cQuote & me!Control.Valu e & cQuote
'******** Code End **********

would carry the current value of the control forward if you place
this code behind a command button to conditionally run it.
HTH
Pieter