CODEWITHSUNDEEP
sqlms-accessvba
connor
connor

Reputation: 5

Search using multiple criteria

I have two textboxes I want to search a table. I am putting the search through a query which I believe to be the issue. the code should take the criteria from both textboxes and search one table. after that the rest of the textboxes will fill with the recordset the is closest to the search.

I get a error at RstRecSet.MoveLast I get a compile error that says no current record. I do have a record like what I type into the box its not picking it up though

When I add in the exact name of the record it finds it no problem. It's almost as if it's treating the like operator as if it were a =.

Here is my code:

Private Sub Command514_Click()
'DoCmd.Close
'DoCmd.OpenForm "frmContacts", acNormal
    Set RstRecSet = Nothing
    Set db = CurrentDb
    Dim searchNum As String
    Dim searchName As String
    searchNum = txtGroupNr
    searchName = txtGroupName
 On Error Resume Next
If IsNull(txtGroupNr) Or txtGroupNr = "" Then

        Me.txtGroupName.BackColor = vbRed
        Forms!frmGroupHeader!txtGroupNr.SetFocus

    Else
        'strSearchICN = txtGroupNr
        Set db = CurrentDb
        Me.txtGroupName.BackColor = vbWhite
        Set RstRecSet = db.OpenRecordset("Select * from tblGroupHeader Where GroupNum  Like '" & searchNum & "' And GroupName  Like '" & searchName & "';", dbOpenDynaset)
        RstRecSet.MoveLast
        intMaxCount = RstRecSet.RecordCount
        RstRecSet.MoveFirst
      ' Exit Sub
   End If

     If RstRecSet.EOF Then


        Me.txtGroupName.BackColor = vbRed
        Forms!frmGroupHeader!txtGroupNr.SetFocus

    Else
       Call DisplayFields

    End If
End Sub

Here is the solution:

    Private Sub Command514_Click()
'DoCmd.Close
'DoCmd.OpenForm "frmContacts", acNormal
    Set RstRecSet = Nothing
    Set db = CurrentDb
    Dim searchGroup As String
    Dim searchName As String

If IsNull(txtgroupSearch) Or txtgroupSearch = "" Then

        Me.txtGroupName.BackColor = vbRed
        Forms!frmGroupHeader!txtGroupNr.SetFocus

    Else
        'searchNum = txtGroupNr
        searchGroup = txtgroupSearch

        Set db = CurrentDb
        Me.txtGroupName.BackColor = vbWhite
        Set RstRecSet = db.OpenRecordset("Select * from tblGroupHeader Where groupName like '*" & searchGroup & "*' or groupNum like '*" & searchGroup & "*';", dbOpenDynaset)
            If RstRecSet.EOF And RstRecSet.BOF Then
            MsgBox ("NO RECORDS!")
                Exit Sub
            End If
        RstRecSet.MoveLast
        intMaxCount = RstRecSet.RecordCount
        RstRecSet.MoveFirst
      ' Exit Sub
   End If

     If RstRecSet.EOF Then
        Me.txtGroupName.BackColor = vbRed
        Forms!frmGroupHeader!txtGroupNr.SetFocus

    Else
        Call DisplayFields

    End If
End Sub

Upvotes: 0

Views: 82

Answers (1)

JNevill
JNevill

Reputation: 50019

I believe the problem is in your SQL. The error suggests that you have no records. Before calling .MoveLast on the recordset you could put in a quick test with:

If RstRecSet.EOF and RstRecSet.BOF Then 
    msgbox("NO RECORDS!")
    exit sub
End if

Back to the SQL. You wrote "Select * from tblGroupHeader Where GroupNum and GroupName Like '" & searchNum & GroupName & "';" which, if you chose "1" as the GroupNum and "Physics" as the Groupname, would come out to something like Select * from tblGroupHeader Where GroupNum and GroupName Like '1Physics'; Which makes no sense

Instead: "Select * from tblGroupHeader Where GroupNum Like '" & searchNum & "' and GroupName Like '" & GroupName & "';" Which would look like Select * from tblGroupHeader Where GroupNum Like '1' and GroupName Like 'Physics';

I've found it helpful to put my SQL into a variable first and then write that variable to the Immediates window with a Debug.Print SQL so that I can just copy and paste back into my database to make sure that everything makes sense.

Upvotes: 1

Related Questions