[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Main-SubFormUndo

Posted on 2002-06-07
14
Medium Priority
?
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
14 Comments
 

Author Comment

by:Taras
ID: 7062524
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
ID: 7069603
I thought that this is would not be so difficult.
Any ideas?
Why everybody is silent?
Taras.
0
 

Author Comment

by:Taras
ID: 7069917
I solved this issue.
Thanks
Taras
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Taras
ID: 7073277
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 58
ID: 7078580
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
ID: 7080802
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
ID: 7083454
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
 

Author Comment

by:Taras
ID: 7083456
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
ID: 7084323
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
ID: 7084702
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
ID: 7091613
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 58
ID: 7092313
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
ID: 7230544

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
ID: 7248140
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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