Solved

Main-SubFormUndo

Posted on 2002-06-07
14
218 Views
Last Modified: 2008-02-01
I am using Microsoft example code for undo procedure with Main form and sub form. First part Save Main and SaveSub are working Ok. I fill in temp tables (which I will use latter for transaction record purpose).But I Have problem with second part :
UndoMain and UndoSub ,UndoMain is working but when I come to Undo Sub I got error on :

For i = 0 To TempRS.Fields.Count - 1
                FormRS(TempRS(i).Name) = TempRS(i)
            Next i


 It looks it passed two times through loop and pop up error.
The TempSub table has 11 fields.My subform has 8 fields,
but record-source of my subform is qry with 11 fields I tought  that it should not be cause of undo procedure error as it was not during SaveSub.
 

Here is Code for SaveMain ,SaveSub  and UndoMain and UdnoSub.
:
Sub SaveMain(F As Form, TempTable As String)
    Dim DB As DATABASE
    Dim FormRS As Recordset
    Dim TempRS As Recordset
    Dim i As Integer
   
    Set DB = CurrentDb()
   
    On Error GoTo Err_SaveMain
   
    ' Clear temp table
    DB.Execute "Delete * From [" & TempTable & "];"
   
    ' Open temp table
    Set TempRS = DB.OpenRecordset(TempTable, DB_OPEN_TABLE)
    If Err Then
        Beep
        MsgBox "BUG: " & Error, 48
        Exit Sub
    End If
   
    ' Get Mainform recordset
    Set FormRS = F.RecordsetClone

    ' Find current record in mainform recordset
    On Error Resume Next
    FormRS.Bookmark = F.Bookmark
   
    ' If error, we must be at a new record. Exit, since nothing to save.
    If Err Then Exit Sub
   
    '
    ' Create a copy of the main form record in the temp table
    '
   
    On Error GoTo Err_SaveMain
   
    ' Create a new record
    TempRS.AddNew
       
        ' Loop through the fields in the new temp table
        ' .. record, copying into each the mainform field value.
        For i = 0 To TempRS.Fields.Count - 1
            TempRS(i) = FormRS(TempRS(i).Name)
        Next i
   
    ' Save the new record
    TempRS.Update
   
Bye_SaveMain:
    Exit Sub

Err_SaveMain:
    Beep
    MsgBox "BUG: " & Error, 48
    Resume Bye_SaveMain

End Sub

Sub SaveSub(F As Form, TempTable As String)
    Dim DB As DATABASE: Set DB = CurrentDb()
    Dim FormRS As Recordset, TempRS As Recordset
    Dim i As Integer

    On Error GoTo Err_SaveSub

    ' Clear temp table
    DB.Execute "Delete * From [" & TempTable & "];"
   
    ' Open temp table
    Set TempRS = DB.OpenRecordset(TempTable, DB_OPEN_TABLE)
   
    ' Get subform recordset
    Set FormRS = F.RecordsetClone
   
    ' Find the first subform record
    On Error Resume Next
    FormRS.MoveFirst
   
    ' If error, we must be at a new record. Exit, since nothing to save.
    If Err Then Exit Sub

    '
    ' Create a copy of each subform record in the temp table
    '
   
    On Error GoTo Err_SaveSub

    ' Loop through all the subform records
    Do Until FormRS.EOF
        '
        ' Create a copy of the subform record in the temp table
        '
        ' Create a new record
        TempRS.AddNew
           
            ' Loop through the fields in the new temp table
            ' .. record, copying into each the subform field value.
            For i = 0 To TempRS.Fields.Count - 1
                TempRS(i) = FormRS(TempRS(i).Name)
            Next i
       
        ' Save the new record
        TempRS.Update
       
        ' Get the next subform record
        FormRS.MoveNext
   
    Loop
   
Bye_SaveSub:
    Exit Sub

Err_SaveSub:
    Beep
    MsgBox "BUG: " & Error, 48
    Resume Bye_SaveSub
   
End Sub

Sub UndoMain(F As Form, TempTable)
    Dim DB As DATABASE
    Dim FormRS As Recordset
    Dim TempRS As Recordset
    Dim i As Integer
   
    Set DB = CurrentDb()
   
    On Error Resume Next

    ' Open temp table
    Set TempRS = DB.OpenRecordset(TempTable, DB_OPEN_TABLE)
       
    ' Find first record
    On Error Resume Next
    TempRS.MoveFirst

    ' If an error occurred, we must be undoing a new record.
    If Err Then
        ' Clear the Error
        Err.Clear
        ' Go to the current record in the Recordset Clone
        F.RecordsetClone.Bookmark = F.Bookmark
        ' Attempt to delete the record if it exists
        F.RecordsetClone.Delete
        If Err Then
            ' If there was an error attempting to delete the record
            ' then the record had never been saved and do an Undo instead
            F.Undo
        End If
   
        Exit Sub
    End If
   
    On Error GoTo Err_UndoMain
   
    ' Loop through all temp table fields, copying contents of
    ' .. temp table record fields back into mainform record fields.
    For i = 0 To TempRS.Fields.Count - 1
           
        ' Update the field ONLY if the contents are different
        If TempRS(i) <> F(TempRS(i).Name) Or IsNull(TempRS(i) <> F(TempRS(i).Name)) Then
            F(TempRS(i).Name) = TempRS(i)
        End If
       
    Next i

Bye_UndoMain:
    Exit Sub

Err_UndoMain:
    Beep
    MsgBox "BUG: " & Error, 48
    Resume Bye_UndoMain

End Sub

Sub UndoSub(F As Form, TempTable As String)
    Dim DB As DATABASE
    Dim FormRS As Recordset
    Dim TempRS As Recordset
    Dim i As Integer

    Set DB = CurrentDb()

    On Error GoTo Err_UndoSub

    ' Open temp table
    Set TempRS = DB.OpenRecordset(TempTable, DB_OPEN_TABLE)
   
    ' Get subform recordset
    Set FormRS = F.RecordsetClone
   
    ' Find first record
    On Error Resume Next
    FormRS.MoveFirst
   
    ' If error, must not have any subform records to remove, so exit.
    If Err Then Exit Sub
    On Error GoTo Err_UndoSub

    ' Loop to delete all records from subform recordset
    Do Until FormRS.EOF
        FormRS.Delete
        FormRS.MoveNext
    Loop

    ' Find the first temp table record to restore
    On Error Resume Next
    TempRS.MoveFirst
   
    ' If error, we must not have any to restore, so exit.
    If Err Then Exit Sub
    On Error GoTo Err_UndoSub

    ' Loop through all the temp table (saved subform) records
    Do Until TempRS.EOF
        '
        ' ReCreate the old subform record by creating a new one with data
        ' .. from the temp table record.
        '
        ' Create a new subform record
        FormRS.AddNew
           
            ' Loop through all the fields in the temp table
            ' .. record, copying into their values into the subform record.
            For i = 0 To TempRS.Fields.Count - 1
                FormRS(TempRS(i).Name) = TempRS(i)
            Next i
       
        ' Save the new record
        FormRS.Update

        ' Get the next temp table record
        TempRS.MoveNext
   
    Loop

Bye_UndoSub:
    Exit Sub

Err_UndoSub:
    Beep
    MsgBox "BUG: " & Error, 48
    Resume Bye_UndoSub

End Sub


Any idea of what I am missing here.


Thanks Taras.

0
Comment
Question by:Taras
  • 8
  • 3
  • 2
  • +1
14 Comments
 

Author Comment

by:Taras
Comment Utility
To clarify further:

Query on which my subform is based coming from LineItem table.
In this table third filed is LineitemID - auto number type.

I saw when I started undo process and when recordset which is  Subform record-source  is deleted that my record from Lineitem table is gone too.
 I understand that is for reason that I am deleting query which is based on records from table LineItem that is ok.
When I try to do undo changes  I got error msg “Field can not be update” in the loop for  third filed.
 I assume that undo trying to put back value in query(which is record-source for my subform).
But  as query is based on LineItem  table I am actually  trying to restore record in table LineItem.
As LineItemID is  auto number type  field  it will not accept old value.
How to go around this.? Or maybe it is not issue.


Probably I made this more confused now with this clarification.
It goes like: If you want to make mass of something just clarify that.


Taras.  

0
 

Author Comment

by:Taras
Comment Utility
I thought that this is would not be so difficult.
Any ideas?
Why everybody is silent?
Taras.
0
 

Author Comment

by:Taras
Comment Utility
I solved this issue.
Thanks
Taras
0
 

Author Comment

by:Taras
Comment Utility
I was wrong this problem still exists.
I made some changes on auto-numbers field type.
I changed them to long and used DMAX function on insert to increase them but it didn’t help.
It looks like UndoSub doesn’t like my query on which my subform is based. Query is made from two tables tblMain and tblSub. From tblMain I am using primary key “ServiceID”  which is foreign key in sub table. I need to use that field from main table not from tblSub, as it is connected with my line item update on Main-Sub form environment.
 So It looks like when my undosub wants to update my Subform record source(my query) it tries to update primary key in tblmain but that value was updated previous in UndoMain and I am getting error field is not updatable.
 
Any idea how to go around this?

Taras
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Taras,

  Where do you stand on this?

Jim.

PS.  Did get you e-mail, but accidently deleted it :( before I could respond.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hi Taras,

Your Save/Undo mechanism looks a bit odd.
I would expect (as you use temp tables), that:
1) the SaveMain would be:
Delete "Real" Main and "Real" Sub entries and add the "Temp" Main entries to "Real"
2) the SaveSub would be:
Delete "Real" Sub entries and add the "Temp" Sub entries to "Real"

The Undo would work like:
1) the UndoMain would be:
Delete "Temp" Main and "Temp" Sub entries and add the "Real" Main entries
2) the UndoSub would be:
Delete "Temp" Sub entries and add the "Real" Sub entries to "Temp"

Where does my thinking "go wrong"?

Nic;o)
0
 

Author Comment

by:Taras
Comment Utility
Hi Nico5038.

The code for Save/Undo is not my I found that as Microsoft example. Maybe my environment is not good to apply this ?
To explain it better my main form is bound  to tblMain and  subform is based on query that coming from tblSubmain those two tables are in one to many relation as they in my case present service and lineitem of this service. When mainform and subform are opened its fields are filled in. At that moment, before I made any change I want to save data in temp area in case that I need undo, this is where “SaveMain” and “SaveSub” starts. That is reason that on current event of Main form they  put SaveMain and SaveSub to move data from forms in temp area. I thing it is proper step.
 After user made changes on mainform or subform (before he save this changes) he suddenly wants to undo changes.
 This is moment where data from “temp area “ getting back on Mainform  and subform first and then back in tables- “real” area.

I think that this is not bad concept, but for some reason it is not working on undo submain.

Taras.

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Taras
Comment Utility
Hi Nico5038.

The code for Save/Undo is not my I found that as Microsoft example. Maybe my environment is not good to apply this ?
To explain it better my main form is bound  to tblMain and  subform is based on query that coming from tblSubmain those two tables are in one to many relation as they in my case present service and lineitem of this service. When mainform and subform are opened its fields are filled in. At that moment, before I made any change I want to save data in temp area in case that I need undo, this is where “SaveMain” and “SaveSub” starts. That is reason that on current event of Main form they  put SaveMain and SaveSub to move data from forms in temp area. I thing it is proper step.
 After user made changes on mainform or subform (before he save this changes) he suddenly wants to undo changes.
 This is moment where data from “temp area “ getting back on Mainform  and subform first and then back in tables- “real” area.

I think that this is not bad concept, but for some reason it is not working on undo submain.

Taras.

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I see, it's "the other way around".
Personally I prefer to work on the temp tables as that can be "inconsistent" and no reports or other users will get faulty results.

BTW is one of your tables having an autonumber?
If so, you need to exclude this from the insert !

Nic;o)
0
 

Author Comment

by:Taras
Comment Utility
Hi Nico5038.
I had auto numbers before (part of primary key in Main and primary key in submain table ) but I changed them for Number –Long type and using DMAX(“Field”,”Table”) + 1 on form open event. It works much better then autonumber, less problems.
Taras.
0
 

Author Comment

by:Taras
Comment Utility
Hi Jim Dettman.
As you can see this issue is still opened. I made some tries and it did not work.
I am still trying.
As they say 99% is in practice 1% is talent.
Taras.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Taras,

  Can you send the MDB to me zipped?  My e-mail is in my member profile (click on my member name).

Jim.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
Comment Utility
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now