Updating fields not displayed on a form. 
Author Message
 Updating fields not displayed on a form.

I have a subform displaying several records from a table.  The subform does
not display all of the key fields to identify the record.  When one of the
displayed fields is changed, I need to change a field not displayed on the
form.  I know I can use an SQL statement to update the record, but how do I
specify which record to update?  Is this the best way to do it, or is their
a better way?

Thanks,
Tom



Sun, 03 Dec 2000 03:00:00 GMT  
 Updating fields not displayed on a form.

I'm not sure exactly how you are using the subform. Whether
it is bound to the main form via the LinkChildFields /
LinkMasterFields properties or it is unbound ?

Assuming it is bound.
Probably the easiest way is to place a text box control on
the subform. Set the controlSource to the field you want to
change. Set the visible property to False. This makes the
field available for editing via VBA yet it does not show on
the form.

In the BeforeUpdate or AfterUpdate property of the
"dependent" control place your VBA code that will
conditional change the value of the "invisible control".

An alternative, but much busier code, is to use the forms
bookmark property to locate the same record in a recordset
clone. However during editing processes you can run into
record(page) lock problems. This is true in a single user
environment. The form is editing the record and will
generally have placed a lock on it which makes it
unavailable for editing elsewhere. Setting the locking to
"Optimistic" will allow dual editing, but needs to be
caefully dealt with.

Much easier to use the invisible control and let the same
"edit lock" change the records values.

Hope this helps.

Bill McKnight


Quote:
>I have a subform displaying several records from a table.
The subform does
>not display all of the key fields to identify the record.
When one of the
>displayed fields is changed, I need to change a field not
displayed on the
>form.  I know I can use an SQL statement to update the

record, but how do I
Quote:
>specify which record to update?  Is this the best way to do
it, or is their
>a better way?

>Thanks,
>Tom



Sun, 03 Dec 2000 03:00:00 GMT  
 Updating fields not displayed on a form.

Tom,

You can use an update query with the key fields specified in the Where clause
(the Criteria row of the Query designer).  Alternately, you can write some code
that uses Seek or FindFirst to get to the desired record.  Next you can do the
updating necessary.

It is important to add a Forms!YourFormName.Requery immediately after changing
the data.  You may want to grab the key field values before you do a requery so
you can get back to the same record after the requery.  This will allow the
data update to happen without disrupting the users train of thought, since the
requery will reset the forms current record to the first record in the
dataset.  If you don't requery then you will get an error stating that another
session has changed the data behind the form.

Hope it is helpful.

Michael A. Cordova
21st Century Technologies, Inc.
http://www.21stsoft.com

Voice: (303) 744-21st  (744-2178)
Fax:   (303) 871-0575
-----------------------------------------

Quote:

> I have a subform displaying several records from a table.  The subform does
> not display all of the key fields to identify the record.  When one of the
> displayed fields is changed, I need to change a field not displayed on the
> form.  I know I can use an SQL statement to update the record, but how do I
> specify which record to update?  Is this the best way to do it, or is their
> a better way?

> Thanks,
> Tom



Mon, 04 Dec 2000 03:00:00 GMT  
 Updating fields not displayed on a form.

<< I have a subform displaying several records from a table.  The subform does
not display all of the key fields to identify the record.  When one of the
displayed fields is changed, I need to change a field not displayed on the
form.  I know I can use an SQL statement to update the record, but how do I
specify which record to update?  Is this the best way to do it, or is their
a better way? >>

If your subform's recordsource is a query, make sure the field to be updated is
included in the sql. It needn't be displayed, but you need access to it. Then
just set it's value, like:

Me![Field Name] = <new value>

-------
Regards  Stuart McCall

"Time flies like an arrow,
Fruit flies like a banana"



Mon, 04 Dec 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. How to DIsplay and Update a NULL Field???

2. Display of ActiveX controls not updated

3. datagrid display not updating

4. ADO 2.6 update Not displaying EULA

5. Updating display from form.

6. cookie value does not display in password textbox field

7. Bound combo box that does not display key field

8. Data Fields Not displaying in Combo boxes

9. field data not displaying from recordset

10. SQL Server2K Money Field via ODBC not displaying any values

11. Tabs not displaying in text field?

12. SQL Expression Field does not display

 

 
Powered by phpBB® Forum Software