Hi Greg
You could have rstData opened by one form and closed by another, but you
would then need to declare it as a Public variable in another module.
Another approach would be to do all the manipulation in the Load and Unload
events of your EditRecord form. You can pass the ID value of the record you
wish to edit using the OpenArgs property of the form.
Your Load code would need to:
1. Empty your work tables (two simple DELETE queries)
2. Open rstData with a single record selected
3. Lock rstData (.Edit)
4. Create a copy record in your master work table
5. Copy any child records (Append query)
6. Requery the form (Me.Requery)
rstData would need to be declared at module level for the form.
The Click code for the Save button would copy everything back and issue a
rstData.Update, then close the form.
The Unload code could check if any changes have been made and show a warning
"Do you wish to save your changes", then do a CancelUpdate and close
rstData.
To record the fact that changes have been made, use the Form_AfterUpdate
procedure in both the EditRecord form and its subform. Declare a boolean
variable at module level in the main form:
Public fDataChanged As Boolean
Then, in the main form's AfterUpdate, set:
fDataChanged = True
and in the subform:
Me.Parent.fDataChanged = True
The test in Form_Unload is then something like this:
If fDataChanged Then
Select case Msgbox("Save changes?...", vbYesNoCancel)
Case vbYes
cmdSave_Click
Exit Sub
Case vbNo
' do nothing
Case vbCancel
Cancel = True ' stop from closing
Exit Sub
End Select
End If
If rstData.EditMode <> 0 Then rstData.CancelUpdate
rstData.Close
Of course, cmdSave_Click would need to set fDataChanged = False. Also, it
could check at the start if fdataChanged was True and if not, bypass all the
saving code.
For adding new records, your Viewrecord form could empty the temp tables and
open the EditRecord form in DataEntry mode. cmdSave_Click could then check
Me.DataEntry to see if it was creating a new record or editing an existing
one.
As you may have gathered, this is all off the top of my head. Hope some of
it works
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Greg said:
Once again Graham, Very helpful. Method 2 is the way I was looking for.
It's
been a number of years since I have done this and I'm pretty rusty.
Could you clarify this for me: When I click my Edit button on my
ViewRecord
Form rstdata.Edit (which will lock the record) occurs. My EditRecord Form
opens and rstdata.Update or rstdata.CancelUpdate (which will unlock the
record) occurs when I click a Save or Cancel. So the recordset is opened
by
one form and closed by another?
Also, changes are made on the edit record form. The user clicks the Cancel
button rather than the Save button. How do I make it so that changes are
recoqnised and the user is asked if changes should be saved?
1 more. I have my ViewRecord and EditRecord forms. Should I have a
seperate
NewRecord form which is bound to rstdata? Or should I use the EditRecord
form
which is bound to rstTemp locally. Or is there a better way?
Thanks Again!!
Graham Mandeno said:
Hi Greg
There are two methods:
You can use SQL queries - an INSERT INTO (append) query to copy the data
to
your work table and an UPDATE query to copy the changes back (or another
append query to add new records). The INSERT INTO syntax is easy, but
the
UPDATE syntax would need to include every field anyway, so I think I
would
personally prefer option 2, which is:
You can use a loop in VBA code. Something like this:
rstData.Edit ' lock the record
rstTemp.AddNew
For i = 0 to rstData.Fields.Count - 1
rstTemp(i) = rstData(i)
Next i
rstTemp.Update
When you copy the changes back, you do NOT want to copy the primary key
value (particularly if it is an AutoNumber):
' rstData.Edit was issued earlier
For i = 0 to rstData.Fields.Count - 1
If rsData(i).Name <> "Name of PK field" Then
rstData(i) = rstTemp(i)
End If
Next i
rstData.Update
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Thanks Graham, very helpful. Could you possible help with the syntax to
copy
the record in the rst from the main table to the temporary table. I
have
about 100 fields that need to be copied across and recall that there is
a
method where I dont have to type in:
Temprst!fieldName = rst!Fieldname for every field name
Hope you can help.
:
Hi Greg
It's possible to use an unbound form to do "offline" updates to a
single
table, but the sticking point here is the subform. The problem is
that
you
cannot move the focus between the main form and the subform, or
between
different records in the subform, without saving the record that is
losing
the focus. If you want to delay the save until all the updates have
been
made (and possibly cancel the changes altogether) then temporary
tables
are
the easiest way to go.
You will need two tables - one for the "parent" record (main form) and
one
for the "child" records (subform). IMO it's very bad practice to put
temporary tables in your front-end as they will cause the front-end to
bloat
and increase chances of corruption. I prefer to put temporary tables
in
a
local back-end and link them to the front-end in the same way the
other
back-end tables are linked.
To manage the record locking, you don't need a flag field. Just open
a
recordset based on the single record you want to edit and execute the
.Edit
method on the record. This will prevent anyone else from doing the
same
until you perform a .CancelUpdate or close the recordset and release
the
lock. This means that the lock will automatically be released if the
front-end terminates for any reason without explicitly releasing it.
Using
a flag field would leave the record in a locked state after, say, a
power
failure on one of the front-end workstations.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi, I am designing a split database. How should I design the
database
so
that
when I add a new record it is not saved until the user clicks a save
button.
I am thinking that a temp table should be on the front end and that
when
the
record is saved the columns are written to the main table on the
back
end.
I
run into a problem because the form that the data is entered into
will
contain subforms. Is it possible for the subforms not to have a
parent/child
relationship with the main form until after the record is saved?
Should
each
subform have a temp table on the front end also? Or is there a
better
way?
I will also want to be able to edit a record in a multiuser
environment. I
am thinking that when a user clicks a button to edit a record the
main
table
on the back end has a column (fEdit) that is checked when the data
is
written
to the temp table on the front end. This would mean that if fEdit
is
true
then other users would not be able to edit the record at the same
time.
The
record would not be available for edit until the user saves the
edited
record
and fEdit is unchecked. Is this the way to do things?
How should I manage parent/child relationships between the form and
subforms?
Any help would be greatly appreciated. Thanks!
