Sequence Problem

[Gazza]

I have a search form (form1) that is used to select a customer from a list
which is generated from a query and has a sequence column but I can’t seem to
get the sequence to re-sequence itself when it is displayed.

Basically on the form the user types part of the accountcode presses the
enter key and a listbox displays the list of customers that match. The
problem is that when the accountcode is entered it might be no.1, no.3 and
no.6 and that is how it is displayed but I want it to display no.1, no.2 and
no.3.

I have a sql line in the sequence field of the query as follows:-

SELECT (Select Count(1) FROM tblcustomers A
WHERE A.AccountCode <=tblcustomers.accountcode) AS Sequence,
TblCustomers.Accountcode, TblCustomers.Address1, TblCustomers.CustomerName
FROM TblCustomers
ORDER BY TblCustomers.Accountcode;

I can’t seem to figure out how to get the sequence column to re-sequence
itself when the account code is entered.

Any help would be much appreciated

Thanks
Gareth

 

[John Spencer (MVP)]

I think you are going to have to restrict the Account codes in the sub query
to the account codes you are searching for

Something like
(SELECT Count(*) FROM tblCustomers A WHERE A.AccountCode In (1,3,6) and
A.AccountCode<=tblCustomers.AccountCode) as Sequence

In other words if you need to apply the same criteria to the subquery as you
do the main query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Gazza]

Thanks for the reply John.

Didnt realise i had a sub query(still new to this a learning very slowly).

When i enter the accountcode into the textbox and press the enter key it
displays the listbox with the correct accountcodes in there but its just the
sequence is out e.g line one has number 4 line two has number 6 in the
sequence column, if i then input the line number e.g 1 for first line 2 for
second line into the other text box it opens the other form with the correct
accountcode.
Basically i just need the sequence part of the query to only calculate
itself once ive entered the accountcode into the first textbox.

Thanks for the help
Gareth

 

[John Spencer]

I don't think I can help. I am totally confused about you are
attempting to do.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================