Sequence problem

[Gazza2]

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 in the query display 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

 

[Thomko]

How do you apply the entered account code, via a filter? If so, I would assume that the SQL is not evaluated again, but just, well, filtered.
Try to throw in a ListBox.Requery command in the code that does the filtering.

If my assumption is wrong, please explain further how you do the filtering.

Thomas

 

[Gazza2]

thanks thomko
i tried the listbox.requery but it didnt seem to help. Here is the complete code for the textbox afterupdate event.

Me.LstData.Visible = True

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source
Dim sText As String 'Contents of the criteria control

sText = Trim(Me.Customer.Text)

'Is there any text to test?
If Not sText = "" And IsDelOrBack = False Then

StrSql = "SELECT sequence, accountcode, customername FROM qrycustomerselect " & _
"WHERE accountcode Like '*" & sText & "*' ORDER BY Sequence;"

'Refresh the rowsource with the new SQL
Me.LstData.RowSource = StrSql

End If
'Requery the list box to show results
Me.LstData.Requery

the query has the sequence column in it and i think this might be the problem as i think the sequence needs to be calculated only once the textbox has had data entered into it and the enter key is pressed but i dont know how to do that so any help would be appreciated

Thanks
Gareth

 

[Thomko]

Hello Gareth,

you are doing the sequencing in qrycustomerselect, but that query is unaffected by the user selection. Move the sequencing to the SQL command you construct in the AfterUpdate event.

HTH
Thomas

 

[Gazza2]

Thanks for the reply thomko.

Ok i understand what your saying but not sure how i would put that in the sql.And do i have to delete the sequence from the query if so i may as well delete the whole query and use the actual customers table.(or am i just talking rubbish)

Thanks
Gareth

 

[Thomko]

Hello Gareth,

applying the sequence number should be the last step. Filter first with the user input:

'--- define query with filter condition on the fly
StrSql = "SELECT sequence, accountcode, customername FROM TblCustomers" & _
"WHERE accountcode Like '*" & sText & "*' ORDER BY accountcode ;"

'--- change the definition of a 'virtual' query object (must pre-exist)
currentDB.QueryDefs("qTemp").SQL = StrSql

'--- now qTemp will return the filtered records, just without the sequence

'--- so apply the sequence in a follow-up SQL
StrSQL = "SELECT (Select Count(1) FROM qTemp WHERE A.AccountCode <=qTemp.accountcode) AS Sequence, * FROM qTemp ORDER BY qTemp.Accountcode;"

'--- and assign that to the listbox
Me.LstData.RowSource = StrSql

untested, watch out for Syntax Errors

Thomas

 

[Gazza2]

Thanks thomko

I added your code to mine but not sure if its right as the lstbox now doesnt display anything.
Here`s what ive done:

Me.LstData.Visible = True

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source
Dim sText As String 'Contents of the criteria control

sText = Trim(Me.Customer.Text)

If Not sText = "" And IsDelOrBack = False Then

StrSql = "SELECT sequence, accountcode, customername FROM TblCustomers" & _
"WHERE accountcode Like '*" & sText & "*' ORDER BY accountcode ;"

CurrentDb.QueryDefs("qTemp").SQL = StrSql

StrSql = "SELECT (Select Count(1) FROM qTemp WHERE A.AccountCode <=qTemp.accountcode) AS Sequence, * FROM qTemp ORDER BY qTemp.Accountcode;"
'Refresh the rowsource with the new SQL
Me.LstData.RowSource = StrSql

End If

When you say that the virtual query object must pre-exist do you mean i have to create a query named qtemp.

Sorry for being a bit thick

Thanks
Gareth

 

[Thomko]

Hello Gareth,

you might want to remove the "On Error Resume Next" from your code to get error messages.

Yes,

CurrentDb.QueryDefs("qTemp").SQL = StrSql

fails if there in no qTemp query. It doesn't matter what is in it as it gets overwritten anyhow.

Thomas

 

[Gazza2]

Thanks for clearing that up Thomas.

Its nearly there, the problem now is that when i enter something into the text box and press enter it lists all the matching records but the sequence number for all of them is the same(which is the amount of records displayed e.g. if there are four records displayed the sequence number is 4)

The rest of it works fine.

Thanks for the help so far.

Gareth

 

[Thomko]

Hello Gareth,

I am surprised that your code works that far

StrSql = "SELECT sequence, accountcode, customername FROM TblCustomers" & _
"WHERE accountcode Like '*" & sText & "*' ORDER BY accountcode ;"

should fail right away as I assume there is no field called sequence in TblCustomers. Have you removed the On Error Resume Next? Why have you put it in in the first place?

Post your latest code, pls, if your problem persists.

Thomas

 

[Gazza2]

Sorry Thomas forgot to say in the last post that i have taken your advice and taken the on error line out.

I also figured out that the SEQUENCE word had to come out of the SQL statement.

Here is the latest code i have

Private Sub Customer_AfterUpdate()
Me.LstData.Visible = True

Dim StrSql As String 'SQL statement for the record source
Dim sText As String 'Contents of the criteria control

sText = Trim(Me.Customer.Text)

If Not sText = "" And IsDelOrBack = False Then

StrSql = "SELECT accountcode, customername FROM TblCustomers WHERE accountcode Like '*" & sText & "*' ORDER BY accountcode ;"

CurrentDb.QueryDefs("qTemp").SQL = StrSql


StrSql = "SELECT (Select Count(1) FROM qTemp WHERE AccountCode <=qTemp.accountcode) AS Sequence, * FROM qTemp ORDER BY qTemp.Accountcode;"

Me.LstData.RowSource = StrSql

End If

Me.LstData.Requery

End Sub


This does part of the job but the sequence number for each customer is the same number (which happens to be the amount of customers matching the search criteria).

Hope this makes sense

Thanks
Gareth

 

[Thomko]

I ruined the sequencing sub query when writing some air code further up (lost the alias).

This should work as the RowSource:

StrSql = "SELECT (Select Count(1) FROM qTemp AS A WHERE A.AccountCode <=qTemp.accountcode) AS Sequence, qTemp.* FROM qTemp;"

Let me know if it does.
Thomas

 

[Gazza2]

Superb

Works like a charm

Thanks for all your help Thomas