New Record Design Delima

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one form that I use for two actions, modifying existing information
and adding new records. To add a new record, my current method involves
changing the record source to a temp table and append the new record to the
main table. However, I have discovered the option "acFormAdd" on the
docmd.open, etc., as another method and creating a new recordset, using
addnew, and update. These last two seem more elegant, and in an effort to
improve the database I want to learn how they work, but I am having a
problem, well two problems, one with each option. Using both options I have a
message box asking if the person wants to save their entry. If the answer is
no, how do I keep the data from being entered anyway with the docmd option?
If I close the form the data goes in! With the recordset option, I realize
the update should only execute when the user answers yes to saving, however,
the creating of the recordset is giving me problems. Here is what I have
sofar in the form open section.
If NewItem = True Then
Dim dbsInquiry As DAO.Database
Dim rstInquiry As DAO.Recordset
Set dbsInquiry = CurrentDb
Set rstInquiry = dbsInquiry.openrecordset("qry Raw Data",
dbOpenDynaset)
Me.Recordset = rstInquiry
Else
Me.Recordset = "qry Raw Data"
End If
I just want to know the best way to add a new record in a multi-user
environment and give the user the option not to save the entry. I hope this
question is not too involved but I have had trouble locating a good example.
Thanks in advance for your help.
Sincerely
Michael
 
Michael, you want the user to chance to confirm the entry? Just use the
BeforeUpdate event of the form, and cancel the event if the user doesn't
want to save.

The code for the form's BeforeUpdate event procedure would look like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save?", vbYesNo) = vbNo Then
Cancel = True
Me.Undo
End If
End Sub

That's it: no other code or temporary tables needed.
 
Allen:
Sorry for the delayed response, I took your undo suggestion and fixed a
bunch of my forms. Thanks for that. However, my original delima centers
around the best way to add a new record. Pretty beginner, but the more you
learn the more you realize there are multiple ways to do something. There is
DAO, ADO, macros, or the right arrow with the star in the nav bar, or a temp
table and append. Which one gives you the most control and satisfies my
conditions.

Condition One: I don't want the autonumber to advance if the user chooses
not to save, the undo you gave me prevents the data from going in, but the
autonumber has already advanced on the first entry because I am working off a
query linked to the Raw Data table, not a temp or recordset.

Condition Two: Multiple new entries without closing the form. This is where
docmd... acNewRecord gets me in trouble, the first one works, the second one,
I don't know, can you open a form that is already open?

Condition Three: When the data does get saved, I need to capture the
autonumber it is assigned so I can move to that new record in the other form.

I like the idea of recordsets, but I am unsure how to write the code
properly. Your site cleared up one unknown, I did not realize both DAO and
ADO used recordsets, with all the books and sites I reference, most use ADO
commands. And they use different commands, how nice. A major part of the
user's functionality is entering new inquiries, so this code has to be
bullitproof. I just don't have a clear picture of the entire process or I
guess what each line does. Does the addnew line pause for entry? If the
addnew is in the button onclick, and the save button is separate, how does
the save button know what to update, do you redefine an already open
recordset or just reference it? Does every field on the form have to be
assigned to a recordset value, is there a "star" option? Anyway, I have
gabbed enough and I think you have the general idea. Thanks for your
continued help.
Cheers,
Michael
 
Couple of approaches.

Firstly, why does it matter whether the AutoNumber is sequential? As soon as
you delete an earlier record, you leave a gap anyway.

If it does matter, use a Number field instead of an AutoNumber. Assign the
value at the last possible moment, Form_BeforeUpdate. (If you have lots of
users, there is still the chance that you will get duplicates, so you will
need to write extra code to handle with another table where you can set a
lock assign the next number, hold the lock until the record is written, and
then release the lock, so that another user is locked out until the write
succeeds.) The other conditions are easily met: #2 the form goes on to a new
record, and #3 you already have the number (and it's in the form in
Form_AfterUpdate.)

The other alternative is to use unbound forms, where you programmatically
add the value to a Recordset or execute an Append query statement to add the
record. You also need to programmatically load the form from an existing
record, and distinguish this as an Update rather than an Append, and
probably handle deletes as well.

The unbound approach is doable - it's essentially what programmers do if you
are writing in a lower-level language - but it defeats the whole point of
all the stuff Access gives you. You no longer have Form_BeforeUpdate for
validation, Form_AfterInsert for responding to new records, Form_AfterUpdate
for triggering other changes, Form_Dirty for knowing when an edit begins,
Form_Undo for rolling back, Form_Current for setting up for another record,
and so on, not to mention all the engine-level checking that happens for
bound controls (such as automatic data typing, validation, stopping when the
text gets to the accepted length, nullability, type range checking,
duplicate index checks, etc, etc.)

And of course, you have to develop your own locking and concurrency handling
as well if you go the unbound approach. Lots of effort, when a net less of
functionality and reliability. By all means, do it for fun, but not for
serious work unless you have a particular reason why all the stuff Access
gives you is not suitable.
 
Back
Top