Listbox does not update

[Vulcan1500]

With help of this forum I'm close to the solution of my problem. I use in a form a listbox and 6 textboxes to to filter the form. This works now, but I do not understand why the listbox does not update and show the records that are filtered. The listbox does not filter at all and shows all records. Pls advise.

 

[ShaneMan]

With help of this forum I'm close to the solution of my problem. I use in a form a listbox and 6 textboxes to to filter the form. This works now, but I do not understand why the listbox does not update and show the records that are filtered. The listbox does not filter at all and shows all records. Pls advise.

Vulcan,

You will need to post your code so folks can take a look at it and probably identify your problem.

Shane

 

[Vulcan1500]

Herewith the searchform of the database. Hope you can help me.

 

[ShaneMan]

Herewith the searchform of the database. Hope you can help me.

Vulcan,

If I did the attachment right you should be able to open up your db and see if that is what you are wanting.

Hope I've helped,
Shane

 

[Vulcan1500]

Vulcan,

If I did the attachment right you should be able to open up your db and see if that is what you are wanting.

Hope I've helped,
Shane

Thanks for your swift reply an the listbox is finally doing what I wanted it to do. Thanks for your help. I will now study your code that made this possible and if I have questions I will come back to you via this forum. Okay?

 

[Vulcan1500]

Listbox or form does not update

Shane, looking at your revised code and form, I understand that the listbox is filtered and correctly shown, but now the form is not filtered. Underneath the area in the footer of the form where the searchtextboxes are two other fields are displayed. The left one should show the number of record left after filtering and the right one the pkProject of the first record. In the form a send to this forum these two fields were updating correctly, but the listbox wasn't. Is it possible to do both?

 

[ShaneMan]

Shane, looking at your revised code and form, I understand that the listbox is filtered and correctly shown, but now the form is not filtered. Underneath the area in the footer of the form where the searchtextboxes are two other fields are displayed. The left one should show the number of record left after filtering and the right one the pkProject of the first record. In the form a send to this forum these two fields were updating correctly, but the listbox wasn't. Is it possible to do both?

Hey Vulcan,

You should be able to use ListCount on your ListBox control to take care of your number of records. I'm not sure I understand what your wanting with the second txtBox but if I go with what I think your asking then you should be able to reference your listbox for the particular record and column that your wanting it to display.

HTH,
Shane

 

[Vulcan1500]

Thanks for your reply. ListCount? I'm working with access and have no background in VBA, but I'm learning every day. I've scheduled a training in January.
The second txtbox was used for testing purposes only. I wanted to know at which record is the selector pointing during filtering when it was not working properly. Now you gave me the advise to filter the listbox it is not necessary anymore. The next step is to select the record I'm interested in and open it with the button with the glasses in a different form. This gives me all the information of the record. It's not working yet, but I'm working on it. Maybe I come back to you.

 

[ShaneMan]

Thanks for your reply. ListCount? I'm working with access and have no background in VBA, but I'm learning every day. I've scheduled a training in January.
The second txtbox was used for testing purposes only. I wanted to know at which record is the selector pointing during filtering when it was not working properly. Now you gave me the advise to filter the listbox it is not necessary anymore. The next step is to select the record I'm interested in and open it with the button with the glasses in a different form. This gives me all the information of the record. It's not working yet, but I'm working on it. Maybe I come back to you.

For your ListCount:
Put this: Me.txtRecordsFiltered = Me.lstTSHDAdvancedSearch.ListCount
After this line: (in the code behind the filter button)
Me.lstTSHDAdvancedSearch.RowSource = stSQL & strWhere

You will probably want to do the same line of code (in red) in the OnClick event behind the command button that removes the filter on your listbox. It would be easier to tell you this way than to tell you to create a function that would do both for you.

HTH,
Shane

 

[Vulcan1500]

Thanks Shaneman. It's all working now apart from one thing I'm working on right now. After filtering the listbox using at least one of the textfields and clicking one of the headers to sort, then all records are back in view. I know the reason for this and try to research of the content of strWhere and strSQL still exists when the form executes the on click event. For this I opened the immediate window and entered ?strWhere. This gives a blank line, but I'm not convimced that I'm doing this right. If I can see what the content of these strings are, then I should be able to use the same filter in the query as I used in the filter. I hope my question is clear for you.

 

[ShaneMan]

Thanks Shaneman. It's all working now apart from one thing I'm working on right now. After filtering the listbox using at least one of the textfields and clicking one of the headers to sort, then all records are back in view. I know the reason for this and try to research of the content of strWhere and strSQL still exists when the form executes the on click event. For this I opened the immediate window and entered ?strWhere. This gives a blank line, but I'm not convimced that I'm doing this right. If I can see what the content of these strings are, then I should be able to use the same filter in the query as I used in the filter. I hope my question is clear for you.

Hey Vulcan,

I think I'm following your question. If you are wanting to sort now you will have to bring the ORDER BY part of your SQL statement into the equation. stSQL is the SELECT and FROM part of the SQL statement for the listbox. The strWhere is the WHERE part of the SQL statement for the listbox. Being stWhere is dynamic data due to users choice then we are concatenating the two strings together to tell the listbox what we what to see. You will need to create a string for the ORDER BY part of the SQL statement and concatenate it to the stSQL and strWhere. Hope this makes sense.

HTH
Shane

 

[Vulcan1500]

Yes I understand this Shaneman, but can I check the value of strWhere by typing ?strWhere in the immediate screen (Ctrl-G). I do not get any value (an empty line) and the chance is big I'm doing something wrong here. If this is possible it gives me the possibility to track the value and try to understand how the system works.

 

[ShaneMan]

Yes I understand this Shaneman, but can I check the value of strWhere by typing ?strWhere in the immediate screen (Ctrl-G). I do not get any value (an empty line) and the chance is big I'm doing something wrong here. If this is possible it gives me the possibility to track the value and try to understand how the system works.

Ctrl-G takes you to the immediate window. strWhere is based off of text controls on your form having data in them so I don't think your going to get what your looking for in the immediate window. You can go into your code behind the filter button and use a MsgBox to display strWhere.

Like this: MsgBox stWhere

The message box will pop up and display the value of strWhere.

Good Luck,
Shane

 

[Vulcan1500]

It's all working now and I'm happy with the result. I believe things can be done more efficient by using 'function' and 'call' as you said, but I will change that after I have done my training in January. Thanks for your swift replies and very useful advise.

 

[ShaneMan]

It's all working now and I'm happy with the result. I believe things can be done more efficient by using 'function' and 'call' as you said, but I will change that after I have done my training in January. Thanks for your swift replies and very useful advise.

Your welcome Vulcan. Glad I could help.

Shane