"Access could not recognize"

[Chrisopia]

The code below is for a search tool, but it returns an error if there isn't any data in the CompanyName field. To get around this I want to divert it to the LastName if there isnt anything in CompanyName.


I thought I could create a variable (CompLast) to use instead of using either CompanyName or LastName in the search tool.
Obviously this wasn't the way to go... what can I do?

Private Sub QuickSearch_AfterUpdate()
    
    Dim CompLast As String
    CompLast = [CompanyName]
    If IsNull(Me![CompanyName]) Then
    CompLast = Me![LastName]
    End If
    
DoCmd.Requery
Me.RecordsetClone.FindFirst "CompLast = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If
End Sub

 

[pono1]

You may want to consider adding another layer or two to the process; it may make things slightly more manageable...

Very rough...just to get you thinking along these lines...

Private Sub QuickSearch_AfterUpdate()

    If Len(Me.CompanyName) Then
      SearchByCompanyName(Me.CompanyName)
    Else
      SearchByLastName(Me![LastName])
    End If
    
End Sub


Sub SearchByCompanyName(companyName as string)

  Me.RecordsetClone.FindFirst "CompanyName = '" & companyName & "'"
  If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
  Else
    MsgBox "Could not locate " & companyName
  End If

End Sub

Sub SearchByLastName(lastName as string)

  Etc.

End Sub

Regards,
Tim