Sorting a ListBox with multiple source rows

[ram_rtg]

Hi all,

I have a ListBox that has a two options for it's RowSource. This is based on the users login ID (based on the value of a text box on the form) and is used to hide records from certain users. The records displayed in the ListBox can be double clicked and loads a form with record details.

I use the following code to identify which RowSource to load:

Private Sub Form_Open(Cancel As Integer)
If Me.TextBox.Value = "Boss" Or Me.TextBox.Value = "Admin" Then
Me.ListBox.RowSource = "qryDynamicSearch"
Else
Me.ListBox.RowSource = "qryDynamicSearchRestricted"
End If
End Sub

I would like to sort the records displayed by column and I have found a great solution in the following sample database that does exactly what I want but I don't know how to apply it without losing the option of multiple Row Sources (based on user login ID).
'access-programmers.co.uk/forums/showthread.php?t=109525

I'm afraid if I use SQL I won't be able to control the Row Source.
Is there a way to replicate the sample database in the above link without using SQL?

 

[fvdb]

Apply sorting in the queries qryDynamicSearch and qryDynamicSearchRestricted

 

[ram_rtg]

Thank you for the quick response fvdb.
I know how to do this but I want the user to be able to do this manually through clicking the heading of the column/field after ONE of those queries has run.
If only there was a way to embed a sort option into the query at requery time.

 

[fvdb]

Is the forms wich you open an continious form or a datasheetview ? If datasheet the user still can apply sorting on the headers

 

[ram_rtg]

Is the forms wich you open an continious form or a datasheetview ? If datasheet the user still can apply sorting on the headers

I'm not sure.
I've inherited the database from a previous staff member.
Is there anyway I can tell?

 

[fvdb]

When you open the form does the subform looks like a query window? Can you click on the headers for sorting?

 

[ram_rtg]

When you open the form does the subform looks like a query window? Can you click on the headers for sorting?

Sorry for the late response fvdb. I'm on the other side of the world
I have attached a copy of this form in my Database.

 

[fvdb]

Sorry for the late response fvdb. I'm on the other side of the world
I have attached a copy of this form in my Database.

No problem.

I adjusted a little bit the DB to show you what i mean. If you add a subform in datasheet view to the form you get the headers and you can apply sorting without programming.

Code after the form is not working because misses some fields , ....

But at least you have an example.

To accomplish this quick, delete the one now on the form and just drag and drop the query on the main form. I makes by itself then a subform. Adjust the format parameters so records selectors , ... not appears on and you have almost the same view like the old one

 

[ram_rtg]

Hey thanks for that it seems really promising.

I have played around with it and I am quite pleased however I am unable to figure out how I would get into one of the records that are displayed.

On the old ListBox I could simply double click or click the "Display Record" button. Now whenever I click this button I am presented with a compile error This is normal since we have removed the ListBox however when I replace the old listbox reference to the new subform, it doesn't like the "Column(0)" section of the code:
stLinkCriteria = "[ID]=" & Me.Subformulier_qryDynamicSearch.Column(0)

Any ideas on how I would change this code to something that works with the subform so when a user double clicks or clicks a command button it shows the selected record?

 

[fvdb]

On the subform after the ID field you put a OnClick sub

 DoCmd.OpenForm "frmViewTask", acNormal, , "ID='" & Me.ID & "'"

 

[ram_rtg]

Hey thanks for that it seems really promising.

I have played around with it and I am quite pleased however I am unable to figure out how I would get into one of the records that are displayed.

On the old ListBox I could simply double click or click the "Display Record" button. Now whenever I click this button I am presented with a compile error This is normal since we have removed the ListBox however when I replace the old listbox reference to the new subform, it doesn't like the "Column(0)" section of the code:
stLinkCriteria = "[ID]=" & Me.Subformulier_qryDynamicSearch.Column(0)

Any ideas on how I would change this code to something that works with the subform so when a user double clicks or clicks a command button it shows the selected record?

The same happens with "ItemData(1)" in the search code:
Me.Subformulier_qryDynamicSearch = Me.Subformulier_qryDynamicSearch.ItemData(1)

 

[fvdb]

Adjust so the search term filters on the task name and dates are also selectable and filters to

Status can not because don't have whole DB

This would get you started

As you see i deleted many VBA code for doing the same with many less

Also added the show record link wich when you click on an ID will open frmTest filtered on the record selected

 

[ram_rtg]

Adjust so the search term filters on the task name and dates are also selectable and filters to

Status can not because don't have whole DB

This would get you started

As you see i deleted many VBA code for doing the same with many less

Also added the show record link wich when you click on an ID will open frmTest filtered on the record selected

Thanks. By the way the forms prompts for Parameter values when it opens. I'll have a thorough look at it tomorrow at work. Thank you for your help.