Using a form to update some records ?

[ZBC]

I have two tables that have a one (detail.db) to many (reserve.db)
relationship.
The field that relates the two tables unique and not one of the fields
to be updated.
I want to use records in the reserve.db to update the detail.db on a
periodic basis.
There are 5 fields in the detail.db that need to be updated based on
data contained in the reserve.db.
During the update process, there will be some math and logical decisions
that need to be made ... Is some cases, it will be merely a replacement
of data.

I would appreciate some suggestions as how to approach this.
Examples would be appreciated.

Bob

 

[John Vinson]

I have two tables that have a one (detail.db) to many (reserve.db)
relationship.
The field that relates the two tables unique and not one of the fields
to be updated.
I want to use records in the reserve.db to update the detail.db on a
periodic basis.
There are 5 fields in the detail.db that need to be updated based on
data contained in the reserve.db.
During the update process, there will be some math and logical decisions
that need to be made ... Is some cases, it will be merely a replacement
of data.

I would appreciate some suggestions as how to approach this.
Examples would be appreciated.

Well, an Update query would be appropriate if you're in fact updating
one table from another table. Forms are good when you want user input
to a table; you could put a command button on the form to run the
queries.

Your question is phrased so generally that it's all but impossible to
give a specific answer. "some math and logical decisions" isn't very
easy to answer!

I do have two real concerns here: you have a .db extension. Access
doesn't use .db files (though it can link to dBase .dbf files); are
these tables within a single database? And of more concern, it really
sounds like you're storing data redundantly; are you? if so, why?

 

[ZBC]

John,
Thanks for your response ...
I made a mistake by using the .db for the tables; they were originally
called that before they were imported to Access ... SORRY.
I am using one table to update the information in the other table.
The Reserve table is only a 'temporary' holding area of the update data
information.
I understand how to put a command button on a form, but have never
activated a update query from a command button.
I am using query as a source for part of the data present on the Reserve
form that gathers some of the update information.
The rest of the update information is entered by the user in textboxes.
I have not used an 'update query' before.
The math that I am referring to will be something like taking a taking a
percentage of one number and adding or replacing the values in an
existing field of the detail table if the value in the detail falls
within a specified range.
Are there wizards to help me?
Example code of such things as changing a field in one table based on
values from another table via an update query might get me started.
Thanks very much for your efforts!

Bob

 

[John Vinson]

John,
Thanks for your response ...
I made a mistake by using the .db for the tables; they were originally
called that before they were imported to Access ... SORRY.
I am using one table to update the information in the other table.
The Reserve table is only a 'temporary' holding area of the update data
information.

Are you "updating" - changing values of fields in existing records -
or "inserting" - adding new records to the target table? These are
different queries, an Update query and an Insert query respectively.

I understand how to put a command button on a form, but have never
activated a update query from a command button.

The button wizard will offer that as an option; or (probably better in
this instance) you can use VBA code to open and execute a Querydef
object.

I am using query as a source for part of the data present on the Reserve
form that gathers some of the update information.
The rest of the update information is entered by the user in textboxes.

Are you intending to update the target table, one record at a time? or
in bulk?

I have not used an 'update query' before.

It's right in the online help. It is best suited for updating multiple
records at a time, but for that reason may not be appropriate here.

The math that I am referring to will be something like taking a taking a
percentage of one number and adding or replacing the values in an
existing field of the detail table if the value in the detail falls
within a specified range.

You're aware, I presume, that it's generally considered A Bad Idea to
store such derived data in any table? Sometimes (and this looks like
it may be one) you need to do so, but it's always sort of a last
resort!

Are there wizards to help me?

Yes. The command button wizard has an option to run a query, for
instance.

Example code of such things as changing a field in one table based on
values from another table via an update query might get me started.
Thanks very much for your efforts!

No code is needed: just a Query. If you have TableA joined to TableB
by a field with a unique index in TableA (such as the Primary Key),
you can usually create a Query joining the two tables; change it to an
Update query using the query type icon; and put

[TableB].[fieldX]

on the Update To line under TableA.FieldY in order to update FieldY
with the corresponding value in FieldX.

 

[ZBC]

Are you "updating" - changing values of fields in existing records -
or "inserting" - adding new records to the target table? These are
different queries, an Update query and an Insert query respectively.

changing values of fields in existing records

The button wizard will offer that as an option; or (probably better in
this instance) you can use VBA code to open and execute a Querydef
object.

I have written VB6 code about a year ago ... I am assuming they are similar?

Are you intending to update the target table, one record at a time? or
in bulk?

At the moment, I am assuming I will be updating about 10-20 records ...
all of them by the same Update Query in a single execution.
Each record in the Reserve table will apply to only 1 record in the
detail table.

It's right in the online help. It is best suited for updating multiple
records at a time, but for that reason may not be appropriate here.




You're aware, I presume, that it's generally considered A Bad Idea to
store such derived data in any table? Sometimes (and this looks like
it may be one) you need to do so, but it's always sort of a last
resort!

I'm not sure I see anything wrong in what I am doing. The Reserve table
is a means of gathering changes to the detail table which will be
'updated'. Once the changes are made to the detail table, the Reserve
table could go away, but may possibly be kept as a matter of record as
to the changes which were made on that date.

Are there wizards to help me?

Yes. The command button wizard has an option to run a query, for
instance.

Example code of such things as changing a field in one table based on
values from another table via an update query might get me started.
Thanks very much for your efforts!

No code is needed: just a Query. If you have TableA joined to TableB
by a field with a unique index in TableA (such as the Primary Key),
you can usually create a Query joining the two tables; change it to an
Update query using the query type icon; and put

[TableB].[fieldX]

on the Update To line under TableA.FieldY in order to update FieldY
with the corresponding value in FieldX.

At what point or where are any calculations preformed that lead up to
generation of the value used for FieldY?

 

[John Vinson]

At what point or where are any calculations preformed that lead up to
generation of the value used for FieldY?

As calculated fields in the Update Query. Instead of just naming a
field, you can put an almost arbitrarily complex expression on the
Update To line. (Well, 1024 bytes is the limit... but you can write a
VBA function in a Module and call it).