Searching for records in a DAP

[Eric]

I have the following code attached to a command button on my DAP that I use
to return records in a recordset. The problem is, if the user doesn't type in
the "CompanyName" value exactly as it is in the database, I get an error
saying the company doesn't exist. Is there any way to alter the code in order
to accomplish the same thing as "Like & etc..." in a Parameter query? Any
help would be great.

<SCRIPT language=vbscript event=onclick for=Command0>
<!--
' Clone the recordset
Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone

On error resume next
' This is the command line for the search string.
rs.find "CompanyName= '" & Cstr(InputBox("Please enter the company_
information you wish 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 record of company in database",,"Done_ Searching"
Exit Sub
End if

MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
-->
</SCRIPT>

 

[Sylvain Lafontaine]

Yes, a quick solution would be to create a combobox displaying all the
Company Name and use it to filter your form:

First, create a combobox (or dropdownlist) and set its ListRowSource to a
query (recordset) returning the list of the names; then set the
ListBoundField to the IDs (primary key) for this list and the
ListDisplayField to the field for the complete names of your companies.

Then, in the GroupLevelProperty, set the GroupFilterControl to this
dropdownlist and the GroupFilterField to the same field as the ID above..

The primary key field for the recordset of the page should also be the same
field.

Now, the combobox will server as a filter for the company name.

There is also a newsgroup about DAP: m.p.access.dataaccess.pages . Old
messages can be searched or accessed via Google:
http://groups.google.ca/group/microsoft.public.access.dataaccess.pages

 

[Eric]

Works like a charm! Thanks for your help! Now if I can only get someone to
help me figure out the scripting for a "FilterByForm" command button in DAP.
That would be perfect. Thanks again!

 

[Sylvain Lafontaine]

A « "FilterByForm" command button in DAP »; what do you mean exactly with
that?

There is a FilterBySelection button in DAP but I don't remember any
FilterByForm button and I don't remember if the FilterBySelection button can
be directly scripted.

 

[Eric]

When you create a regular form in Access, you can attach a very simple code
to a command button that actually takes the form you have created and bases a
parameter query on every field in the form. It then allows the user to select
what they are searching for from the corresponding drop downs on the form.
Then you just click the ApplyFilter button at the top of the screen and
records containing what they have selected will become available in the form.

Incidentally, the vbcode for "FilterbyForm" is:

Private Sub btnButtonName_Click()
DoCmd.RunCommand acCmdFilterByForm
End Sub

That's it. Simple and powerful. Try it out and you'll see how it works. I
would like to have something like this for a data access page. Anyways,
thanks again for your help!

 

[Sylvain Lafontaine]

The only practical way to do this with DAP would be to collect the values of
each drop down in a cookie and then use these values to open a parameterized
query in DAP.

(You cannot do that directly in a javascrit without refreshing the page
because each time you will change a parameter value after the page is open,
the MSODSC control will make a full requery for the whole thing. So, if you
have say 10 parameters, this means 10 requeries. Add this to the fact that
many of these drop downs can be themselves based on parameterized queries
and you will see where this is going.)

But if you have saved these values in a cookie, then you can set these
parameters before the recordset is opened.

See for an example:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/ODC_acc10_DSCOM.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/programdap.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/ODC_acc10_DSCOM.asp

 

[Steve H]

Hello Eric,

You seem to have a form that does exactly what I want to do - I am just
having a very hard time creating it. Maybe you can help.

I have a database that stores Building name, project info and other details.

I want the user to be able to open a blank form, using pull downs choose the
criteria (ie. Building Name and Project #) they want to search for then click
a button and the database will filter to that criteria.

Any help is much appreciated.

 

[ghislain bourassa]

Hi. I've been trying to do that for days now. The first drop-down-list is
to find a unique project number. Very easy in the script:

<SCRIPT language=vbscript event=onchange for=SPROJECT>
<!--
Dim rs
Set rs = MSODSC.DefaultRecordset
rs.Find "[PROJECT_NO] = '" & document.all.item("SPROJECT").value &"'", 0, 1,
1
-->
</SCRIPT>

Then I have a "Category" drop-down-list. I want to filter the DAP with this
list. I did exactly what you said and it works perfectly! Thing is, I have
more than one drop-down-list like that. In the group-filter-control I can
set only one control. I have another drop-down-list named "Villages" that I
want to use for filtering my data.
How can I set more than one comboxbox to filter my DAP?
Thanks

 

[ghislain bourassa]

Ok, I tried something but all I get is the first item that match the
combobox but what I need is to filter the records and get all the records
that match my combobox named "SCAT". The info that are searched is from
field "CATEGORY". Here is the code I tried:

<SCRIPT language=vbscript event=onchange for=SCAT>
<!--
' Clone the recordset
Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone

On error resume next
' This is the command line for the search string.

rs.Find "[CATEGORY] = " & document.all.item("SCAT").value

' Check search results for success.
If (rs.bof) or (rs.eof) Then
Msgbox "No record found for this category in the database",,"Done_
Searching"
Exit Sub
End if

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

-->
</SCRIPT>

That's the first part of my search. The other thing I need to do is using a
drop-down-list with choices of reports. Whatever the user choose in that
list, the correct hyperlink will open the correct report. Right now I have
a page with all the reports name and hyper link for each report. It works
but when you have thousand of choice of reports, you don't want a page like
that!

Thanks anybody for your future help.

ghislain

 

[ghislain bourassa]

Ok, I tried something but all I get is the first item that match the
combobox but what I need is to filter the records and get all the records
that match my combobox named "SCAT". It returns a numeric value. The info
that are searched is from field "CATEGORY". The primary key for that field
is also numeric. Here is the code I tried:

<SCRIPT language=vbscript event=onchange for=SCAT>
<!--
' Clone the recordset
Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone

On error resume next
' This is the command line for the search string.

rs.Find "[CATEGORY] = " & document.all.item("SCAT").value

' Check search results for success.
If (rs.bof) or (rs.eof) Then
Msgbox "No record found for this category in the database",,"Done_
Searching"
Exit Sub
End if

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

-->
</SCRIPT>

That's the first part of my search. The other thing I need to do is using a
drop-down-list with choices of reports. Whatever the user choose in that
list, the correct hyperlink will open the correct report. Right now I have
a page with all the reports name and hyper link for each report. It works
but when you have thousand of choice of reports, you don't want a page like
that!

Thanks anybody for your future help.

ghislain