Search Compile Error

[chrisguk]

Hi,

I keep trying to run this search script below, but it keeps returning a compile error. are there any gurus out there that can figure it out. I have tried all weekend and cant see what it is:

Private Sub cmdSearch_Click()
    Dim strsiteRef As String
    Dim strSearch As String
    
'Check txtSearch for Null value or Nill Entry first.
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a Valid Site Code!", vbOKOnly, "Oops Something Was Wrong!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
        
'Performs the search using value entered into txtSearch
'and evaluates this against values in siteid
      
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("sitesid")
    DoCmd.FindRecord Me!txtSearch
        
    txtSearch.SetFocus
    strsiteRef = sitesid.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found sets focus in siteid and shows msgbox
'and clears search control
    If strsiteRef = strSearch Then
        MsgBox "Match Found For Side Code: " & strSearch, , "Your site has been found!"
        sitesid.SetFocus
        txtSearch = ""
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
           MsgBox "Match Not Found For Site: " & strSearch & " - No Site Found Please Try Again.", _
            , "OOPS NO SITE FOUND!"
            txtSearch.SetFocus
    End If
End Sub

 

[JamesMcS]

Give us a pointer - what line does it fail on? Or if not a particular line, what is the error?

 

[chrisguk]

Give us a pointer - what line does it fail on? Or if not a particular line, what is the error?

Errors on
txtSearch.SetFocus

 

[boblarson]

If txtSearch is a control, then try this instead:

Me.txtSearch.SetFocus

with the period instead of the bang.

 

[chrisguk]

If txtSearch is a control, then try this instead:

Me.txtSearch.SetFocus

with the period instead of the bang.

Sorry Bro my bad it is:

sitesid.SetFocus

And the compile error is : Method or data member not found

 

[boblarson]

Okay, so you have:

txtSearch.SetFocus
strsiteRef = sitesid.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

Which, if you just use this instead:

strsiteRef = Me.sitesid
strSearch = Me.txtSearch


you won't have a set focus problem because .Text needs focus but using the default (which is .Value) doesn't.

Also, remember to use Me. before any control names because it helps Access to know that you want to refer to something on the current form/report. If you just use

txtSomething = 4

Then sometimes it just doesn't like it. So

Me.txtSomething = 4

would be the way to go.

 

[chrisguk]

Okay, so you have:

txtSearch.SetFocus
strsiteRef = sitesid.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

Which, if you just use this instead:

strsiteRef = Me.sitesid
strSearch = Me.txtSearch


you won't have a set focus problem because .Text needs focus but using the default (which is .Value) doesn't.

Also, remember to use Me. before any control names because it helps Access to know that you want to refer to something on the current form/report. If you just use

txtSomething = 4

Then sometimes it just doesn't like it. So

Me.txtSomething = 4

would be the way to go.

This code seems plagued with errors now and now I wondering if I am using the most efficient way to search for a record.

Im getting an error now stating there is no field named sitesid in the current record.
and it stops on this line:

DoCmd.GoToControl ("sitesid")

 

[boblarson]

1. Is this just a single form or does it have a subform and one of the controls being referred to is on the subform?

2. Is it a single form view, continuous, or datasheet view?

3. If all of your controls are named correctly as used in the code, you can modify your code like this to do the search:

Private Sub cmdSearch_Click()
    [COLOR=red]Dim rst As DAO.Recordset
[/COLOR]    Dim strSearch As String

    [COLOR=red]strSearch = Me.txtSearch
[/COLOR]    
    'Check txtSearch for Null value or Nill Entry first.
    [COLOR=red]If Len(strSearch & vbNullString) = 0[/COLOR] Then
        MsgBox "Please enter a Valid Site Code!", vbOKOnly, "Oops Something Was Wrong!"
        [COLOR=red]Me.[/COLOR]txtSearch.SetFocus
        Exit Sub
    End If
    '---------------------------------------------------------------
    'Performs the search using value entered into txtSearch
    'and evaluates this against values in siteid
[COLOR=red]    Me.Filter = ""
    Me.FilterOn = False[/COLOR]
 
[COLOR=red]    Set rst = Me.RecordsetClone[/COLOR]
[COLOR=red]    rst.FindFirst "[SitesID] = " & strSearch
    If rst.NoMatch Then[/COLOR]
        MsgBox "Match Not Found For Site: " & [COLOR=red]strSearch[/COLOR] & " - No Site Found Please Try Again.", _
               , "OOPS NO SITE FOUND!"
        [COLOR=red]Me.[/COLOR]txtSearch.SetFocus
    Else
        MsgBox "Match Found For Side Code: " & [COLOR=red]strSearch[/COLOR], , "Your site has been found!"
        Me.sitesid.SetFocus
        [COLOR=red]Me.txtSearch = Null[/COLOR]
    End If
 
[COLOR=red]    rst.Close
    Set rst = Nothing[/COLOR]

End Sub

But make sure that the control names are what you think they are.

 

[chrisguk]

The form is single form but has a subform on it. The controls are correct but with your modification there is an error here:

Me.sitesid.SetFocus

 

[boblarson]

The form is single form but has a subform on it. The controls are correct but with your modification there is an error here:

Me.sitesid.SetFocus

If there is an error there, then the control either isn't named sitesid (maybe siteid without the s??) or it is on the other form than the code is on. So, it could be on the subform when the main form has this code or if you have this code on the subform and the control is really on the main form.