Filter form on open

[Xarkath]

Hello,



I have a table that associates our clients with premium detail by date range and product.
I then have a form with various options to choose from such as entering new rates/products, adding a new client, producing a bill and a few other options. On this form are fields (tied to another table) for the user to enter the basic criteria such as client number, start date and end date. Once entered the user then uses the button appropriate for the task. All but one option works perfectly. that one option is a form used for making corrections to premiums. The user would enter the client number and select the effective date then click the button for rate corrections and the form should display the table in datasheet view for just those records that match the client number and effective dates with no other required entry. But I only seem to get 1 of 2 results, the list includes the entire table or I get dialog boxes asking for the infomation that was already entered.

I tried a filter query and run on its own it works perfect but used with the form I still get the dialog boxes.

Can anyone tell me what I am doing wrong? Why can't I get the filter to pull from existing fields and by pass the dialog boxes? The basic filter criteria could be read as: where [Rates]![Client] = [Nav]![Client] and [Rates]![Eff Date] = [Nav]![Eff Date]

Thanks

[Dal Jeanis]

I've heard rumours of the nav forms having a limitation that only one is loaded at any given time, so you couldn't refer to information on the other form because it would have been unloaded. If this might be the case with your problem, you could store the values you need as temp variables, during the afterupdate of the controls that are holding the values. Then, the values will be available whenever and wherever you need them.

Where ( ([Rates]![Client] = [TempVars]![tvClient]) OR IsNull([TempVars]![tvClient]))
and ([Rates]![Eff Date] = [TempVars]![tvEffDate]) OR IsNull([TempVars]![tvEffDate])))

[Xarkath]

Hi Dal,

I will give that a try but I am not sure that is really the problem. "Nav" as I used it above is an actual table of which the main form is built around. All of the buttoned options on the main form start by saving the new data to the Nav table then opens the query, report or form accordingly with all criteria being pointed to the Nav table. Could my problem be that the filter is based on a table to table comparison? I wouldn't think so but I am fairly new to Access.

Thanks

[Dal Jeanis]

Nope, that can't be it, then. If your [Nav] table has only one record in it, then it should work. So, check to see whether/how many records are in [Nav] when your queries are failing.

[Xarkath]

There is only 1. I have it everything set up to see ID 1 on the [Nav] table so that when there is a change to a field it truely changes; it never adds.

Thanks

[Dal Jeanis]

Okay, let's back up then.
What, exactly, is the SQL in the query?
What, exactly, is the recordsource of that form that's not working?
What, exactly, does the dialog box say?

[Xarkath]

I just got this partially working. Using the setfilter command I entered this into the where clause: "[Rates]![client]=" & "[Nav]![client]" And "[Rates]![Effective]=" & "[Nav]![Effective Date]" I also tried splitting into two separate setfilter commands versrs using "AND" but got the same results. The main difference from what I was doing before is that I was not using the quotes or the & and I was applying the filter through the button command within the openform step verses setting it in the form under the onopen event. The result is that it now filters by client (without a dialog box) but it ignores the effective date portion altogether.

Anyone have any suggestions about getting it to process the second half of the formula?

Thanks

[Dal Jeanis]

1) Check whether your effective date fields are date or date/time. (if one is string, that's your problem)
2) Use this and see what happens:

"[Rates]![client]= [Nav]![Client] And [Rates]![Effective]= [Nav]![Effective Date]"

[Xarkath]

Thanks Dal,

I got this figured out. I just remembered that date fields always include the time even if it is not displayed. I remember that while checking the field setting as you had suggested above. The formula is: "[Rates]![client]= [Nav]![Client]" And DateValue([Rates]![Effective])= DateValue([Nav]![Effective Date]). By using "DateValue" I eliminated the time portion of the data.

Notice that the date portion of the formula does not have quotes. When I applied quotes I got prompted with a dialog box to enter the date. When I left the quotes off the client portion I got a dialog box prompting for the client. By using it the way it ihere it just filtered; no prompts, just the way I wanted to.

Thanks