Search routine for a data access page.

kcddoorman

This loop searches through all the fields in a table. I get a problem when executing it says "expected end of statement"; line 24 on here. Syntax problem? I don't see it.

Expand|Select|Wrap|Line Numbers

 
'code from MSDN for a simple search function.

'http://support.microsoft.com/kb/271728
 
dim i           'Counter variable

dim rs          'ADO recordset object

dim fld         'ADO field object

dim FieldCount  'Number of fields in the recordset
 
FieldCount = MSODSC.DefaultRecordset.Fields.Count
 
'This will return the default recordset on the page

'in this case, the Customers table.

set rs = MSODSC.DefaultRecordset
 
for i = 0 to FieldCount - 1

    'get a field object

    set fld = rs.Fields(i)
 
    '0 = Skip no records

    '1 = Search forward

    '1 = Start with the first record
 
'the next line is where the debugger stops on the error.

    rs.Find fld.name &amp; " = '" &amp; txtSearch.value &amp; "'", 0, 1, 1 
 
    'Check for EOF.  If  at EOF but have not exhausted

    'all the fields, then reset to the first position in the 

    'recordset.  Otherwise, if a match was found, exit the loop.

    if rs.EOF then 

        rs.MoveFirst

    else

        exit for

    end if

next
 
'Clean up.

set fld = nothing

set rs = nothing

kcddoorman

This is how I got it to work. There are quite a few questions about this floating around on here so I will try to post the link on there discussions.

FOR A DATA ACCESS PAGE! Straight from microsoft.
This will search a single Integer Field for an Integer given by the user and display the corresponding record set. No wildcards allowed in this example.

(A string search will also work with a bit of tweaking)

In design view
1. turn off the controls wizard
2. create a command button and name it cmdSearch
3. go to Tools --> Macro --> Microsoft Script Editor
4. from the Objects and Events box select cmdSearch
5. from the Events box(to the right) select OnClick
6. this will set your cursor in the correct place to insert the following code.

Expand|Select|Wrap|Line Numbers

 
' Clone the recordset.
 
Dim rs

Set rs = MSODSC.DataPages(0).Recordset.Clone

On error resume next
 
' This line assumes that the value you are filtering on is an integer.

' If the search value is a string, use slightly different syntax.

' For example, "Field you want to search = '" & CStr(InputBox("Please enter Whatever you want to find", "Find")) & "'" 

rs.find "[yOUR fIELD HERE]=" & cLng(inputbox("Enter Something to find","Find"))

' Custom error handling.

If (err.number <> 0) Then

    Msgbox "Error: " & err.number & " " & err.description,,"Invalid Search"

    Exit Sub

End If

' Check search results for success.

If (rs.bof) or (rs.eof) Then

    Msgbox "No wHATEVER found",,"Search Done"

    Exit Sub

End If

MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark

-->

Hope this helps.

James

kcddoorman

Alright so I got it to work searching for Integers only. I thought I would try out searching for strings so I take the:

Expand|Select|Wrap|Line Numbers

 rs.find "[WO NUMBER]=" & cLng(inputbox("Enter a Workorder Number","Find"))
 

and change it to:

Expand|Select|Wrap|Line Numbers

 rs.find "[CUSTOMER NAME]= " & CStr(InputBox("Please enter customer to find", "Find"))&""
 

and it gets an error about using the wrong data type. It will find an integer (a number) if I put one into the table. I tried making a customer named 12345 and used the CStr syntax above and it works fine. It will not, however, find a string.

In the example their is an example to search a string:

Expand|Select|Wrap|Line Numbers

 ' If the search value is a string, use slightly different syntax.

' For example, "CustomerID = "'" & CStr(InputBox("Please enter customer to find", "Find")) & "'"

this in itself will not work. the syntax is wrong.

kcddoorman

This here is teh goodness.

Expand|Select|Wrap|Line Numbers

 rs.find "[Customer NAME] = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
 

Now I'm trying to get wildcards to work. From what I've read you can change the = to LIKE.

Expand|Select|Wrap|Line Numbers

 rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "'"
 

This will not return a record though. I think this will become a "How To" search for record sets for noobs before I'm done with it.

Strozak

Quote:

Originally Posted by kcddoorman

This here is teh goodness.

Expand|Select|Wrap|Line Numbers

 rs.find "[Customer NAME] = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
 

Now I'm trying to get wildcards to work. From what I've read you can change the = to LIKE.

Expand|Select|Wrap|Line Numbers

 rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "'"
 

This will not return a record though. I think this will become a "How To" search for record sets for noobs before I'm done with it.

I have somewhat of a solution for this. Change your line to the below:

rs.find "[job name] LIKE %" & CStr(InputBox("Please enter a job name to find", "Find")) & "%"

However, It only brings you to the closest record that you typed and doesnt filter the records down which would be ideal. Any thoughts of how to set it to not only find but filter?

Search routine for a data access page.

re: Search routine for a data access page.

re: Search routine for a data access page.

re: Search routine for a data access page.

re: Search routine for a data access page.

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about