Runtime error "3021" with search button

G

gt11990

Registered User.
Local time
Today, 04:42
Joined
Mar 20, 2013
Messages
18
hi there i have a search button on the form which does work does the search very well how ever if a wrong search value is entered which does not exist in the database i get the Runtime error "3021"

here is my code

Code: 
Private Sub cmdSearch_Click()
Dim bkmk As Variant
Dim strField As String
Dim strWhere As String
Me.RecordsetClone.MoveFirst
strWhere = "Code Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Names Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Surename Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Line Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Broadband Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Activation Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Activation2 Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
"OR Orderdate Like" & Chr(34) & Me.txtsearch & "*" & Chr(34)


Me.Filter = strWhere
Me.FilterOn = True
If Me.RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
bkmk = Me.RecordsetClone.Bookmark
Me.Recordset.Bookmark = bkmk
End If
End Sub
 
Hello gt11990, you do not have any space after the Like operator nor before the OR operator.. Your generated SQL would look like..
Code: 
Code [B][COLOR=Red]Like"*[/COLOR][/B]something[B][COLOR=Red]*"OR[/COLOR][/B] Names Like"*something*"
This is a very good example of why you need to use a Debugging technique, you might be able to see the problem almost immediately..
 
oh my am so confused right now is it possible for you to show it just a section of my code so i can understand more clearly

thank you
 
Copy the following code and paste over the code you currently have,
Code: 
Private Sub cmdSearch_Click()
    Dim bkmk As Variant
    Dim strField As String
    Dim strWhere As String
    Me.RecordsetClone.MoveFirst
    strWhere = "Code Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Names Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Surename Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Line Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Broadband Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Activation Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Activation2 Like" & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               "OR Orderdate Like" & Chr(34) & Me.txtsearch & "*" & Chr(34)
    
    Debug.Print strWhere

[COLOR=Green]'    Me.Filter = strWhere
'    Me.FilterOn = True
'    If Me.RecordsetClone.NoMatch Then
'        MsgBox "No Match"
'    Else
'        bkmk = Me.RecordsetClone.Bookmark
'        Me.Recordset.Bookmark = bkmk
'    End If[/COLOR]
End Sub
Save and then go to the form, click the button.. Nothing will happen but open the VBA Editor and hit CTRL+G it will open the immediate window, see what is generated.. You will see my point..
 
i did what you said but nothing comes up on the new window when i press the button
 
nothing when i search the right information i can do a search no problem if i leave it blank and search the run tiem error comes up the same one
 
Are you sure you did not get something like..


attachment.php


And that you did not see anything strange?
 

Attachments

  • ImmediateWindow.png
    ImmediateWindow.png
    37.1 KB · Views: 574
ok after closing and reopening the database i get that same message the one in your screen shot but what does it mean i mean how could i solve this problem in the screen shot

when i search a record which does not exist i get this error
 

Attachments

  • accesss.jpg
    accesss.jpg
    98.7 KB · Views: 180
Try this code..
Code: 
Private Sub cmdSearch_Click()
    Dim bkmk As Variant
    Dim strField As String
    Dim strWhere As String
    Me.RecordsetClone.MoveFirst
    strWhere = "Code Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Names Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Surename Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Line Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Broadband Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Activation Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Activation2 Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
               " OR Orderdate Like " & Chr(34) & Me.txtsearch & "*" & Chr(34)
    Me.Filter = strWhere
    Me.FilterOn = True
    If Me.RecordsetClone.NoMatch Then
        MsgBox "No Match"
    Else
        bkmk = Me.RecordsetClone.Bookmark
        Me.Recordset.Bookmark = bkmk
    End If
End Sub
 
thank you for the code it still searches normally for existing records but none existing still same error please see on attached screen shot

attachment.php
 

Attachments

  • access 1.jpg
    access 1.jpg
    92.9 KB · Views: 591
Okay so sorry did not see that at all.. All I was looking at was the String concatenation.. Well This should work..

I have assigned the RecordsetClone to a Recordset Object, and tested if there is some records in the recordset clone, before using the Move methods.. If there are no records, then simple show a message "No Records"..
Code: 
Private Sub cmdSearch_Click()
    Dim bkmk As Variant, rstObj As DAO.Recordset
    Dim strField As String
    Dim strWhere As String
    Set rstObj = Me.RecordsetClone
    
    If Not (rstObj.BOF And rstObj.EOF) Then 
        rstObj.MoveFirst
        strWhere = "Code Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Names Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Surename Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Line Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Broadband Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Activation Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Activation2 Like " & Chr(34) & Me.txtsearch & "*" & Chr(34) & _
                   " OR Orderdate Like " & Chr(34) & Me.txtsearch & "*" & Chr(34)
        Me.Filter = strWhere
        Me.FilterOn = True
        If rstObj.NoMatch Then
            MsgBox "No Match"
        Else
            bkmk = rstObj.Bookmark
            Me.Recordset.Bookmark = bkmk
        End If
    Else
        Set rstObj = Nothing
        Call MsgBox("No Records available")
    End If
End Sub
 
oh lord this is driving me crazy now i get the following error soo weird

attachment.php
 

Attachments

  • accesss.jpg
    accesss.jpg
    101.3 KB · Views: 587
Okay this has gone over and beyond.. What is that you are trying to do? In simple English..
 
ok i have the following fields in the screen shot,

its linked to customers table with customer details

so in the form i want to just search anything name, id address and once the search is done

the results are displayed in the form fields.

so far so gd the search works

but when you enter a value which does not exist in the database i get the run time error

attachment.php
 

Attachments

  • accesssss.jpg
    accesssss.jpg
    89 KB · Views: 618
So the Form is Unbound..? Is it possible for you to upload a Dummy version of your DB to play with?
 
Okay found the problem.. Try this code..
Code: 
Private Sub cmdSearch_Click()
    Dim bkmk As Variant, rstObj As DAO.Recordset
    Dim strField As String
    Dim strWhere As String
    Set rstObj = Me.RecordsetClone
    
    If Not (rstObj.BOF And rstObj.EOF) Then
        rstObj.MoveFirst
        strWhere = "Code Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Names Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Surename Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Line Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Broadband Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Activation Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Activation2 Like " & Chr(34) & Me.txtSearch & "*" & Chr(34) & _
                   " OR Orderdate Like " & Chr(34) & Me.txtSearch & "*" & Chr(34)
        rstObj.FindFirst strWhere
        If rstObj.NoMatch Then
            MsgBox "No Match"
        Else
            bkmk = rstObj.Bookmark
            Me.Recordset.Bookmark = bkmk
        End If
    Else
        Set rstObj = Nothing
        Call MsgBox("No Records available")
    End If
End Sub
 
You must log in or register to reply here.

Similar threads

M
Solved Syntex Error
Replies
12
Views
1,156
Josef P.
J
D
Dynamic Report based on Current Form Filter
Replies
9
Views
1,214
arnelgp
arnelgp
M
Search Button
2
Replies
33
Views
2,813
Isaac
Isaac
M
Pop-up Search Tool will not pull in data
2
Replies
31
Views
2,471
pbaldy
pbaldy
U
Solved Visible and invisible Button in the form.
Replies
11
Views
1,309
Minty
Minty

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom