Form design to add new record when subforms exist

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

Guest

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!
 
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

Greg said:
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.

Graham Mandeno said:
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

Greg said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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

Greg said:
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.

Graham Mandeno said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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

Greg said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Hi again, You've bamboozled me here!
How and where do I declare rst.Data as a Public variable?
In Modules design mode I have something marked Bas_Global with this sort of
thing in it:
Global glngFlightPlanLegDetailIDNew As Long
Global glngFlightPlanLegIDNew As Long

I've tried adding this to the group:
Public rstData As Recordset

but the object rst.Data is not set when I try to copy the data from rstWrk
back to rstData.

Graham Mandeno said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Load code is working perfectly using OpenArgs method as you described.
However when I try to save the edited record I get the error message :
"Object variable or With Block variable not set" No doubt its because I
haven't declared rstData at module level for the form. I don't know how. Can
you advise how to do this? Thanks

Here's a copy of my code on the Save Button:
Dim db As Database
Dim rstWrk As Recordset
Dim sqlWrk As String
Dim sqlData As String
Dim i As Integer

Set db = CurrentDb()

'Find the record in the working table
sqlWrk = "SELECT * FROM tbl_Wrk_EditBooking "
sqlWrk = sqlWrk & "WHERE lng_BookingID = " & Me!ID
Set rstWrk = db.OpenRecordset(sqlWrk, dbOpenDynaset)

'Check if the record exists at the working end
If rstWrk.EOF Then
MsgBox "The record does not exist! Changes cannot be saved.",
MB_ICONSTOP, "Critical Error"
rstWrk.Close
Set rstWrk = Nothing
Set db = Nothing
Exit Sub
End If

'Copy the record to the data table
'rstData.Edit was issued earlier
For i = 0 To rstData.Fields.Count - 1
If rstData(i).Name <> "lng_BookingID" Then ***EROR GENERATED HERE***
rstData(i) = rstWrk(i)
End If
Next i
rstData.Update
rstData.Close
rstWrk.Close
Set db = Nothing
Set rstData = Nothing
Set rstWrk = Nothing

'close the form
DoCmd.Close acForm, "Frm_EditBooking", acSaveNo


Graham Mandeno said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Hi Greg

Did you declare rstData in your Form_Load procedure? it should have been
declared at module level in your form module (before the first Function/Sub
declaration). Your Dim db as Database should really be moved to module
level as well.

Also, be sure to save the current form record to the work table before you
try to open it in rstWork:

Me.Dirty = False ' should do the trick

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Greg said:
Load code is working perfectly using OpenArgs method as you described.
However when I try to save the edited record I get the error message :
"Object variable or With Block variable not set" No doubt its because I
haven't declared rstData at module level for the form. I don't know how.
Can
you advise how to do this? Thanks

Here's a copy of my code on the Save Button:
Dim db As Database
Dim rstWrk As Recordset
Dim sqlWrk As String
Dim sqlData As String
Dim i As Integer

Set db = CurrentDb()

'Find the record in the working table
sqlWrk = "SELECT * FROM tbl_Wrk_EditBooking "
sqlWrk = sqlWrk & "WHERE lng_BookingID = " & Me!ID
Set rstWrk = db.OpenRecordset(sqlWrk, dbOpenDynaset)

'Check if the record exists at the working end
If rstWrk.EOF Then
MsgBox "The record does not exist! Changes cannot be saved.",
MB_ICONSTOP, "Critical Error"
rstWrk.Close
Set rstWrk = Nothing
Set db = Nothing
Exit Sub
End If

'Copy the record to the data table
'rstData.Edit was issued earlier
For i = 0 To rstData.Fields.Count - 1
If rstData(i).Name <> "lng_BookingID" Then ***EROR GENERATED
HERE***
rstData(i) = rstWrk(i)
End If
Next i
rstData.Update
rstData.Close
rstWrk.Close
Set db = Nothing
Set rstData = Nothing
Set rstWrk = Nothing

'close the form
DoCmd.Close acForm, "Frm_EditBooking", acSaveNo


Graham Mandeno said:
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!!


:

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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top