GoToRecord (1 Viewer)

[tarcona]

Hi, in my main form I have company information (Name, Address, City, State, etc). I am searching a subform. My subform contains Contact information (First Name, Last Name, Title, etc). I searched for several weeks on this issue and I have had no success. This is my code:

    Dim strSQL As Variant
    Dim l_strAnd As String
    Dim RS As DAO.Recordset
    Set RS = Me.RecordsetClone
    If (Me.txtSearchClientID & "" = "") And (Me.txtSearchFirst & "" = "") And (Me.txtSearchLast & "" = "") And (Me.txtSearchCompany1 & "" = "") Then
        ' If the search criteria are Null, use the whole table as the RecordSource.
        Me.RecordSource = "Accounts"
    Else
        l_strAnd = ""
        strSQL = "SELECT distinctrow Accounts.* " _
            & "FROM Accounts INNER JOIN ClientInformation " _
            & "ON Accounts.[Account ID] = ClientInformation.[Account ID] " _
            & "WHERE "
                        
        If Me.txtSearchClientID.Value & "" <> "" Then
            strSQL = strSQL & l_strAnd & "ClientInformation.[Client ID] = " & Me.txtSearchClientID
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchCompany1.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "Accounts.[Company] Like '" _
                & Me.txtSearchCompany1.Value & "*'"
            l_strAnd = " AND "
        End If
        If Me.txtSearchFirst.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[First Name] Like '" _
                & Me.txtSearchFirst.Value & "'"
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchLast.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[Last Name] Like '" & Me.txtSearchLast.Value & "'"
        End If
        
        On Error Resume Next
        Me.RecordSource = strSQL
        If Me.Recordset.RecordCount = 0 Then
            MsgBox "Your search returned no results.  Please check the spelling and try again.", vbInformation, "No Records"
            Me.RecordSource = "Accounts"
            RS.Close
            Set RS = Nothing
        End If
        
        If Me.Recordset.RecordCount > 0 Then
            Me.ClientInformation_Subform1.SetFocus
            DoCmd.GoToRecord , , "[Last Name] = '" & Me.txtSearchLast.Value & "'"

Currently, after the search is performed, the searched word is found with the rest of the contacts that belong to the specific company. And the first record is selected; not the matched record. What would be the code so the matched record is selected after the search?

DoCmd.GoToRecord , , "[Last Name] = '" & Me.txtSearchLast.Value & "'"

This portion of the code doesnt work and I want to edit this so it does work. Thanks.

 

[tarcona]

? Can anyone help ?

 

[ErikSnoek]

Change

DoCmd.GoToRecord , , "[Last Name] = '" & Me.txtSearchLast.Value & "'"

To

    RS.FindFirst "[Last Name] = '" & Me.txtSearchLast.value & "'"
    If Not RS.NoMatch Then
        Me.Bookmark = RS.Bookmark
    End If

 

[tarcona]

That still goes to the first record in the list. In advance, thank you for your help.

 

[ErikSnoek]

Weird. Maybe try using

RS.FindFirst "[Last Name] LIKE '" & Me.txtSearchLast.value & "'"

since thats what you do in your recordsource..

 

[tarcona]

Yeah, that is still not working. I dont know. I used the DoCmd.GoToRecord, , acLast and it worked and went to the last record in the list.
Do you have any suggestions on doing a GoToRecord function?

 

[ErikSnoek]

Well the snippet I posted basically is some sort of GoToRecord thing.. I think I know what's up, the RS is set to a Recordsetclone _before_ you set the new Recordsource so it has already changed when you do the FindFirst on it. Try this:

    Set RS = Me.RecordsetClone
    RS.FindFirst "[Last Name] = '" & Me.txtSearchLast.value & "'"
    If Not RS.NoMatch Then
        Me.Bookmark = RS.Bookmark
    End If

 

[tarcona]

Yeah, that didnt work again. Weird. Hmm.

 

[tarcona]

Again, I do appreciate you helping me out here. Thanks a lot.

 

[ErikSnoek]

Try the "Like" instead of "=" again and also add a little check to the code to see whats happening, like this:

    Set RS = Me.RecordsetClone
    RS.FindFirst "[Last Name] Like '" & Me.txtSearchLast.value & "'"
    If Not RS.NoMatch Then
        MsgBox "found a record. searched on: [Last Name] Like '" & Me.txtSearchLast.value & "'"
        Me.Bookmark = RS.Bookmark
    Else
        MsgBox "not found!!!!!"
    End If

 

[tarcona]

The message box appeared: found a record. searched on: [Last Name] Like '" & Me.txtSearchLast

but still, the record was not on the searched last name. The point of this is because I have highlighted rows. So when the record is selected, the record is highlighted. But when I do the search, the first record is highlighted; not the record being searched for.

 

[ErikSnoek]

But wait, the first record meets the criteria in the FindFirst right?

 

[tarcona]

No, sorry for not telling you this earlier.

When I perform a search, it works, but in addition to bringing up the matched record, it brings up the rest of the clients according to the Company.

So when I search Last Name: Johnson. It will bring up the records with the Last Name that is Johnson PLUS the other clients according to that company. It is weird, I know. But it works for now. The clients are ordered alphabetically.

 

[ErikSnoek]

You could also do this:

    Set RS = Me.RecordsetClone
    RS.FindFirst "[Last Name] Like '" & Me.txtSearchLast.value & "'"
    If Not RS.NoMatch Then
        DoCmd.GoToRecord , , acGoTo, RS.AbsolutePosition + 1
    End If

but it does the same as the bookmark thing really. I made a little test enviroment and both ways work for me

 

[tarcona]

Weird, I think it worked the first time, then I did another search and it didnt work and wont work again.

 

[tarcona]

Here, I will just send you an example of my DB. THanks for your help.

 

[ErikSnoek]

Was just about to ask that

 

[tarcona]

This is in Access 2000. Open AllRecordsSearch. Go to the right and click the contacts tab. Type in "Blass" for the last name and click search. As you can see, "Blass" comes up, BUT "Buchanan" is selected (first in the list).

 

[ErikSnoek]

Woops, we've constantly used the recordset of the mainform, not the subform

This is what it should be:

            Set RS = [ClientInformation Subform1].Form.RecordsetClone
            RS.FindFirst "[Last Name] Like '" & Me.txtSearchLast.Value & "'"
            If Not RS.NoMatch Then
                [ClientInformation Subform1].Form.Bookmark = RS.Bookmark
            End If

 

[tarcona]

Thank You! I Wanted This Done For A Long Time And I Couldnt Figure It Out! Thanks Sooooooo Much!