Searching Access table

[swappy]

I have two tables in my database, one customer and other cddetails.. n i`m trying to search both tables for letter entered by user.. The code for Customer table works fine...but the code for cddetails is giving me some problem... I would appreciate if some could help me out...

Here is the code

Code for searching CD Details Table

Private Sub cmdSearch_Click()
'write a code validity
On Error GoTo errlable
again:
 mpbar.Value = 0
 

If (cmb_mfield.Text = "Movie Category") Then
 fnd = "select Cdid,MCat,MName,MRating,Rate,Cast from CdDetails where MCat like '" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Movie Name") Then
 fnd = "select Cdid,MCat,MName,MRating,Rate,Cast from CdDetails where MName like '" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
Else
fnd = "select Cdid,MCat,MName,MRating,Rate,Cast from CDDetails where " & Trim(cmb_bfield) & " like '" & Trim(txt_bvalue) & "%'"
bpbar.Value = 30
End If
 If (mflag = False) Then
            MR.Open fnd, conn, adOpenStatic, adLockOptimistic
            mpbar.Value = 65
            mdatagrid.Visible = True
            Set mdatagrid.DataSource = MR
            mpbar.Value = 80
            mdatagrid.ReBind
            mflag = True
            Else
            mflag = False
            MR.Close
            GoTo again
              End If
mpbar.Value = 100
mpbar.Value = 0
Exit Sub
errlable:
mpbar.Value = 0
MsgBox Err.Number & "  " & Err.Description
End Sub

Code for searching Customer Table

Private Sub cmd_bsearch_Click()
On eror GoTo errlable:
'write code for validity
again:
bpbar.Value = 0
'If (cmb_bfield.Text = " ") Then
'MsgBox ("Idiot")
If (cmb_bfield.Text = "Customer Name") Then
fnd = "select CustId,CName,CAdd,CTel,CMob from Customer where CName like'" & Trim(txt_bvalue.Text) & "%'"
bpbar.Value = 30
ElseIf (cmb_bfield.Text = "Address") Then
fnd = "select CustId,CName,CAdd,CTel,CMob from Customer where CAdd like'" & Trim(txt_bvalue.Text) & "%'"
'bpbar.Value = 30
'ElseIf (cmb_bfield.Text = "Movie Name") Then
'fnd = "select Cdid,MCat,MName,MRating,Rate,Cast from CDDetails where MName like'" & Trim(txt_bvalue.Text) & "%'"
Else
fnd = "CustId,CName,CAdd,CTel,CMob from Customer where " & Trim(cmb_bfield) & " like '" & Trim(txt_bvalue) & "%'"
bpbar.Value = 30
End If
If (bflag = False) Then

           BR.Open fnd, conn, adOpenStatic, adLockOptimistic
            bpbar.Value = 50
            bdatagrid.Visible = True
            Set bdatagrid.DataSource = BR
           bpbar.Value = 70
           bdatagrid.ReBind
            bflag = True
            bpbar.Value = 85
           Else
           
          bflag = False
            BR.Close
           GoTo again
           
            bpbar.Value = 90
            End If
bpbar.Value = 100
bpbar.Value = 0

Exit Sub
errlable:
bpbar.Value = 0
MsgBox Err.Description
End Sub

Both Search boxes are inside Tabs...

[vb5prgrmr]

Where or what is the problem? Your code looks like it should work even if I have a few sugestions, but what I have in the way of suggestions may not help you, so please describe what kind of problem you are having or where your error is occuring.

Thanks

[swappy]

This is the error I get

Run-time error '-2147467259 (80004005)

Method 'Open' of object '_Recordset failed

[vb5prgrmr]

Okay, did you open the database, a connection to the database?

[swappy]

yes ...Inside the form load

[Eddy Jawed]

use ado instead for your table searches

[vb5prgrmr]

Okay, in your else (CD Details) your missing the .text from your text box declaration and this should not be a problem but just in case.

I also see that you are not placing a space between your like's and the single tick Like' and once again this should not be a problem but I thought I would mention it.

Then I see you are using the percent character % as your wild card character and from my Access documentation (2k) I do not see it in the list of usable wild cards. If you are using Access, check its help file to see what wild card are available. (Same if you are using any other DBMS.

Good Luck

[swappy]

I did the changes...and for the percent character % it works in case of Searching the customer table so I dont think that should be the problem... I has something to do with the recordset.

[vb5prgrmr]

Well, I don't know what to tell you then. Last guess is, is it declared properly?

[swappy]

In the general declaration...yes...