DAO Recordset Help

[Guest]

I need a lesson in recordsets. I want to use a recordset to open a form and
add a new record. I am having trouble finding all the necessary code to
create the recordset, make the form use that recordset and then update the
table after they choose to save it. Should they choose not to save, the
autonumber on the table should not advance. If I get comfortable with this
code, I will use it to speed up the main form by calling only one record,
rather than open the entire query as its recordsource, however, I need to
lock the edited record, is this possible? This is a multi-user database with
the tables on the network, but I can't find the code I need to accomplish
these prefered programming methods. Any help would be greatly appreciated.
I'll even buy a book if it has DAO code, not ADO, or read an article
somewhere, I just need to get this clear in my head.
Regards,
Michael

 

[Ken Snell \(MVP\)]

You may be overengineering this.

Set the form's recordsource to a query that will give you the desired
recordset. Then open the form in "data entry" mode:

DoCmd.OpenForm "FormName", , , , , acFormAdd

 

[Douglas J. Steele]

Unfortunately, that won't meet one of the requirements: "Should they choose
not to save, the autonumber on the table should not advance."

However, I'd challenge that requirement anyhow. Autonumber fields exist for
one purpose: to provide a (practically guaranteed) unique number that can be
used as a primary key. No meaning should be attached to its value. In fact,
it's generally considered a good idea not to even show its value to the
users. Be aware that should you even need to replicate the database, the
Autonumber fields would all be changed from sequential to random.

Should they choose to ignore this advice, it would be possible to create an
unbound form that let them play with the entry, and only save it once they
were satisfied with it (using either an INSERT INTO SQL statement, or adding
to a recordset). However, in a multi-user environment, there would be no way
to know what the value of the Autonumber field is going to be in advance.

 

[Ken Snell \(MVP\)]

Unfortunately, that won't meet one of the requirements: "Should they
choose not to save, the autonumber on the table should not advance."

Yep. Missed that sentence in his post.

However, I'd challenge that requirement anyhow. Autonumber fields exist
for one purpose: to provide a (practically guaranteed) unique number that
can be used as a primary key. No meaning should be attached to its value.
In fact, it's generally considered a good idea not to even show its value
to the users. Be aware that should you even need to replicate the
database, the Autonumber fields would all be changed from sequential to
random.

Agreed!