Hi, all,

    I received the Yehuda reply, and change the my VBA in statement as 

who's said, but it is still no work. The error message is show that " 

Runtime error: 3077". At the debug mode, the yellow pointer is pointing 

at "rs.FindFirst criteria". How come and how to solve this problem.

=A1@

    If my code is incorrect, could you give your suggest/better code?



Raingo Chiu



Original Code:



Option Compare Database

Option Explicit



Private mFlg As Boolean

Private Sub Form_Load()

    mFlg =3D False

End Sub





Private Sub Search_Click()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim criteria As String

   

    Set db =3D CurrentDb()

    Set rs =3D Me.Recordset.Clone

    rs.Bookmark =3D Me.Bookmark



      criteria =3D "Device LLI Like ""*" & Me!txt & "*"""

      If mFlg =3D False Then

      rs.FindFirst criteria      <-------- Stop here, Error:3077

      mFlg =3D True

    Else

      rs.FindNext criteria

    End If



    If rs.NoMatch Then

      MsgBox ("No have matched recordes !")

    Else

      Me.Bookmark =3D rs.Bookmark

    End If

   

    rs.Close: Set rs =3D Nothing

End Sub





Private Sub txt_AfterUpdate()

    mFlg =3D False

End Sub

Error 3077 is The expression you entered isn't valid for the reason

indicated in the message. Make sure you entered field names and

punctuation correctly, and then try the operation again.



-----Original Message-----

From: Raingo [mailto:raingo@h...]

Sent: Thursday, July 26, 2001 10:37 AM

To: Access

Subject: [access] Feedback of search command code







Hi, all,

    I received the Yehuda reply, and change the my VBA in statement as 

=3D

who's said, but it is still no work. The error message is show that " 

=3D

Runtime error: 3077". At the debug mode, the yellow pointer is pointing

=3D at "rs.FindFirst criteria". How come and how to solve this problem.

=3DA1@=3D20

    If my code is incorrect, could you give your suggest/better code?



Raingo Chiu=3D20



Original Code:



Option Compare Database

Option Explicit



Private mFlg As Boolean

Private Sub Form_Load()

    mFlg =3D3D False

End Sub





Private Sub Search_Click()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim criteria As String

   =3D20

    Set db =3D3D CurrentDb()

    Set rs =3D3D Me.Recordset.Clone

    rs.Bookmark =3D3D Me.Bookmark



      criteria =3D3D "Device LLI Like ""*" & Me!txt & "*"""

      If mFlg =3D3D False Then

      rs.FindFirst criteria      <-------- Stop here, Error:3077

      mFlg =3D3D True

    Else

      rs.FindNext criteria

    End If



    If rs.NoMatch Then

      MsgBox ("No have matched recordes !")

    Else

      Me.Bookmark =3D3D rs.Bookmark

    End If

   =3D20

    rs.Close: Set rs =3D3D Nothing

End Sub





Private Sub txt_AfterUpdate()

    mFlg =3D3D False

End Sub

For your scrap book. Here?s an approach I like to use for single parameter 

searches. I?ve seen a bunch of stuff published about the ?Bookmark bug? so I 

don?t use the bookmark property.  Maybe someone else has some more 

information about that. Anyway, this is a relatively easy way to do single 

parameter searches.



On your main form (mine is called frmContacts with tblContacts as it?s 

record source), create a Find command button and name it cmdFind. In the 

click event of cmdFind, enter the following code (modified according to your 

object names of course):



Private Sub cmdFind_Click()

    'Open frmFind or Show All records, depending on user's last action.



    Dim strSQL As String



On Error GoTo HandleErr



    If Me!cmdFind.Caption = "&Show All" Then

        strSQL = "SELECT * FROM tblContacts ORDER BY LastName"

        Me.RecordSource = strSQL

        Me!cmdClose.SetFocus

        Me!cmdFind.Caption = "&Find..."

        Me!cmdFind.SetFocus

    Else

        DoCmd.OpenForm "frmFind", acNormal, , , , acDialog

    End If



ExitHere:

    Exit Sub



HandleErr:

    Select Case Err.Number

        Case Else

            MsgBox "Error " & Err.Number & ": " & Err.Description, 

vbCritical, "frmContacts.cmdFind_Click"

    End Select

End Sub



Now create a new form and call it frmFind. Add an option group and name it  

optChoose.  Add option buttons for the parameters you are likely to search 

for. Here I?m using Last Name, First Name, and Company.  Add a combo box to 

frmFind and name it cboSelect. Set the following properties of cboSelect:



Row Source:



SELECT DISTINCT [tblContacts].[LastName] FROM tblContacts ORDER BY 

[tblContacts].[LastName];



Default Value:



=[Form].[cboSelect].[ItemData](0)



Bound Column:



1



Add a command button to frmFind and name it cmdSearch.  Add the following 

code in the click event of cmdSearch and the AfterUpdate event of optChoose:



Private Sub cmdSearch_Click()

'Requery frmContacts based on item selected in cboSelect.



Dim strSQL As String



On Error GoTo HandleErr



    DoCmd.OpenForm "frmContacts"



    'Contruct SQL statement and assign to frmContacts.RecordSource

    With Forms!frmContacts

        If Len(Me!cboSelect & "") > 0 Then

            Select Case optChoose

                Case 1

                    strSQL = "SELECT * FROM tblContacts "

                    strSQL = strSQL & "WHERE LastName LIKE '" & Me!cboSelect 

& "'"

                Case 2

                    strSQL = "SELECT * FROM tblContacts "

                    strSQL = strSQL & "WHERE FirstName LIKE '" & 

Me!cboSelect & "'"

                Case 3

                    strSQL = "SELECT * FROM tblContacts "

                    strSQL = strSQL & "WHERE Company LIKE '" & Me!cboSelect 

& "'"

            End Select

            .RecordSource = strSQL

            !cmdFind.Caption = "&Show All"

        End If

    End With



    DoCmd.Close acForm, "frmFind"



ExitHere:

    Exit Sub



HandleErr:

    Select Case Err

        Case Else

            MsgBox Err & ": " & Err.Description, vbCritical, "Error in 

frmFind.Cick"

    End Select

    Resume ExitHere

    Resume



End Sub



Private Sub optChoose_AfterUpdate()

'Populate RowSource property of cboSelect



    Dim strSQL As String



On Error GoTo HandleErr



    Select Case optChoose

        Case 1

            strSQL = "SELECT DISTINCT LastName FROM tblContacts"

            strSQL = strSQL & " ORDER BY LastName"

        Case 2

            strSQL = "SELECT DISTINCT FirstName FROM tblContacts"

            strSQL = strSQL & " ORDER BY FirstName"

        Case 3

            strSQL = "SELECT DISTINCT Company FROM tblContacts"

            strSQL = strSQL & " ORDER BY Company"

    End Select



    With Me!cboSelect

        .Value = Null

        .RowSource = strSQL

        .Requery

        .Value = .ItemData(0)

    End With



ExitHere:

    Exit Sub



HandleErr:

    Select Case Err

        Case Else

            MsgBox Err & ": " & Err.Description, vbCritical, "Error in Form 

frmFind." _

            & "optChoose_AfterUpdate()"



    End Select

    Resume ExitHere

    Resume



End Sub



I like this because the option buttons let you select multiple row sources 

for cboSelect, and then select the parameter you want from cboSelect. Just 

another way to go.

Hi



If 'Device LLI' is your field name, try enclosing it in square brackets 

(so it looks like this: [Device LLI]). As you have a space in the field 

name, Access will treat them as two seperate entities when trying to 

resolve your criteria.



Best practice is to never use spaces when naming objects in your 

database. UseCapitalizationInsteadToIndicateWhereWordsEnd and 

USE_UNDERSCORES_WHEN_NAMING_CONSTANTS!!!



-BDS





Original Code:



Option Compare Database

Option Explicit



Private mFlg As Boolean

Private Sub Form_Load()

    mFlg =3D3D False

End Sub





Private Sub Search_Click()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim criteria As String

   =3D20

    Set db =3D3D CurrentDb()

    Set rs =3D3D Me.Recordset.Clone

    rs.Bookmark =3D3D Me.Bookmark



      criteria =3D3D "Device LLI Like ""*" & Me!txt & "*"""

      If mFlg =3D3D False Then

      rs.FindFirst criteria      <-------- Stop here, Error:3077

      mFlg =3D3D True

    Else

      rs.FindNext criteria

    End If



    If rs.NoMatch Then

      MsgBox ("No have matched recordes !")

    Else

      Me.Bookmark =3D3D rs.Bookmark

    End If

   =3D20

    rs.Close: Set rs =3D3D Nothing

End Sub





Private Sub txt_AfterUpdate()

    mFlg =3D3D False

End Sub