A strange error...

[dreamspy]

Hi

My Problem in short:
I have a form which runs in it's "On Open" event the VB code:
Me.OrderBy = "Category & Date"
And the record source query has an input parameter. The error I get is that the user is asked two times for the input parameter. This error goes away if I remove the VB code.


Now My long version

My task:
I have a form which shows some fields from a query. And everytime the form is loaded I want the form to be sorted by two fields (the fields are category and date).

The Problem:
Now the query is sorted accordingly and the form also at first, but every time a user changes the sorting, the new sorting gets saved in the "Order By" field, so the next time someone else opens up the form, it will have the sorting from the previous user.

My solution:
I made an "On Open" event with the following code:
Me.OrderBy = "Category & Date"

Now the Real Problem:
If I do this it usualy works fine. The sorting is like I want it to be every time I open the form.
But now I have a form based on a query, that takes in parameter from the user. The user can here select what dates to look at. The Criteria in the query looks like this:
>=[From date]
And the problem is that the user is asked 2 times when he opens up the form, for the "from date".
Now if I remove the little VB code (Me.OrderBy = "Category & Date"), this error dissapears, and the user is only asked once, like he's supposed to.

So somehow this error is caused by that little code... anybody have any clue why?

 

[vbJupiter]

Hi

My Problem in short:
I have a form which runs in it's "On Open" event the VB code:
Me.OrderBy = "Category & Date"
And the record source query has an input parameter. The error I get is that the user is asked two times for the input parameter. This error goes away if I remove the VB code.


Now My long version

My task:
I have a form which shows some fields from a query. And everytime the form is loaded I want the form to be sorted by two fields (the fields are category and date).

The Problem:
Now the query is sorted accordingly and the form also at first, but every time a user changes the sorting, the new sorting gets saved in the "Order By" field, so the next time someone else opens up the form, it will have the sorting from the previous user.

My solution:
I made an "On Open" event with the following code:
Me.OrderBy = "Category & Date"

Now the Real Problem:
If I do this it usualy works fine. The sorting is like I want it to be every time I open the form.
But now I have a form based on a query, that takes in parameter from the user. The user can here select what dates to look at. The Criteria in the query looks like this:
>=[From date]
And the problem is that the user is asked 2 times when he opens up the form, for the "from date".
Now if I remove the little VB code (Me.OrderBy = "Category & Date"), this error dissapears, and the user is only asked once, like he's supposed to.

So somehow this error is caused by that little code... anybody have any clue why?

I would think that it has something to do with the date because both the parameter query and the orderby form property both involve the date. As to why user is asked twice...I think what's happening is the Orderby property of the form load event causes the parameter to be reevaluated.

So, when the form opens and it addresses the parameter query, (the first appearance)after the user enters it in, the OrderBy property is executed, which resorts it. But then Access reasks for the parameters (the second time).

I don't know why exactly this happens, nor do I know for sure how to correct it. I would think that maybe the form shouldn't be based on a parameter query. Perhaps you could have a button that you click for the user to be prompted for the parameter query?

HTH!
AA

 

[vbJupiter]

I would think that it has something to do with the date because both the parameter query and the orderby form property both involve the date. As to why user is asked twice...I think what's happening is the Orderby property of the form load event causes the parameter to be reevaluated.

So, when the form opens and it addresses the parameter query, (the first appearance)after the user enters it in, the OrderBy property is executed, which resorts it. But then Access reasks for the parameters (the second time).

I don't know why exactly this happens, nor do I know for sure how to correct it. I would suggest either reading more about forms based on parameter queries and the Orderby property to find out exactly what's going on. I would think that maybe the form shouldn't be based on a parameter query. Perhaps you could have a button that you click for the user to be prompted for the parameter query?

HTH!
AA

 

[dreamspy]

yeah adding the criteria by using a button might do the trick. I'll try that.

 

[gemma-the-husky]

i've seen that before, that the first record in the query actually gets processed twice, and I'm not sure why. Rather than having the parameter in the query, the better way to do this is to have a text box on your form for the user to enter the date, and the query would then have forms!"myformname!"![mydatebox] as the criteria. (the syntax may be slightly off - some of the separtors may need to be dots, not bangs) - instead of prompting for the date in an inputbox, the query just reads the date set in the form - you can also then validate the date (if necessary) before showing the user the results, or (if there are no results) giving a helpful message.