Open Record from Union Query

[kingnothingz]

Hi Experts,

I have an union query getting data from two separate tables. I'm using this union query to search for records using simple text search.

Query looks like this:

SELECT table1.Name, table1.Surname, table1.DOB, table1.id, table1.diag
FROM table1;
UNION
SELECT table2.Name, table2.Surname, table2.DOB, table2.id, table2.diag
FROM table2;

The form i used to build the search looks like this -->

The result set of the search would have records from both the tables.

Both table1 and table2 have forms built on them to be able to view/edit records.

The ultimate aim is for the user to search, select the record and open the particular form with the selected record to view more details.

I have no clue how to open the form based on the search.

Can you please help me out??

 

[RainLover]

You have two Tables that look exactly the same.

Why do you have two instead of just the one.

 

[kingnothingz]

Its a long story...

I didn't design this application, it is handed down to me from some one.

i'm not allowed to make modifications to the tables, just build forms on top of them.

The tables are similar in most aspects except for some unique fields in each table.

 

[RainLover]

Do you realise that a Union query is not updateable.

So in your form you will not be able to edit or add any new records.

 

[kingnothingz]

Yep, I know that.

I'm only using the union query to display from two tables. I'm not adding, editing any records in the form(searchform). Thats the reason I want to open another form to be able to edit records.

 

[RainLover]

In that case which Table do you want to edit.

The Recordsource for your sub form should be the table that you want to edit. So I do not see the need for a union query at all.

Then if your Main form holds the data from the other table then you need to set the Links in the sub form. This is most likely the field ID.

Sorry for not answering your problem more fully but I am still trying to understand what is going on.

 

[kingnothingz]

That's quite alright.

To make things clear, my search form is based on the example (dbSearchExample.mdb) found in this thread.

The Recordset for this form is my union query.

I hope this answers your question.

 

[RainLover]

I am guessing that you are clicking on search and not getting the result you were expecting.

I would need to look at the code behind your search button to see where the problem is.

Better still can you post a cut down version of what you have.

 

[kingnothingz]

No, I get the results. The search part of it works beautifully.

My only problem is I dont know how to open the selected record in another form for editing.

Anyways, heres the code for the search.

Private Sub cmdSearch_Click()
' Update the record source to get data from UnionQuery (qrySearchTables)
    Me.frmSubSearchResults.Form.RecordSource = "SELECT * FROM qrySearchTables " & BuildFilter
' Requery the subform
    Me.frmSubSearchResults.Requery

End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant

    varWhere = Null  ' Main filter
   
       
    ' Check for LIKE First Name
    If Me.txtFirstName > "" Then
        varWhere = varWhere & "[Name] LIKE """ & Me.txtFirstName & "*"" AND "
    End If
    
    ' Check for LIKE Last Name
    If Me.txtLastName > "" Then
        varWhere = varWhere & "[Surname] LIKE """ & Me.txtLastName & "*"" AND "
    End If
    
    ' Check for LIKE DOB Name
    If Me.txtDob > "" Then
        varWhere = varWhere & "[DOB] LIKE """ & Me.txtDob & "*"" AND "
    End If
       
    ' Check for ID
    If Me.txtId > "" Then
        varWhere = varWhere & "[id] = " & Me.txtId & " AND "
    End If
    
    ' Check for Diag
    If Me.txtDiag > "" Then
        varWhere = varWhere & "[diag] = " & Me.txtDiag & " AND "
    End If
        
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

 

[RainLover]

You could change the view of the sub form to Continious instead of Datasheet.

Then using the Toolbox add a Command button to the sub form. The wizard will help you open a Form where you can set it to open the matching record.

This needs to be done on the Sub as you may have more than one matching record.

 

[kingnothingz]

Hi RainLover,

This works great!!

Again, i'll have to have two command buttons to open two separate forms

Is there anyway to get around this?

After making changes that you suggested, the form looks like this.

 

[RainLover]

If you want the choice of Forms you will need two buttons.

But if you want both forms to open you simply add the code to open the second form to the existing event.

 

[kingnothingz]

Hi RainLover,

Based on your suggestions, I added the button and modified the code that the wizard created to open separate forms.

The final code looks like this:

Private Sub cmdOpenRecord_Click()
On Error GoTo Err_cmdOpenRecord_Click

    Dim stDocName As String
    Dim stDocName1 As String
    Dim stLinkCriteria As String

    stDocName = "table1"
    stDocName1 = "table2"
    
        
    stLinkCriteria = "[id]=" & Me![id]
    If [diag] <> "17" Then

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    DoCmd.OpenForm stDocName1, , , stLinkCriteria
    End If

Exit_cmdOpenRecord_Click:
    Exit Sub

Err_cmdOpenRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenRecord_Click
    
End Sub

This works!!


Thanks for your help!!!!

 

[RainLover]

Glad we got there in the end.