3rd question...

[Chatbox]

Since no one can help me with my previous two issues...here's a third one, which will help me decide how I should do what it is that I want.

Question: Can I have a single recordset that is both "User editable" (from datasheet) AND batch updateable (via VBA code)?

 

[raskew]

Suspect you're not likely to get many responses since you've yet
to tell us what version you're working with, e.g.
"disconnected Recordset in batch mode"--what is that?

Instead of starting new threads for each question, which appear to
be all interconnected (and expecting that contributors are going to
'chase-down' each of your previously published problems) how 'bout a coherent
thread that provides basic information re what you're working with.

Bob

 

[Chatbox]

Disconnected Recordset is one that has ActiveConnection set to Nothing after the data is cached locally.
Batch mode is when you open a recordset, the LockType is set to adLockBatchOptimistic.

Recordset is using ADO....

The fact that you're asking what "Disconnected Recordset in batch mode" is just means you have a low chance of knowning the answer to the questions.

 

[WayneRyan]

Whoever you are,

I'm backing Bob on this one. He even understands me at times.

Need more info ...

Wayne

 

[Chatbox]

I really don't see what info is missing.
So, I'll say the same thing again, but differently:

Users need to have the ability to edit data of a table. Everytime a record is changed/updated, its revision number is incremented by one.
One way is to edit data directly in a datasheet form, but will not commit the changes until he/she finished on multiple rows/records.
The other way is to have VBA code to change the data (field values).
The recordset in question is disconnected, so that changes will not be submitted to the table when the user move to another record. But will only update to the table at a later time, in one submission (rst.UpdateBatch).

The problem is this:
1. In datasheet view, batch changes to the data/field values do not reflect on the screen/display/GUI until UpdateBatch is ran. i.e. I can't preview the batch changes.
2. The other issue is that when the user is editing the datasheet manually, I can't Update the changes back to the table unless the changes is made by VBA code (instead of clicking and typing in the datasheet)....why?

One last thing: The entire update of multiple rows/records needs to be an atomic action (records' data are somewhat related to each other). So, updating one row/record to the table at a time is not an option.

 

[WayneRyan]

Chat,

What you are describing is not traditional Access (or any DB) behaviour.

If you need to create a new record when the users modify data.

Use the form's AfterUpdate event:

1) Create a new record, with a new ID number (DMax + 1), and use the
"current" data.

2) "Undo" the changes to the current record.

3) ReQuery the form, moving to the new record.

A nice alternative might be to use an Audit Trail to record changes.
Don't alter your data to track modifications, just track it. Use the
Search Facility here and look for "Audit" ... stop at ghudson!

Wayne

 

[Chatbox]

Chat,

What you are describing is not traditional Access (or any DB) behaviour.

If you need to create a new record when the users modify data.

Use the form's AfterUpdate event:

1) Create a new record, with a new ID number (DMax + 1), and use the
"current" data.

2) "Undo" the changes to the current record.

3) ReQuery the form, moving to the new record.

A nice alternative might be to use an Audit Trail to record changes.
Don't alter your data to track modifications, just track it. Use the
Search Facility here and look for "Audit" ... stop at ghudson!

Wayne

Hum...no.

Let's try a mini example:

[Flight Rates Table]:

ID   RevisionID    Origin    Destination     Price    DateUpdated
1    4             Tokyo     New York        1100     1/10/2004
2    2             Tokyo     Vancouver       1050     15/10/2004
3    5             New York  Chicago         450      30/9/2004
4    7             London    Paris           400      5/10/2004


After Update (of Tokyo to Vancouver, 2nd row):

ID   RevisionID    Origin    Destination     Price    DateUpdated
1    4             Tokyo     New York        1100     1/10/2004
2    3             Tokyo     Vancouver       1025     6/12/2004
3    5             New York  Chicago         450      30/9/2004
4    7             London    Paris           400      5/10/2004

The RevisionID is incremented by code, and so is the DateUpdated value.
User change the Price value. There's another table (archieve) that holds all preview revision of rates of all different routes. The table above only holds the latest rates. There's a reason why there's a revision ID...it's to do with checking rate change frequency, check quoted rates (to customers, say 3 days ago) compare to current rates (It's faster to use a revision ID, rather than compares the prices because there are first class, bussiness, econ...student prices...) . Also, checking the date will not work because a rate can update more than once a day. (i.e. So, base on the ID-RevisionID pair, I can pin point a particular route/rate of a particular time)

But again: The example only shows one record being updated. In reality, it's a bunch of rates (e.g. All rates going to Sydney to Seattle, all airlines.). So, as I said, it has to be a batch & atomic operation. Updates all (with in the batch), or nothing. i.e. Not one record at a time. No new records are added unless it's a new route, airline or something...

 

[Chatbox]

Anyone else got any suggestion?

 

[WayneRyan]

Chat,

For one record on a form, you can use the BeforeUpdate event:

DoCmd.RunSQL "Insert Into tblArchive (ID, RevisionID, Origin, Destination, " & _
             "                        Price, DateUpdated) " & _
             "Values (" & Me.ID & ", " & _
                          Me.RevisionID.OldValue & ", '" & _
                          Me.Origin & "', '" & _
                          Me.Destination & "', " & _
                          Me.Price.OldValue & ", #" & _
                          Date & "#);"

For a "Batch" of new records:

1) Get "new/modified" record (disk file or table)?

2) If route exists, save current record to archive and
replace current record with new record (adding +1 to RevisionID)

3) If new route, just enter into table (RevisionID = 0)

Wayne

 

[BLeslie88]

Well...

Updating one at a time is not an option....

I am currently working on something that would apprently solve your problem.

I am working on unbound t-boxes.

Everything is populated vb code...

So in this sense... no updating would be done until they commit all record changes. The # of updates/record could this way can be very controlled by your code.

I don't update tables until users commit the data by a form action.

Disadvantage. Well if the db closes for any reason, there is no tracking of the active form... so all changes lost.

I am just providing another spectrum, good luck.

 

[Chatbox]

Chat,

For one record on a form, you can use the BeforeUpdate event:

DoCmd.RunSQL "Insert Into tblArchive (ID, RevisionID, Origin, Destination, " & _
             "                        Price, DateUpdated) " & _
             "Values (" & Me.ID & ", " & _
                          Me.RevisionID.OldValue & ", '" & _
                          Me.Origin & "', '" & _
                          Me.Destination & "', " & _
                          Me.Price.OldValue & ", #" & _
                          Date & "#);"

For a "Batch" of new records:

1) Get "new/modified" record (disk file or table)?

2) If route exists, save current record to archive and
replace current record with new record (adding +1 to RevisionID)

3) If new route, just enter into table (RevisionID = 0)

Wayne

I know what I need. I'm having problem with making what I need to happen. (You just basically listed the things I already know I need to do.)

A disconnect recordset used with datasheet, when edit by hand, it doesn't mark the record (rst.Status) as adRecModified (for some unknown reason)...and because of this, rst.UpdateBatch doesn't know which rows/records are edited. (A normal, non-disconnected recordset doesn't have this issue.)

 

[Chatbox]

Updating one at a time is not an option....

I am currently working on something that would apprently solve your problem.

I am working on unbound t-boxes.

Everything is populated vb code...

So in this sense... no updating would be done until they commit all record changes. The # of updates/record could this way can be very controlled by your code.

I don't update tables until users commit the data by a form action.

Disadvantage. Well if the db closes for any reason, there is no tracking of the active form... so all changes lost.

I am just providing another spectrum, good luck.

I have some other forms that are using unbound textboxes already. Updates are done by VBA code, just like you. However, the users prefer the multiple fields/multiple rows look (e.g. as if in Excel...) That's why I'm trying to provide them with datasheet editing mode. (trying to, anyway).

 

[BLeslie88]

However, the users prefer the multiple fields/multiple rows look (e.g. as if in Excel...) QUOTE]

I have customed a form with 40 records and 15 fields.... (Even change one text box that slides left and right depending on the company selection) there is little limitation with imagination.

I have customed the filters, have auto-tab on masked entries, coded down arrow to go down like excel not across, auto droping of data when they get to the second line.

You can be as creative as you like, if you have vb coding and sql down pat.

Anyhow, just trying to elaborate on how this is very excel like with many additional functions / formatting / and autofilling... unlike excel....

 

[Chatbox]

40 records is a limitation, but I guess you can add more if you want. Do you have resizeable columns and columns sorting and repositioning?

There's also no real reason for me to re-invent the wheel (the datasheet) just because of a small issue. (and it is a small issue, just one that I can't solve yet.) Any extra functions/features can be add on top of the datasheet form.

 

[Pat Hartman]

Is there some reason that you elected not to use Transactions? It seems to me that wrapping your batch update in a transaction solves the problem of requirirng all or nothing. Using that method, you can stick with bound forms and not have to deal with the tons of code required to properly manage unbound forms.

I think that if you investigate the capabilities, you may find that you have more control and options if you use DAO transactions rather than ADO. I know that some people think that DAO is dead so they won't use it, but it is back as the default with A2003. Too much that DAO does was never implemented in ADO.

 

[Chatbox]

Thanks for the pointers, Pat. I'll look into it right away.