What strategy to add a new record on subform?

[WDSnews]

man! I was just starting to feel a little bit competent. Add New Record
should be one of the most basic tasks. If only there were a book called
"How not to be an idiot for dummies". I've spent many hours researching the
possible ways of adding a record until I'm completely befuddled about the
right strategy to use in Access. What seemed to work for one person caused
problems for another. It was completely unclear when I was looking at ADO
examples, DAO examples, Access examples, etc. I want to use ADO rather than
DAO for hope of future advantages, or perhaps a method that is independent
of ADO and DAO.

In the past I've relied on the user to create a new record and I've enjoyed
Access coding features like .defaultvalue to autofill various fields for me,
while allowing the user to change their mind. However in this case, I
definitely want to create a new record with code and fill-in the fields with
code.

I want my subform records to appear in reverse order, with newest on top.
After a new record is added, I want it to show at the top of the list. My
subform uses a table as its data source. Should I use a query instead?

As I understand it, there are four possible strategies, but I don't know why
I'd choose one over the other...

1. Open a new recordset with ADODB.Connection and ADODB.Recordset
2. Clone a recordset
3. Address the subform control
4. Address the subform

Which strategy should I use?
What does that strategy's Add New Record command look like?
Do I need to requery? update? resort?
How do I get the new record to display in the subform? on top?

 

[John W. Vinson]

man! I was just starting to feel a little bit competent. Add New Record
should be one of the most basic tasks. If only there were a book called
"How not to be an idiot for dummies". I've spent many hours researching the
possible ways of adding a record until I'm completely befuddled about the
right strategy to use in Access. What seemed to work for one person caused
problems for another. It was completely unclear when I was looking at ADO
examples, DAO examples, Access examples, etc. I want to use ADO rather than
DAO for hope of future advantages, or perhaps a method that is independent
of ADO and DAO.

In the past I've relied on the user to create a new record and I've enjoyed
Access coding features like .defaultvalue to autofill various fields for me,
while allowing the user to change their mind. However in this case, I
definitely want to create a new record with code and fill-in the fields with
code.

I want my subform records to appear in reverse order, with newest on top.
After a new record is added, I want it to show at the top of the list. My
subform uses a table as its data source. Should I use a query instead?

As I understand it, there are four possible strategies, but I don't know why
I'd choose one over the other...

1. Open a new recordset with ADODB.Connection and ADODB.Recordset
2. Clone a recordset
3. Address the subform control
4. Address the subform

Which strategy should I use?
What does that strategy's Add New Record command look like?
Do I need to requery? update? resort?
How do I get the new record to display in the subform? on top?

If you want the records to appear in a specified order, by all means use a
Query, sorting the records by descending date.

You can't get the blank "new record" to appear on top. A continuous or
datasheet subform always has the new record at the bottom, and AFAIK that
can't be changed. What you *can* do is use TWO continuous subforms: one, for
new records only, with only one row of height and with its DataEntry property
set to Yes; and the other, for record display and editing, with Allow
Additions set to No, Allow Edits to Yes, based on the sorted query. Carefully
line up the new record subform directly above the (bigger) display subform.

You'll need to Requery the display subform in the AfterUpdate event of the
data entry subform. No other code, and no recordset operations, should be
needed.

 

[WDSnews]

Ken,

fantastic. Thank you for your quick replies and surprisingly great
solutions. I very much like the idea of using an INSERT query, followed by
a subform requery.

In this case, my table has an autonumber field and about two dozen fields
which do not apply to this scenario. Most of them are fields which are
obsolete and scheduled to be removed from the database. Is the INSERT
solution appropriate and workable in this case?

I borrowed several of your ideas. I created a query and changed the data
source of my subform to use it. Then in my code I invoked a subform
requery, which instantly displays the new record at the top of the list just
as I wanted.

Instead of an INSERT Query, I opened a recordset and used .addnew.
Nevertheless, I'd like to try coding it with an INSERT query if I knew how
to handle the Autonumber and the inapplicable fields.

thank you for your great help.

Firstly to sort the rows in the subform in descending order you should use
a
query as its RecordSource. You'll also need a means of ordering the query
by
when the rows were inserted. You cannot rely on an autonumber column for
this; an autonumber is designed to guarantee unique values, nothing more.
The simplest way is to add a DateTimeStamp column to the table and set its
DefaultValue property to Now() in the table design; this will insert the
current date/time when a row is inserted. The query can then be like
this:

SELECT *
FROM YourTable
ORDER BY DateTimeStamp DESC;

To insert a row into the table in code you can execute an SQL statement,
using ADO in the example below, and then requery the subform, so it would
go
something like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim frm As Form

' return reference to subform via parent
' form's subform control's Form property
Set frm = Forms![YourParentForm].[YourSubformControl].Form

' insert row into table
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "INSERT INTO YourTable " & _
"(SomeNumberField,SomeTextField, SomeDateField) " & _
"VALUES(123,""ABC"",#2009-08-07#)"

cmd.CommandText = strSQL
cmd.Execute

' requery subform
frm.Requery

In reality you'd build the SQL statement's value list at runtime from
values
obtained from elsewhere of course, not by inserting literal values.

Ken Sheridan
Stafford, England

man! I was just starting to feel a little bit competent. Add New Record
should be one of the most basic tasks. If only there were a book called
"How not to be an idiot for dummies". I've spent many hours researching
the
possible ways of adding a record until I'm completely befuddled about the
right strategy to use in Access. What seemed to work for one person
caused
problems for another. It was completely unclear when I was looking at ADO
examples, DAO examples, Access examples, etc. I want to use ADO rather
than
DAO for hope of future advantages, or perhaps a method that is independent
of ADO and DAO.

In the past I've relied on the user to create a new record and I've
enjoyed
Access coding features like .defaultvalue to autofill various fields for
me,
while allowing the user to change their mind. However in this case, I
definitely want to create a new record with code and fill-in the fields
with
code.

I want my subform records to appear in reverse order, with newest on top.
After a new record is added, I want it to show at the top of the list. My
subform uses a table as its data source. Should I use a query instead?

As I understand it, there are four possible strategies, but I don't know
why
I'd choose one over the other...

1. Open a new recordset with ADODB.Connection and ADODB.Recordset
2. Clone a recordset
3. Address the subform control
4. Address the subform

Which strategy should I use?
What does that strategy's Add New Record command look like?
Do I need to requery? update? resort?
How do I get the new record to display in the subform? on top?