Search for particular record on continuous form

[AccessWimp]

I have a continuous form based on a query. In the form footer I have placed a text box and a command button. One of the text boxes on the continuous form is bound to the VIN field in the query. I would like the user to be able to place the last 8 digits of a particular VIN in the text box in the footer and click the command button to bring that particular record to the top. I am on a deadline and if anyone can help with this I would really appreciate it.

Thank you

 

[WayneRyan]

AccessWimp,

Base the Continuous form on a query.

In the query grid, the VIN field has for Criteria:

Like "*" & Forms![YourContinuousForm]![ThatFooterField]

Give ThatFooterField some valid DefaultValue.

Don't need a Command Button, just use the AfterUpdate event
for [ThatFooterField] to do: Me.Requery. If you must use
a Command Button, it should only do Me.Requery.

Wayne

 

[AccessWimp]

Wayne,

Thank so much for your quick response. The continuous form is based on a query of unpaid invoices. Some times there are just a few, and sometimes there are a lot. I would like the user to be able to see all the unpaid invoices, but have the option of clicking the command button to bring the specific record to the top to enter the payment amount. I had thought about using your suggestion of plaing the form and control reference in the query, but that would not allow all the unpaid records to show.

 

[WayneRyan]

AW,

Good luck.

Your query defines a recordset for the form. If the sort order can't be
defined by the Query (or form), I don't know how to "promote" that one
record to the top of the list.

I guess that you could add a new "unbound" field to the query. Use an
IIf statement to populate it based on your textbox (either assign it a
0 or 1 based on whether or not it's "like" the textbox). Then sort on
that field. That should actually work.

Wayne

 

[AccessWimp]

Wayne,

I'm not sure what you mean here. Since I'm such a "wimp" with Access, could you elaborate?

Thanks!

 

[WayneRyan]

AW,

Uh oh.

Your Continuous form is already based on a query. OK.

In the Query, for the [ThatField] column, make the criteria:

Like "*" & Forms![YourContinuousForm]![ThatFooterField]

Where [ThatFooterField] is your field in question.

If you give it a valid value, on Form Startup, you'll display some records.

Otherwise, your one record (and its relatives) will "sift" to the top.

Post a sample if that doesn't work.
Wayne