Using a union query for archived data - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Using a union query for archived data

Daniel Golden

16 months ago

I just finished watching the archive data tech help video. I have an archive database set up for my backend files based on the instruction in the video. What I am wanting to do is allow my users to see archived data in the frontend accde file which of course is no longer in the table which the search form pulls data from. I know the likely solution here is to link the archive tables and use a union query for the form so that it pulls data from both the backend db and the archive db tables. I am just wondering if this will cause ALL data in the form to become read only? The reason I ask is because the current data not in the archive db will still need to be updated.

	Kevin Robertson @Reply 16 months ago
What I usually do is have a button on the Form to toggle between current data and archived data. Basically changing the Record Source of the Form dynamically at runtime.

	Daniel Golden OP @Reply 16 months ago
@Kevin What code would you recommend for the button to perform this toggle sequence?

Kevin Robertson @Reply

16 months ago

This is some code from one of my databases.
The Record Source is a Union Quey which has a field called LogType which I use to determine what to show.
The value is set in txtLogType.
RequeryLog requery's my list.

Details

Private Sub btnView_Click()



    Dim ctrl As Control

    

    If btnView.Caption = "View Archive" Then

        btnView.Caption = "View Log"

        Me.Caption = "Database Log Archive"

        txtLogType = "Archive"

        ArchiveDate.Enabled = False

        btnArchive.Enabled = False

    Else

        btnView.Caption = "View Archive"

        Me.Caption = "Database Log"

        txtLogType = "Log"

        ArchiveDate.Enabled = True

        btnArchive.Enabled = True

    End If

    

    RequeryLog

    

    For Each ctrl In Me.Controls

        If ctrl.ControlType = acComboBox Then

            If Right(ctrl.Name, 6) = "Filter" Then

                ctrl.Requery

            End If

        End If

    Next

    

End Sub

This is the code from RequeryLog.

Details

Private Sub RequeryLog()



    Dim mySQL As String, whereStr As String

    



    mySQL = "SELECT * FROM LogUnionQ WHERE LogType=""" & txtLogType & """ "

    whereStr = ""

    

    If Not IsNull(cboMonthFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Month(LogDate)=" & cboMonthFilter

    End If

    

    If Not IsNull(cboYearFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Year(LogDate)=" & cboYearFilter

    End If

    

    If Not IsNull(cboTimeFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        If cboTimeFilter = "AM" Then

            whereStr = whereStr & "And Hour(LogTime)<12"

        Else

            whereStr = whereStr & "And Hour(LogTime)>=12"

        End If

    End If

        

    If Not IsNull(cboUsernameFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Username=""" & cboUsernameFilter & """"

    End If

            

    If Not IsNull(cboEmployeeFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Employee=""" & cboEmployeeFilter & """"

    End If

            

    If Not IsNull(cboDepartmentFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Department=""" & cboDepartmentFilter & """"

    End If

            

    If Not IsNull(cboDescriptionFilter) Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Description=""" & cboDescriptionFilter & """"

    End If

                

    If Not IsNull(txtNotesFilter) And txtNotesFilter <> "" Then

        If whereStr <> "" Then whereStr = whereStr & cboAndOr & " "

        whereStr = whereStr & "And Notes LIKE ""*" & txtNotesFilter & "*"""

    End If



    mySQL = mySQL & whereStr

    mySQL = mySQL & " ORDER BY LogDate DESC, LogTime DESC"

    Me.RecordSource = mySQL

    NowDisplaying = Me.Recordset.RecordCount

    

End Sub

Perhaps mow indepth and complex than you are looking for but it may help to inspire some ideas.

Daniel Golden OP @Reply

16 months ago

Kevin, this is much more complex than I was needing but I do thank you for the inspiration! I was able to come up with a simple few lines of VBA like this:

Private Sub SwitchBtn_Click()



    If Me.RecordSource = "Unit/Center Reviews" Then

        Me.RecordSource = "UCRevArchT"

        Me.Requery

        SwitchBtn.Caption = "View Current Records"

    Else

    If Me.RecordSource = "UCRevArchT" Then

        Me.RecordSource = "Unit/Center Reviews"

        Me.Requery

        SwitchBtn.Caption = "View Archived Records"

    End If

    End If

    

End Sub

	Kevin Robertson @Reply 16 months ago
Good job. This can be condensed slightly. `Private Sub SwitchBtn_Click() If Me.RecordSource = "Unit/Center Reviews" Then Me.RecordSource = "UCRevArchT" SwitchBtn.Caption = "View Current Records" Else Me.RecordSource = "Unit/Center Reviews" SwitchBtn.Caption = "View Archived Records" End If Me.Requery End Sub`

Thomas Gonder @Reply

16 months ago

I think Richard covered this in the extended cut, but it's been a while since I watched it.
I think like Kevin's solution; a button was used to toggle source.
It looks like Kevin nailed it, so I'm betting Richard's approach is very similar.
If you have a SAVE button, you might try disabling it if you don't want archived records changed.
If you haven't disabled all the SAVE methods in Access, you may consider changing the RecordsetType to a 2 (snapshot) when archive is the source.

Me.RecordsetType = 2

and set it back to "0" for a Dynaset of current records.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View