Show results from multiple tables in one listBox

[Surka]

Dear all,

I have multiple tables (Desktops and Telephones)
A search form, to search into those tables (It searches by "User")
The search form contains a listbox that shows results (listPC)
And the following code:

Option Compare Database
Dim strUserPC As String, strUserTel As String
Dim db As DAO.Database, rsUserPC As DAO.Recordset ', rsUserTel As DAO.Recordset
Private Sub txtSearch_LostFocus()
    strUserPC = ""
    strUserTel = ""
 
    strUserPC = "SELECT ALL Desktops.Serial " _
    & "FROM Desktops " _
    & "WHERE Desktops.User LIKE '" & "*" & Me.txtSearch.Value & "*" & "' " _
    & "ORDER BY Desktops.Serial"
 
    strUserTel = "SELECT ALL Telephones.Serial " _
    & "FROM Telephones " _
    & "WHERE Telephones.User LIKE '" & "*" & Me.txtSearch.Value & "*" & "' " _
    & "ORDER BY Telephones.Serial"
 
    Me.listPC.AddItem Item:="User;Device", Index:=0
    Me.listPC.AddItem Item:=Me.txtSearch.Value, Index:=1
 
    Set db = CurrentDb()
    Set rsUserPC = db.OpenRecordset(strUserPC)
    Set rsUserTel = db.OpenRecordset(strUserTel)
 
         If (rsUserPC.RecordCount > 0) Then
            UpdateList
         End If
 
         If (rsUserTel.RecordCount > 0) Then
            UpdateList
 
            Else
                MsgBox "No results", vbOKOnly, "No results"
                Me.txtSearch.SetFocus
 
         End If
 
    rsUserPC.Close
    rsUserTel.Close
 
    Set db = Nothing
 
End Sub
Sub UpdateList()
 
    Me.listPC.RowSource = strUserPC
 
    'Me.listPC.RowSource = strUserTel
 
End Sub

It works, but I have one problem. It only shows telephones or desktop, not both. It deppends on which line inside UpdateList is first.

That example searches into 2 tables:
-Telephones
-Desktops

And searches by "user".

I want to list all telephones and desktops that a user has assigned.

What should I change to show both results?

Thanks,

Sur.

 

[pbaldy]

Not sure there should be two tables, but you can get both with a UNION query. Basically your two queries together separated by

UNION ALL

 

[Mihail]

Take a look to the Property Sheet.
Set the Column Count property to 2

 

[Surka]

It worked with UNION, but how can I do to have 1 variable per table? As the code shows. Because the search results will be compared with a value from a text box.

Mihail, thanks for the suggestion, already have it that way.

 

[pbaldy]

I'm not clear on the question. Like I said, you can use the same 2 SQL statements, separated by UNION ALL.

 

[Surka]

When I use UNION or UNION ALL I get : Syntax error in query. Incomplete query clause here:

Set rsUserPC = db.OpenRecordset(strUserPC)

 

[pbaldy]

Would need to see how the SQL is created. You can use this to see the finished SQL with this technique:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Surka]

Sorry, I really did not remember which problem appeared when using UNION. Now I test it. I need to order results in individual sections and while using UNION only one ORDER BY is allowed.

I read something about combining views to use 2 ORDER BY, but I don't get the idea, and how to code it.

So UNION accomplish the original thread task, but I need to order the result different ways.

Thanks Baldy.

 

[Surka]

I figured out, that there is no need to order results. But is there a solution for this problem? Using UNION with more than 1 ORDER BY.

Thanks.

 

[pbaldy]

Typically I can put fields into each select clause that let the order by clause sort records appropriately.