Searching for records Invalid Bookmark error 1

[comboy]

Hi all,

I'm trying to teach myself VB and am using unbound forms as advised, my question is how can I use an InputBox to allow users search for records using words and phrases and return the first record that matches the text entered into the InputBox if they are held in an Access DB.

I've tried the following code and am getting an invalid bookmark error in VB6

Private Sub cmdSearch_Click()
    Dim SearchVar As String
    SearchVar = InputBox("Enter a Word or Phrase to search for")
            
    Sales.MoveFirst
    
    Sales.Find "HowTo LIKE '*[SearchVar}*'", , adSearchForward, [SearchVar]
    
    
LoadData
        
End Sub

Private Sub Form_Load()
Set conn = New Connection
Set Sales = New Recordset

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Program Files\QicLEARNQicPOS\Data1\QicLEARNQPOS.mdb"
Sales.Open "SELECT * FROM tblHowTo WHERE [Category]='Sales Entry'", conn, adOpenStatic, adLockReadOnly, adCmdText


LoadData

Me.Left = 0
Me.Top = 0


End Sub

Thanks in advance for the help,


Graham.

 

[macleod1021]

I personally prefer to use ADO, but one thing that jumped out at me was the brackets in your select

>> "HowTo LIKE '*[SearchVar}*'",

You have an opening [ and a closing }. That will always throw an error (assuming you copy & pasted). Change that and see if you get the same error.

 

[comboy]

Hi macleod,

I'm sure I have tried both {} and [] but have got the same error with both. Thanks for spotting the typo and I'll try it again tonight.


Cheers,


Graham.

 

[Golom]

Since you're using ADO, note first that both ADO and DAO have a recordset object. If you have a reference to both then you should be explicit and specify

Set Sales = New [COLOR=red]ADODB.[/color]Recordset

The second thing to note is that the "zero or more" mask character for ADO is "%" ... (not "*" ... that's DAO.) In the FIND you may need

Sales.Find "HowTo LIKE '%" & SearchVar & "%'", , adSearchForward

The find method syntax is
Find (criteria, SkipRows, searchDirection, start)
By supplying [SearchVar] as the last parameter you were generating the "invalid bookmark" method because "Start" is an optional recordset bookmark.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[comboy]

Hi Golom,

Thanks for your post and for clearing this up for me.



Graham.