Update a field in a "generic" function

[Martureo Bob]

Hi:

I'm a newbie and have implemented some code to update a database field using
DAO 3.6 --- that code is working. For example, the following will set the
field named [Receipt Comment Identifier] to a value
of 1:

' These "DIM" statements appear in the Declarations section
Dim daoDatabase As DAO.Database
Dim daoRecordSet As DAO.Recordset


' This command to open the database is done once at the beginning
' In the following, note that the path to database
' is passed in shortcut after /cmd
Set daoDatabase = OpenDatabase(Command)


' Code like this appears many times throughout the module for various
fields
Set daoRecordSet = daoDatabase.OpenRecordset("Report Options Table")
With daoRecordSet
' Enter edit mode
.Edit
' Change data in edit buffer
' Do not print the "tear off slip"
![Receipt Comment Identifier] = 1
' Save changes and close the recordset
.Update
.Close
End With


In order to streamline the module, here's what I'm trying to accomplish...
I want to create a Function within the module to which I can pass the table
name (like "Report Options Table"), the field name (like [Receipt Comment
Identified]) and the value to be set (like 1). This sub would then perform
something like the above sequence to perform the desired database update.
Something like this:

Private Function Update_Field ( _
Table_Name as Variant, _
Field_Name as Variant, _
Value_Name as Variant _
)
Set daoRecordSet = daoDatabase.OpenRecordset(Table_Name)
With daoRecordSet
' Enter edit mode
.Edit
' Change data in edit buffer
!Field_Name = Value_Name '<--this is invalid
' Save changes and close the recordset
.Update
.Close
End With
End Function


But I cannot figure out how to code the line that I marked above as
"invalid" --- the system is looking for a field named [Field_Name] rather
than looking in the Field_Name variable to find the field name. I'm sure
this is something simple --- but I'm still fairly new to VBA coding.

Thanks in advance....

Bob

 

[Martureo Bob]

Thanks Rick --- you did it again!

Here's my reason for coding in this manner:

I have an application deployed with three components:

1) A front end MDE --- used by both new users and by existing users who are
performing a version upgrade.

2) An EMPTY back end MDB only used by new users --- this is their "starter"
database. The application's "data tables" all start off empty. But this
database also contains three "control tables" each with one record
containing various fields. The entire set of these fields define the
processing options for the application. These are initially set to default
values, but are changed as the user executes various forms within the
application.

3) A special MDB only used by existing users that they must execute as the
last step of a
version upgrade process --- I call this the "Database Upgrade Processor."
This MDB adds new (empty) tables, new relationships, new field definitions,
etc., etc. to their existing back end MDB. The code in question by my post
is within this component!

This Database Upgrade Processor does not muck with the user's data. It only
defines new stuff for the application to use.

But if a new version of the application happens to also require a new field
in any of the three "control tables" then I also need to set a value to that
field so that when the user opens the application after the upgrade it will
start off "happy" (this may not be the same as the default value in the
starter database).

The front end and the back end each has its version number set in control
record tables. In the Database Upgrade Processor, I look at each and
determine what version they are upgrading from (the version number in their
existing back end MDB) and what version they are upgrading to (the version
number in the front end MDE). The Database Upgrade Processor has one little
chunk of code for each version I've deployed --- starting with the earliest
version I created, up through the one most recently distributed. When
executing, this Processor starts at the chunk for upgrading from their back
end's current version, and applies one chunk at a time (applying the upgrade
for one version at a time), until the desired version (the version number of
the front end) is reached. If it completes without error, it then changes
the version number within the back end MDB to be equal to the version number
within the front end MDE.

BTW, in the front end MDE's OnOpen event there's test to compare its version
number with the version number of its back end MDB --- the OnOpen event will
close the application if these are unequal (indicating that a version change
occurred but that the Database Upgrade Processor did not run or did not
complete successfully).

When I come up with a new version of the application, besides changing the
application I also update the starter database and add one more "chunk" to
the Database Upgrade Processor. In development, I actually start off by
making the changes to the starter database first (after the design work is
complete). Then reflect those changes within the Database Upgrade Processor
by adding another "chunk" of code. Then I run the Database Upgrade
Processor against my test database. Then I perform the coding additions /
changes to the application. If I need to change something (i.e., faulty
design), I restore the test database, modify the starter database, reflect
that in the Database Upgrade Processor, and run it again. So far, it's
worked very well!

I hope this all made sense --- I usually ramble (obviously).

Bob.

Martureo Bob wrote:
[snip]I want to create a Function within the module to which

I can pass the table name (like "Report Options Table"), the field
name (like [Receipt Comment Identified]) and the value to be set
(like 1). This sub would then perform something like the above
sequence to perform the desired database update. Something like this:

Private Function Update_Field ( _
Table_Name as Variant, _
Field_Name as Variant, _
Value_Name as Variant _
)
Set daoRecordSet = daoDatabase.OpenRecordset(Table_Name)
With daoRecordSet
' Enter edit mode
.Edit
' Change data in edit buffer
!Field_Name = Value_Name '<--this is invalid
' Save changes and close the recordset
.Update
.Close
End With
End Function


But I cannot figure out how to code the line that I marked above as
"invalid" --- the system is looking for a field named [Field_Name]
rather than looking in the Field_Name variable to find the field
name. I'm sure this is something simple --- but I'm still fairly new
to VBA coding.

Instead of...

!Field_Name

...use...

!Fields(Field_Name)

BTW is there any reason you don't just use an update query? Recordsets are
seldom the best method for editing records.