Sorting data in a form

[PMC1]

Hi,

I have a form (frmInvoices) which is based on a table (tblInvoice).
There is a column in tblInvoice that contains a reference number which
is formatted as mmyy-nn. So for example the fist invoice in august
would have a reference number of 0806-01, the second would be 0806-02
and so on.

This is the field that is used in "frmInvoices" to search for and
select Invoices from a combobox. My problem is as you can see is this
reference number in not great for sorting as it is formatted as a text
field.

I created a query (qrySortRefNo) that breaks the reference number into
its componant parts (i.e. Month, Year and Incrementing number) and then
sorts based on the componants. I then use this query to populate the
combobox and this works fine.

Where I'm having trouble is I have some buttons on the form used to
move to the next or previous record. This doesn't work as the reference
number is not sorted in the table so I end up moving from say 0806-30
to 0806-03 then 0806-29 etc.

Is there some way I can link the form to the sorted query
"qrySortRefNo" so as to be able to move through the invoices correctly,
while still being linked to tblInvoices. Or could somebody suggest a
better way to sort the records on the form by this reference number.

I'm using Access XP. Any help appreciated

Thanks

Paul

 

[Jeff L]

If I am reading this correctly, all you should have to do is change
your Record Source of the form to the query instead of the table.

Hope that helps!

 

[PMC1]

Hi Jeff,

Thanks for the reply.

The problem here is this is a data entry form also so I need to keep
tblInvoices as the Recordsource in order to update or add records

Paul

 

[PMC1]

Hi Jeff,

Thanks for the reply.

The problem here is this is a data entry form also so I need to keep
tblInvoices as the Recordsource in order to update or add records

Paul

 

[Access101]

I still like Jeff's idea ...
Go to properties of the form in design view
ON the Data Tab, under Recource Source, click once
On the right now, there should be three dots ...
Click on that and answer the question YES
"You Invoked the Query Builder on a Table" YES NO
Now drag all the fields from tblInvoices onto the query grid and sort by the
concatinated field.