DAP - FILTERING WITH DROP-DOWN-LISTS 1

[Techniproshop]

Techniproshop wrote 5 May 07 1:56
Hi fneily. Me again...!

This is almost what I'm looking for but in my DAP I have a drop-down-list that I want to use to apply a filter. I want the page to show only the records that match that drop-down-list "on change"

Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone
and?........

Filter to be applied to [costs] from the drop-down-list "SCOSTS"

Thanks!

fneily (Instructor) 5 May 07 13:13
Techniproshop - didn't we just do this??
And please start new threads.
<SCRIPT language=vbscript event=onchange for=SCOSTS>
<!--
Dim rs
Set rs=MSODSC.DefaultRecordset
rs.Find "[costs] = '" & document.all.item("SCOSTS").value & "'", 0, 1, 1
-->
</SCRIPT>

The rs.Find statement is on one line.
------------------My new thread---------------------------
Yes we did that, well, not exactly the samething! The other search list was for filtering on a unique number (SPROJECT). Now, if I use the same code for my other combolist, I get only the first record that match the combobox but what I want is all the records that match that combobox. That's why I thought I had to use the "clone" search command. When I create my DAP, I get automatically a Navigation bar in the navigation section which has the options for filtering a control. That's not why I need. I prefer the user make a choice from a combo list and whatever he choose, the system will filter the table and show only the records that match that choice. I want the same effect has the navigation bar but with a combobox.
Then the user will see how many records match his criteria.

Thanks.

 

[fneily]

Sorry, misread it, I guess. This should cover you.

<SCRIPT language=vbscript event=onchange for=SCOSTS>
<!--
Dim myFilter
myFilter = "[cost] = '" & document.all.item("SCOSTS").value & "'"
MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter
-->
</SCRIPT>

 

[Techniproshop]

It seems that I can never have it easy!

I use the code but it didn't work. Then I thought it's because the field "COST" is numeric and the combo "SCOST" is also returning a numeric value so I changed it to:

Dim myFilter
myFilter ="[Cost] = " & document.all.item("SCOSTS").value
MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter

But it's doesn't work either. I get no script error when I open my DAP so it's not a typo this time.

Any clue?

 

[Techniproshop]

To add on my last, in an access form, I used that code for filtering a field (numeric value)

Dim stDocName As String
Dim STLINKCRITERIA As String

stDocName = "FrmEnterData"

STLINKCRITERIA = "NUM=" & Me![CblVC]

DoCmd.OpenForm stDocName, , , STLINKCRITERIA

I get all records that match the criteria. All I need to do is the same but in my DAP. Is Access 2003 or 2007 more user friendly with DAP's^

 

[fneily]

Ok. First, post the EXACT code you used. (COPY AND PASTE) so I can see it.
Second, and I REPEAT, DAP's ARE NOT forms so forget about what you know about forms. So your form example means nothing.

your line should look like:
myFilter = "[cost] = '" & document.all.item("SCOSTS").value & "'"

all on one line.

The last thing. You keep asking about versions of Access when talking about DAP's. DAP's ARE NOT Access. They are html(web) pages.

 

[Techniproshop]

Here is the code:

<SCRIPT language=vbscript event=onchange for=SCOSTS>
<!--
Dim myFilter
myFilter = "[COSTS] = '" & document.all.item("SCOSTS").value & "'"
MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter
-->
</SCRIPT>

I now know that Access forms are not DAP since you told me the first time and understand that the VBA code is not the same as the code used in DAP. However, I wanted to show you an example of what I was doing in forms so it might gives you a better idea of what I need. My only question about that is why DAPs have nothing to do with Access since they are created from Access? Weird. It's like creating a table in excel but it's no longer an excel sheet after you save it! Just a little confusing! It's very interesting thaugh.

So what's wrong with my code?

 

[fneily]

I must apologize. I started to rant about the difference between forms and DAP's and overlooked the numeric thing. The code should be:
<SCRIPT language=vbscript event=onchange for=SCOSTS>
<!--
Dim myFilter
myFilter = "[costs] = " & document.all.item("SCOSTS").value
MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter
-->
</SCRIPT>

It looks like on your second try, the one for numeric, you forgot an "s" on [cost]. It should be [costs].

 

[Techniproshop]

If you read my "13 May 07 0:03 " post, that's what I tried and got no luck. So either the text or numeric way are not filtering the DAP. I'm gonna start a new table and DAP from scratch and see if the problem has something to do with the table liaisons.

 

[fneily]

Your "13 May 07 0:03 " post had a typo that I mentioned in my last post.

 

[fneily]

You may also considering doing a group filter control:
How to Add a Group Filter Control to a Data Access Page

http://support.microsoft.com/?kbid=201011

 

[Techniproshop]

Is the typo you talk about was for "cost" field name? The correct name is COSTS

I tried the code with another list I want to use for filtering. The returned value is also numeric:

Dim myFilter
myFilter = "[CATEGORY] = " & document.all.item("SCAT").value
MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter


It's the exact same as yours exept that I changed the control's name:

Dim myFilter
myFilter = "[costs] = " & document.all.item("SCOSTS").value
MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter

?????? So confusing!

 

[Techniproshop]

I found it!!!!!!!!!!! Here is my mistake:

In the Project table, only primary keys of related tables are saved. But on the DAP I wanted to see the text result so I created a query and linked all the tables together: TblProjects, TblCategory, TblCosts, TblVillages.

What I did is creating a new DAP from the TblProjects only. Then I added a list for category and putted the code in. Finally it works!!!! Whooooooooo! So happy man! Can't believe it was that stupid. Well, that's how I learned Access so I'm very used to!

Thanks for all of your time. Very appreciated!

 

[fneily]

Can't believe it was that stupid." No. Not stupid at all. In fact, that's how I and most of us learn. We try something, it doesn't work, spend 3 days trying things to fix it. and it's usually something that, when fixed, seems simple. And it's not like there's alot of books around about DAP's.
But I know how you feel when it finally works. Plus, you now can understand the frustrations of other new users of DAP's.
Also, I didn't even think to ask if you created your combo from a query. Now the next time I'll know.

 

[Techniproshop]

Thanks again.