Updating a table from a Form...

[Guest]

What is the easiest way to allow changes to certain fields in a table from a
form, without erasing other data???

My table contains an EmployeeID, Cert1, Cert1Exp (date), Cert2, Cert2Exp,
.... I wish to open a form (or otherwise) and only have to update what needs
to be changed...

Does that make sense???

 

[Damon Heron]

Just make a form (use the form wizard if you are unsure) and you can make
changes to any data you want without erasing anything. I think what you may
be saying is you want to be able to change the date, for instance, but not
allow the user to change some of the other fields -- if that is the case, in
the field properties (on the form) lock the field to prevent changes.

HTH
Damon

 

[Guest]

My original post made it sound like I was trying to protect data. My
apologies. Maybe if I worded it better...

Let's say I have 20,000 employees, each with 20 certifications. One of
those employees comes into my office and brings me in an updated certificate
with a new expiration date. What is the easiest way to locate his record,
and make the changes???

 

[Damon Heron]

That's a different question - On a form you could put a combo box with a
query to the employee table - something like EmployeeID, EmployeeLast Name,
etc. and then you can type in the combobox the first letters of the last
name and your selection will come up.
20,000 records is a lot, and the speed of this operation depends on a lot of
differing factors, whether the table has indexed fields, etc.
If it is too slow, you can use additional filtering criteria to limit the
number of employees displayed.

Damon

..

 

[John W. Vinson]

Let's say I have 20,000 employees, each with 20 certifications. One of
those employees comes into my office and brings me in an updated certificate
with a new expiration date. What is the easiest way to locate his record,
and make the changes???

You haven't shared any information about your table structure, so it's
a bit hard to be specific; but if you have a table of employees, you
can create a Form bound to that table. It might have a Subform for the
certificates, or you might have a field for certificates in the
employee table - I can't see your table from here to see!

If you use the Toolbox Combo Box Wizard, you can put an unbound Combo
Box on the form using the option "Use this combo to find a record".
With this, you can pick an employee by name (or name plus enough other
fields to uniquely identify an employee); the form will jump to that
record and you can then edit that field.

John W. Vinson [MVP]

 

[Damon Heron]

Not sure of your expertise, so here are some more details - here is what I
would do, not the only way.
1. Create a form with the record source as the employee table.
2. Add an unbound combobox, with a row source query to employeeID, Name
2A. Add a textbox, with controlsource set to employeeID. set
visible=false.
3. In the after update event of the combobox, (which I call cboFind) enter:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![cboFind], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
4. Add a subform with the Link Master and child fields set to EmployeeID
5. Subform source object is employee table. Add the fields you will be
updating.

 

[Damon Heron]

Or- you could do it the simple way (John Vinson) -
I made it harder than it needs to be. Guess those wizards really are
helpful!!!

Damon

Not sure of your expertise, so here are some more details - here is what I
would do, not the only way.
1. Create a form with the record source as the employee table.
2. Add an unbound combobox, with a row source query to employeeID, Name
2A. Add a textbox, with controlsource set to employeeID. set
visible=false.
3. In the after update event of the combobox, (which I call cboFind)
enter:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![cboFind], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
4. Add a subform with the Link Master and child fields set to EmployeeID
5. Subform source object is employee table. Add the fields you will be
updating.

My original post made it sound like I was trying to protect data. My
apologies. Maybe if I worded it better...

Let's say I have 20,000 employees, each with 20 certifications. One of
those employees comes into my office and brings me in an updated
certificate
with a new expiration date. What is the easiest way to locate his
record,
and make the changes???

 

[Rolls]

You update a field, first by locating the record you want to change, then
replacing field(s) in that record. Simple to see using the QBE window.
Add your table to a form, limit records to those you want to update.