Search box display help

[joeKra]

Hi,
i am having a customers form which i want to be able to search by contracts, the problem i am having is that after refreshing the form to show the new results the search box will show the first contract # , how can i make it should show the same contract # as it was searched for , for reference, basically what the search is doing is that it appends the customer name and related tables to separate tmp tables.

any help would be appreciated.

enclosed the code

'Append filtered customer to the tmpTables


CurrentDb.Execute "Delete * From tmpCustomers"
CurrentDb.Execute "Delete * From tmpContracts"
CurrentDb.Execute "Delete * From tmpcontractDetails"


CurrentDb.Execute "INSERT INTO tmpCustomers Select * From Customers Where CustomerID = " & Me.cmbSearch
CurrentDb.Execute "INSERT INTO tmpContracts Select * from Contracts Where CustomerID = " & Me.cmbSearch
CurrentDb.Execute "INSERT INTO tmpcontractDetails SELECT ContractDetails.* FROM tmpContracts INNER JOIN " _
& "ContractDetails ON tmpContracts.ContractId = ContractDetails.ContractID;"
Me.Requery

here is the search box row source, it is based on another combo box

Private Sub cmbSearchby_AfterUpdate()
    Select Case Me.cmbSearchby

        Case 1  'Group
            Me.cmbSearch.RowSource = "SELECT Customers.CustomerId, Customers.GroupName FROM Customers;"
        
        Case 2  'Contract#
            Me.cmbSearch.RowSource = "SELECT Customers.CustomerId, Contracts.ContractId FROM Contracts " _
                                                              & "INNER JOIN Customers ON Contracts.CustomerId = Customers.CustomerId;"

        Case 3  'Trip#
            Me.cmbSearch.RowSource = "SELECT Customers.CustomerId, ContractDetails.ContractDetailID FROM Customers " _
                    & "INNER JOIN (ContractDetails INNER JOIN Contracts ON ContractDetails.ContractID = Contracts.ContractId) " _
                    & "ON Customers.CustomerId = Contracts.CustomerId;"

    End Select
     Me.cmbSearch.Requery
    Me.cmbSearch.SetFocus
End Sub

 

[mdlueck]

the problem i am having is that after refreshing the form to show the new results the search box will show the first contract # , how can i make it should show the same contract # as it was searched for

Here you go...

Relocate Previously Selected Record in a Multiple Items Form
http://www.access-programmers.co.uk/forums/showthread.php?p=1178436#post1178436

 

[joeKra]

Thanks for reply.. i will give it a try