How to edit a continuous subform's recordset in batch

  • Thread starter Thread starter Bula
  • Start date Start date
B

Bula

Hello:

I have a main form with a continuous sub form. Suppose the main form
is bound to the Orders table and the sub forms is bound to the Order
Details table. In the main form's oncurrent event, I use the
following code to make the subform synchronous to the main form

Me.subform.form.filter = "OrderDetails.orderID = me.orderID"
me.subform.form.filteron = true

There are about 10 rows in the subform. I want to allow users to edit
values on any row and append rows on the sub form, and then click the
save button to save all the changes to the subform's underlying table
in batch, or click cancel to roll back all the changes.

Please tell me how Can I realize it?
Thank you very much!

Bula
 
If your subform has all the controls to facilitate the mandatory fields, I
don't see any problems.
If the problem persists, pls elaborate on your situation.

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE
 
First, you don't need any code at all to display eh child records.

You supposed to use the link master, and link child settings, and the
filtering should be automaton.

MORE IMPORTANT then the filter, is if you set the link master/child
settings, then you DO NOT have to set the value of orderid in the sub-form
(with your setup now, you have to set the value of orderID for each sub-form
record you add. If you use the link master/child settings, then you can
remove your code that you have for filtering, and also when new records are
added, the orderID will be set for you.

As for wrapping the while mess in a transaction, to have a single unify ed
save (ie: you edit some some fields...you add, or delete some sub-form
records..you then navigation on to another order...do some editing.

at a certain point in time, your asking for a save button to commit all of
that editing. Unfortunately, while JET (and ms-access) does support commit,
and rollback (transactions), those transactions DO NOT WORK with the GUI
(forms).

It gets fairly messy, and especially if you allow navigation on the main
orders form (what happens is a user edits, and then navigates to several
other orders..and then decides they don't want to save, but undo the current
record, they going to un-do all of the sub-form records, and also all of the
previous orders (since the last time they saved). So, a particularly caution
has to be exercised in terms of how you design your UI for the user (for
example, you likely have to remove the navigation buttons for the main order
form).

It is not very practical at all to wrap the recordsets in a transaction when
they are attached to a form. You *can* attempt this. This means you create a
reocordet in code (wrapped in a transaction). You then attach this reocrdset
to the form. And, you must do the same for the sub-form. it is messy code
(and, worse, if you take this approach, then you have to throw out my
suggestion to use the link master/child settings, as they don't work in this
case.

The problem is much when you move to another record, you save the current
one, and that also applies to a continues form (and, the fact that this form
is a sub-form is a moot point). So, you looking to have navagation on a sub
form, but not commit the records.
 
Thank you very much, Albert and Perry:

1) Regarding link master, and link child:
If I use the link Master Child to sychronize the two forms, the sub
form's recordset has to be of Dao.recordset type, which does not
support UpdateBatch method.
If I am incorrect here, please let me know.

2) Navigate in the main form will not be a problem: In the on current
event of the main form, we can detect weather the sub form is dirty.
If so, ask user to commit or roll back before moving to another row.

2) I am trying to convert the sub form's recordset from Dao to Adodb.

In the main form's on current event, call the filterSubform method in
the subform

FilterSubForm

Dim cmdtxt As String
cmdtxt = "select * from OrderDetails where [OrderID] = &
me.parent.orderID
Dim FrmRst As adodb.Recordset

Set FrmRst = AdodbBatchRecordset(cmdtxt) ' a function returns an
adodb recordset with locktype = lockbatchpermissive and cursorloction
= vbClient

Application.Echo False
Me.RecordSource = ""

If Not FrmRst Is Nothing Then
Set Me.Recordset = FrmRst
End If

Me.RecordSource = Me.RecordSource
Me.Requery
Application.Echo treue
end sub

In the click events of the Save and Cancel buttons on the subform, we
run the command
Sub cmdSave_Click
me.recordset.updatebatch
me.requery
end sub
Sub cmdCancel_Click
me.recordset.cancelbatch
me.requery
end sub

With such ways, I am hoping that changes to all the rows and fields on
the continuous subform, no matter programmatically or mannually, can
be commited or rolled back in batch.

Do you think such way is practical?
 
1) Regarding link master, and link child:
If I use the link Master Child to sychronize the two forms, the sub
form's recordset has to be of Dao.recordset type, which does not
support UpdateBatch method.
If I am incorrect here, please let me know.
Click to expand...

Yes, actually, I think you can bind a form to a ado, or dao reocrdset.
However, you going to have to create two separate reocrdsets (one for main
form, and one for sub-form). Regardless, if you build the recordsets in
code, then you CAN NOT use the link master/child settings.
2) Navigate in the main form will not be a problem: In the on current
event of the main form, we can detect weather the sub form is dirty.
If so, ask user to commit or roll back before moving to another row.
Click to expand...

Well, there is problems. If you delete the main record, you have child
records that need to be deleted, but you have a pending transaction for the
child records.

Do you think such way is practical?
Click to expand...

You do realize that the instant the cursor moves from the main form part to
the sub-form, that the main record is committed to disk (it has to be...else
how can you add child records!!). So, ms-access does a disk write of the
main record when the focus changes to the sub-form (it has to work that way
if you think about how bound forms work).

So, if your save button is going to save the whole screen, you have to wrap
the main record in a transaction also. As I said..it starts to get messy
real fast.

Further, I would never allow navigation to occur on the main form. This
would simplify your designs a lot.

As a general rule, I don't allow or have navigation buttons on my main forms
anyway. Users find a record, display it, edit it, and when done, they close
form and are ready back for editing the next record. This "classic"
find->edit cycle is shown here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

Do you think such way is practical?
Click to expand...

I can't really say that the approach is easy. The problem is once you build
one form this way, then virtually ALL OF your forms your application will
have to function this way. This will SUBSTANTIALLY increase the costs of
development time. Only you can really decide if this extra effort is worth
it.

I do wish we could wrap the UI (forms) in a transaction, but the bound
nature of ms-access makes this quite difficult to achieve. I certainly not
spent the time to build a solution, and amazing enough, I don't know any of
the developers who I respect a lot that have a nice solution.
 
Thank you again, Albert D. Kallal:

I figured out a work around that may help us updatebatch a table
through the UI.
We can create a child form with ado recordset. After data is populated
into the form's recordset, we disconnect the recordset. Users can
update values of all rows on the form, and then click the Save button,
which call a method to interate and read the form's disconnected
recordset and update the original table through a command query or
another recordset.
 
Back
Top