Using a combobox to "rotate" a form through various histories/revisions (1 Viewer)

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
I thought this would be a simple one, but as with most of my task on my project, it is causing me a headache.

I have my parent/master form "JobQuote". This form has all of the main information such as JobName and JobID. Within this form is a tab control with 5 tabs that each have sub forms (some of them have several). Everything is bound via JobID in either its underlying query, or via master/child links. All in all, this form is complete and runs wonderfully.

What I am trying to add to it, is the ability to have multiple "versions" of itself through Revisions. Here is the table structure:
Capture.PNG

Hopefully I did the above correctly. If so, on my parent form I want to have a text box linked to RevNum so that a number/short text may be entered. That would then populate a combobox also tied to RevNum and "filtered" by JobID. I would then like the form to "rotate" through its contents based on the selection in the Combobox, with empty being "base".

The idea is you start a job and enter all info required. You then later need to revise the job, but you need to retain all the previous info as a history. The info would hopefully stay from revision to revision, allowing simple edits and/or sweeping changes. You can then select which every revision you want to view via the combobox and the entire form would go to how it was under that revision number.

The simplest way of doing this would be to simply add a "-(dash)1" on our Quote Number and "force" a new JobID with all the same info every time we need a revision, but that seems super super sloppy.


I have done something similar on this form already, but haven't been able to get it to work for the entire form. The problem I think lies in the tab control. I did try making the entirety of the form (essentially just the tab control) a sub-form and tried that method, but everything broke, and broke pretty badly. I have not had a good experience dealing with tab controls within subforms.
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
Never thought to use the term audit. It's not quite what I am doing, but the general principle is the same. My main worry is causing massive bloat with hundreds and hundreds of duplicate records. But I guess no matter what I really do, I am going to have duplicates if I want to keep any form of history.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:37
Joined
Oct 29, 2018
Messages
21,357
Never thought to use the term audit. It's not quite what I am doing, but the general principle is the same. My main worry is causing massive bloat with hundreds and hundreds of duplicate records. But I guess no matter what I really do, I am going to have duplicates if I want to keep any form of history.
I think the difference between an audit trail and recreating records is an audit trail will only hold changes history; whereas, a duplicated record would store "all" fields data, whether they were changed or not. So, there should be less storage requirements when using an audit trail as compared to recreating records.
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
I think the difference between an audit trail and recreating records is an audit trail will only hold changes history; whereas, a duplicated record would store "all" fields data, whether they were changed or not. So, there should be less storage requirements when using an audit trail as compared to recreating records.
I read that article, and I don't quite think that would work. It may work in some select instances, but for my department it is very common to have a revision/addendum that does sweeping changes to a job and my boss wants the ability to view what the job was at every stage during the process which unfortunately is probably going to cause a lot of bloat due to duplicate records.

On some jobs it wouldn't be a problem. Less than 50 records would be duplicated. On our larger jobs however, it could be hundreds and hundreds if not break a thousand due to the size of the job and just how many major revisions it would go through.

Also just to clarify a little bit, I am not really referring to one or two changes. This is for construction and a project/job can go through multiple versions/rebids before the project is actually awarded and construction started. I need to somehow keep track of all that without breaking Access with the sheer amount of duplicates.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:37
Joined
Oct 29, 2018
Messages
21,357
I read that article, and I don't quite think that would work. It may work in some select instances, but for my department it is very common to have a revision/addendum that does sweeping changes to a job and my boss wants the ability to view what the job was at every stage during the process which unfortunately is probably going to cause a lot of bloat due to duplicate records.

On some jobs it wouldn't be a problem. Less than 50 records would be duplicated. On our larger jobs however, it could be hundreds and hundreds if not break a thousand due to the size of the job and just how many major revisions it would go through.

Also just to clarify a little bit, I am not really referring to one or two changes. This is for construction and a project/job can go through multiple versions/rebids before the project is actually awarded and construction started. I need to somehow keep track of all that without breaking Access with the sheer amount of duplicates.
You might eventually end up using SQL Server or the likes, if you end up handling/storing a huge amount of data.
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
You might eventually end up using SQL Server or the likes, if you end up handling/storing a huge amount of data.
I considered that possibility. I have actually been reading up on it just in case.
Thankfully this will all be text data, so while the bloat will get bad over time, it might remain manageable. I guess I wouldn't really know until the app has been in use for awhile and I can see just how quickly it approaches the 2G mark.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:37
Joined
Oct 29, 2018
Messages
21,357
I considered that possibility. I have actually been reading up on it just in case.
Thankfully this will all be text data, so while the bloat will get bad over time, it might remain manageable. I guess I wouldn't really know until the app has been in use for awhile and I can see just how quickly it approaches the 2G mark.
Probably sooner than you may think, just guessing...
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
Maybe I will start looking into SQL server more sooner rather than later then.

For now as far is this thread is concerned, I will just scrap the idea of a combo box and just modify how we do quote numbers. Taking the easy road!
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
For the record (with assistance from StackOverflow), the code I used to accomplish this is
Code:
Public Sub CreateRevisions()

    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Count       As Integer
    Dim Item        As Integer
    Dim Bookmark    As Variant
    Dim OldId       As Long
    Dim NewId       As Long
    
    ' Copy parent record.
    Set rstAdd = Forms.JobQuote.RecordsetClone
    Set rst = rstAdd.Clone
    
    ' Move to current record.
    rst.Bookmark = Forms.JobQuote.Bookmark
    OldId = rst!JobID.Value
    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                Else
                    .Value = rst.Fields(.Name).Value
                End If
            End With
        Next
        .Update
        ' Pick Id of the new record.
        .MoveLast
        NewId = !JobID.Value
    End With
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark
    
    ' Copy child records 1.
    ' If a subform is present:
    ' Set rstAdd = Me!subChild1.Form.RecordsetClone
    ' If a subform is not present, retrieve records from the child table:
    Set rstAdd = CurrentDb.OpenRecordset("Select * From tblProduct Where JobID = " & OldId & "")
    Set rst = rstAdd.Clone

    If rstAdd.RecordCount > 0 Then
        rstAdd.MoveLast
        rstAdd.MoveFirst
    End If
    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "JobID" Then
                        ' Skip master/child field.
                        .Value = NewId
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next

    ' Copy child records 2.
    ' If a subform is present:
    ' Set rstAdd = Me!subChild2.Form.RecordsetClone
    ' If a subform is not present, retrieve records from the child table:
    Set rstAdd = CurrentDb.OpenRecordset("Select * From tblFixtureTypes Where JobID = " & OldId & "")
    Set rst = rstAdd.Clone

    If rstAdd.RecordCount > 0 Then
        rstAdd.MoveLast
        rstAdd.MoveFirst
    End If
    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "JobID" Then
                        ' Skip master/child field.
                        .Value = NewId
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next
    
    ' Copy child records 3.
    ' If a subform is present:
    ' Set rstAdd = Me!subChild2.Form.RecordsetClone
    ' If a subform is not present, retrieve records from the child table:
    Set rstAdd = CurrentDb.OpenRecordset("Select * From tblContractorJob Where JobID = " & OldId & "")
    Set rst = rstAdd.Clone

    If rstAdd.RecordCount > 0 Then
        rstAdd.MoveLast
        rstAdd.MoveFirst
    End If
    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "JobID" Then
                        ' Skip master/child field.
                        .Value = NewId
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next
    
    ' Copy child records 4.
    ' If a subform is present:
    ' Set rstAdd = Me!subChild2.Form.RecordsetClone
    ' If a subform is not present, retrieve records from the child table:
    Set rstAdd = CurrentDb.OpenRecordset("Select * From tblDrawings Where JobID = " & OldId & "")
    Set rst = rstAdd.Clone

    If rstAdd.RecordCount > 0 Then
        rstAdd.MoveLast
        rstAdd.MoveFirst
    End If
    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "JobID" Then
                        ' Skip master/child field.
                        .Value = NewId
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next

    rst.Close
    rstAdd.Close
    
    ' Move to the new recordcopy.
    Forms.JobQuote.Bookmark = Bookmark
    
    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing

End Sub
I did learn that this WONT work with attachment fields. Once those were removed, it worked perfectly (credit to Gustav on stackoverflow for getting the original code I had working correctly)
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
One hiccup with this.

While my combos know all their data correctly, they dont remember their selection. Is there a way to get them to keep the value they hold when the records are all duplicated?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
26,999
Not sure what you meant by "don't remember their selection." Under what sequence of (high level) events in English do the combos "forget" their selection?
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
Fair enough Doc. The combos "remember" their value in terms of ID (I determined that by changing the bound column. So they are still on the right value, but not displaying the correct one after the copy. They are set to show the name tied to the ID, but after the copy they no longer show the name of the ID, just the ID.

The combos still have the correct list of names as a selection as well. Its just a weird event/bug I am going to have to mess with.
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
I am not the most articulate person when trying to describe things. So here are some pictures of before/after.
Before the copy:
Capture.PNG

After the copy:
Capture.PNG
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
26,999
OK, a combo box has a .RowSource, so look at the source including WHERE clauses. What does the copy operation do that affects any field in the .RowSource SELECT clause or WHERE clause? Does a combo.Refresh or combo.Requery help in that context?
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
The SQL statements are solid and unchanged as I just verified. Doing a refresh/requery does not do anything either.
I verified all tables involved have the correct data after the copy event as well. The query behind the combos also display the correct data, but the combo box doesn't.

It is interesting because they still have all the correct values in them and when I change which column is bound, they work but it does no good to see ID 1246 :).
 

June7

AWF VIP
Local time
Today, 05:37
Joined
Mar 9, 2014
Messages
5,423
Suggest providing db for analysis. Follow instructions at bottom of my post.
 

Users who are viewing this thread

Top Bottom