Adding record to table

[RD]

Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD

 

[Brian]

First, why not just give the form a RecordSource of the table you want, make
the text boxes bound, and go to a new record when you open the form (or make
it a DataEntry form so that it only allows additions, if this is what you
want)? That way, the data is saved automatically when you close the form,
navigate to another record, or issue the save command via the VBA code on a
button.

If you really want to use unbound boxes, then have a button that opens an
append query. In the queyr builder, the various fields will look like this:

Expr1: [Forms]![frmForm]![ctrlControl1] (to be appended to Field 1 in the
table)
Expr2: [Forms]![frmForm]![ctrlControl2] (to be appended to Field 2 in the
table)

This takes the contents of the various text boxes on frmForm (the name of
your current form) and makes them the basis of the various fields in your
append. I still think it is much easier to let Access handle the saving of
the record natively by using a form RecordSource and binding the controls.

 

[RD]

The data is confidential info. One of the requirements of this app is that it
has a secure audit trail of historical information. Once a record is entered
it's as good as written in stone. If a record needs to be edited, a new record
is written with a date stamp and an UpdatedBy field and the old one archived.
In this design, none of the forms or their controls will be directly bound to
tables or fields. There can be no way for someone to go in and actually change
what's been written. From the user's POV it will *look* like they are changing
a record but they won't be. For reporting purposes the datstamp will serve as
the identifier of the most current version of a record.

Now that we can stop trying to redesign the app we can debate the pros and cons
of writing a record using Recordset.AddNew vs. an Append Query.

For instance, since one of these fields is not required I was wondering how the
append query would handle a Null value.

First, why not just give the form a RecordSource of the table you want, make
the text boxes bound, and go to a new record when you open the form (or make
it a DataEntry form so that it only allows additions, if this is what you
want)? That way, the data is saved automatically when you close the form,
navigate to another record, or issue the save command via the VBA code on a
button.

If you really want to use unbound boxes, then have a button that opens an
append query. In the queyr builder, the various fields will look like this:

Expr1: [Forms]![frmForm]![ctrlControl1] (to be appended to Field 1 in the
table)
Expr2: [Forms]![frmForm]![ctrlControl2] (to be appended to Field 2 in the
table)

This takes the contents of the various text boxes on frmForm (the name of
your current form) and makes them the basis of the various fields in your
append. I still think it is much easier to let Access handle the saving of
the record natively by using a form RecordSource and binding the controls.

Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD

 

[Brian]

Fair enough. I was just suggesting use of a native Access feature that might
save some time over the use of the query (then again, it might not). Binding
does not necessarily imply that existing records can be accessed via the
form, only that the form's can communicate (write and/or read, depending on
how you as a programmer set the AllowAdditions, AllowEdits, AllowDeletions,
and DataEntry properties) directly with the table without an intervening
query. When the form's DataEntry property is set to True, for example, the
user can add new records, but not view/edit existing records. Enough db
philosophy, though...back to your question.

I don't think null will be a problem in the append (although I did not test
it), as long as the table does not require that field, in which case an error
will be generated. If null is a problem, you could build the insert statement
dynamically so that there is no reference to null fields in the SQL at all:

If Not Isnull(ctrlControl1), then -> append the ctrlControl1 reference to
the SQL

I'm kind of lazy about SQL, so I try to let the query builder do this work
for me as much as possible, though...

The data is confidential info. One of the requirements of this app is that it
has a secure audit trail of historical information. Once a record is entered
it's as good as written in stone. If a record needs to be edited, a new record
is written with a date stamp and an UpdatedBy field and the old one archived.
In this design, none of the forms or their controls will be directly bound to
tables or fields. There can be no way for someone to go in and actually change
what's been written. From the user's POV it will *look* like they are changing
a record but they won't be. For reporting purposes the datstamp will serve as
the identifier of the most current version of a record.

Now that we can stop trying to redesign the app we can debate the pros and cons
of writing a record using Recordset.AddNew vs. an Append Query.

For instance, since one of these fields is not required I was wondering how the
append query would handle a Null value.

First, why not just give the form a RecordSource of the table you want, make
the text boxes bound, and go to a new record when you open the form (or make
it a DataEntry form so that it only allows additions, if this is what you
want)? That way, the data is saved automatically when you close the form,
navigate to another record, or issue the save command via the VBA code on a
button.

If you really want to use unbound boxes, then have a button that opens an
append query. In the queyr builder, the various fields will look like this:

Expr1: [Forms]![frmForm]![ctrlControl1] (to be appended to Field 1 in the
table)
Expr2: [Forms]![frmForm]![ctrlControl2] (to be appended to Field 2 in the
table)

This takes the contents of the various text boxes on frmForm (the name of
your current form) and makes them the basis of the various fields in your
append. I still think it is much easier to let Access handle the saving of
the record natively by using a form RecordSource and binding the controls.

Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD

 

[David C. Holley]

First, why aren't the unbound controls bound to an underlying field.
What is the SPECIFIC reason to use unbound controls?

Second, if I were doing this (and couldn't bind the controls). I would
take this approach.
1. In the TAG property of each unbound control, I would add the
following value SaveValue,[targetTable] (e.g. SaveValue,tblGuests)
2. I would then use code to loop through the controls, inspecting the
..TAG property to determine which fields should be saved and to where.
(Basically using the DAO .AddNew method that you're familar with)

 

[David C. Holley]

I would *HIGHLY* recommend investigating SQLServer or any other DB that
supports TRIGGERS for this specific application.

The data is confidential info. One of the requirements of this app is that it
has a secure audit trail of historical information. Once a record is entered
it's as good as written in stone. If a record needs to be edited, a new record
is written with a date stamp and an UpdatedBy field and the old one archived.
In this design, none of the forms or their controls will be directly bound to
tables or fields. There can be no way for someone to go in and actually change
what's been written. From the user's POV it will *look* like they are changing
a record but they won't be. For reporting purposes the datstamp will serve as
the identifier of the most current version of a record.

Now that we can stop trying to redesign the app we can debate the pros and cons
of writing a record using Recordset.AddNew vs. an Append Query.

For instance, since one of these fields is not required I was wondering how the
append query would handle a Null value.

First, why not just give the form a RecordSource of the table you want, make
the text boxes bound, and go to a new record when you open the form (or make
it a DataEntry form so that it only allows additions, if this is what you
want)? That way, the data is saved automatically when you close the form,
navigate to another record, or issue the save command via the VBA code on a
button.

If you really want to use unbound boxes, then have a button that opens an
append query. In the queyr builder, the various fields will look like this:

Expr1: [Forms]![frmForm]![ctrlControl1] (to be appended to Field 1 in the
table)
Expr2: [Forms]![frmForm]![ctrlControl2] (to be appended to Field 2 in the
table)

This takes the contents of the various text boxes on frmForm (the name of
your current form) and makes them the basis of the various fields in your
append. I still think it is much easier to let Access handle the saving of
the record natively by using a form RecordSource and binding the controls.

:

Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD

 

[John Nurick]

Hi RD,

I don't think it matters which you use if you're going to stick with
DAO/Jet/mdb, so either stick with what's familiar or go for a query and
treat it as a learning opportunity.

If you envisage porting the database to an SQL client-server system some
time in the future, write VBA code to build and execute an INSERT INTO
.... VALUES statement.

 

[RD]

Unfortunately, I'm not afforded that option.

I would *HIGHLY* recommend investigating SQLServer or any other DB that
supports TRIGGERS for this specific application.

The data is confidential info. One of the requirements of this app is that it
has a secure audit trail of historical information. Once a record is entered
it's as good as written in stone. If a record needs to be edited, a new record
is written with a date stamp and an UpdatedBy field and the old one archived.
In this design, none of the forms or their controls will be directly bound to
tables or fields. There can be no way for someone to go in and actually change
what's been written. From the user's POV it will *look* like they are changing
a record but they won't be. For reporting purposes the datstamp will serve as
the identifier of the most current version of a record.

Now that we can stop trying to redesign the app we can debate the pros and cons
of writing a record using Recordset.AddNew vs. an Append Query.

For instance, since one of these fields is not required I was wondering how the
append query would handle a Null value.

First, why not just give the form a RecordSource of the table you want, make
the text boxes bound, and go to a new record when you open the form (or make
it a DataEntry form so that it only allows additions, if this is what you
want)? That way, the data is saved automatically when you close the form,
navigate to another record, or issue the save command via the VBA code on a
button.

If you really want to use unbound boxes, then have a button that opens an
append query. In the queyr builder, the various fields will look like this:

Expr1: [Forms]![frmForm]![ctrlControl1] (to be appended to Field 1 in the
table)
Expr2: [Forms]![frmForm]![ctrlControl2] (to be appended to Field 2 in the
table)

This takes the contents of the various text boxes on frmForm (the name of
your current form) and makes them the basis of the various fields in your
append. I still think it is much easier to let Access handle the saving of
the record natively by using a form RecordSource and binding the controls.

:


Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD

 

[RD]

Hm! I like that. It will come in handy on some of my more involved forms.

First, why aren't the unbound controls bound to an underlying field.
What is the SPECIFIC reason to use unbound controls?

Second, if I were doing this (and couldn't bind the controls). I would
take this approach.
1. In the TAG property of each unbound control, I would add the
following value SaveValue,[targetTable] (e.g. SaveValue,tblGuests)
2. I would then use code to loop through the controls, inspecting the
.TAG property to determine which fields should be saved and to where.
(Basically using the DAO .AddNew method that you're familar with)

Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD

 

[RD]

Nah, this app won't be porting anywhere. It's just a small desktop solution for
a small group here in the agency.

 

[David C. Holley]

If the information is as sensitve as it sounds, I would educate the
decision makers.

Unfortunately, I'm not afforded that option.

I would *HIGHLY* recommend investigating SQLServer or any other DB that
supports TRIGGERS for this specific application.

The data is confidential info. One of the requirements of this app is that it
has a secure audit trail of historical information. Once a record is entered
it's as good as written in stone. If a record needs to be edited, a new record
is written with a date stamp and an UpdatedBy field and the old one archived.
In this design, none of the forms or their controls will be directly bound to
tables or fields. There can be no way for someone to go in and actually change
what's been written. From the user's POV it will *look* like they are changing
a record but they won't be. For reporting purposes the datstamp will serve as
the identifier of the most current version of a record.

Now that we can stop trying to redesign the app we can debate the pros and cons
of writing a record using Recordset.AddNew vs. an Append Query.

For instance, since one of these fields is not required I was wondering how the
append query would handle a Null value.



First, why not just give the form a RecordSource of the table you want, make
the text boxes bound, and go to a new record when you open the form (or make
it a DataEntry form so that it only allows additions, if this is what you
want)? That way, the data is saved automatically when you close the form,
navigate to another record, or issue the save command via the VBA code on a
button.

If you really want to use unbound boxes, then have a button that opens an
append query. In the queyr builder, the various fields will look like this:

Expr1: [Forms]![frmForm]![ctrlControl1] (to be appended to Field 1 in the
table)
Expr2: [Forms]![frmForm]![ctrlControl2] (to be appended to Field 2 in the
table)

This takes the contents of the various text boxes on frmForm (the name of
your current form) and makes them the basis of the various fields in your
append. I still think it is much easier to let Access handle the saving of
the record natively by using a form RecordSource and binding the controls.

:



Hi all,

I have an unbound form with a mess of unbound text boxes. I'm going to use data
entered into them to write a new record into a table. In the past I would have
opened up a DAO recordset and done a .AddNew but since I started hanging out
here I see a lot of action query use.

Any comments on which might be preferred. This form is set up to only add one
record at a time so I'm not appending a whole bunch of records.

TIA,
RD