Filter Multiple Tables And Merge The Result In One Editable Table

[larmenien]

Hello,

I have 4 main tables in my database. All 4 have the same fields so except for the number of lines and the actual data, they're identical. With some help, I wrote a code that filters the 4 tables according to one of the fields. An inputbox appears where the user writes his name and the 4 tables are then filtered with that name (which appears in the field "Buyer"). The table names are "CiastaSystemsRigs", "BAStructuralRigs", "SystemsSupplierRigs" and "StructuralSupplierRigs".

The result is that the tables are filtered 1 by 1 and the ones that have at least one match, open with the filtered results. So if the name exists in all 4 of them, 4 windows will open up. I can then edit each table and the changes will be saved normally.

What I would like, is to append all the filtered tables into a single one, and still be able to edit it. So I guess a query wouldn't do the trick since they're not editable.
The resulted editable table should update each of the respective 4 database tables. The objective is not to create a new table.

My code may have mistakes, but it compiles and works.

Thank you in advance for your help!

Here's my code:

Option Compare Database
Option Explicit

Public Function FindNAME()

Dim AMO, critere As String
Dim DB As Database
Set DB = CurrentDb
Dim temp1, temp2, temp3, temp4 As QueryDef


AMO  = InputBox("Find Data for which agent?" & (Chr(13) & Chr(10))  & (Chr(13) & Chr(10)) & "Please enter full or partial name"  & _
     (Chr(13) & Chr(10)) & (Chr(13) & Chr(10))  & "Use wildcard * before and/or after" & (Chr(13) & Chr(10))  & "to help your search")
critere = "Buyer like '" & AMO & "*'"

If Not IsMissing(DB.QueryDefs("temp1")) Then
    DoCmd.DeleteObject acQuery, "temp1"
End If
If Not IsMissing(DB.QueryDefs("temp2")) Then
    DoCmd.DeleteObject acQuery, "temp2"
End If
If Not IsMissing(DB.QueryDefs("temp3")) Then
    DoCmd.DeleteObject acQuery, "temp3"
End If
If Not IsMissing(DB.QueryDefs("temp4")) Then
    DoCmd.DeleteObject acQuery, "temp4"
End If

Set  temp1 = DB.CreateQueryDef("temp1", "SELECT 1_CiastaSystemsRigs.* FROM  1_CiastaSystemsRigs WHERE Buyer LIKE '*" & AMO & "*';")
Set temp2 = DB.CreateQueryDef("temp2", "SELECT * FROM 2_BAStructuralRigs where Buyer LIKE '*" & AMO & "*';")
Set temp3 = DB.CreateQueryDef("temp3", "SELECT * FROM 3_SystemsSupplierRigs where Buyer LIKE '*" & AMO & "*';")
Set temp4 = DB.CreateQueryDef("temp4", "SELECT * FROM 4_StructuralSupplierRigs where Buyer LIKE '*" & AMO & "*';")

Dim RS As Recordset
Set RS = DB.OpenRecordset("1_CiastaSystemsRigs", dbOpenSnapshot)

RS.MoveFirst
RS.FindFirst critere
If RS.NoMatch Then
    RS.Close
Else
DoCmd.OpenQuery "temp1"
End If
    
    Set RS = DB.OpenRecordset("2_BAStructuralRigs", dbOpenDynaset)
    RS.MoveFirst
    RS.FindFirst critere
    If RS.NoMatch Then
        RS.Close
    Else
    DoCmd.OpenQuery "temp2"
    End If
        
        Set RS = DB.OpenRecordset("3_SystemsSupplierRigs", dbOpenDynaset)
        RS.MoveFirst
        RS.FindFirst critere
        If RS.NoMatch Then
            RS.Close
        Else
        DoCmd.OpenQuery "temp3"
        End If
            
            Set RS = DB.OpenRecordset("4_StructuralSupplierRigs", dbOpenDynaset)
            RS.MoveFirst
            RS.FindFirst critere
            If RS.NoMatch Then
                RS.Close
            Else
            DoCmd.OpenQuery "temp4"
            End If
        

Set RS = Nothing

End Function

 

[MarkK]

You need one table called tRigs that has a RigType field. Possible values in that field are "CSystems", "BAStructural", and so on.
Then you just search the one table and vastly simplify everything.
Cheers,
Mark