how to requery the list

[associates]

Hi,

I have a form that has a listbox and four comboboxes that allow users to perform filtering. And any changes to the value of comboboxes will change the data items listed in the listbox (like filtering). My question is this. I'd like to have a button called clear that is used to clear all the comboboxes values. In another word, i want to reset all the comboboxes to original state where no selection is made (all empty) and the listbox should list out all items, not specific records.

Here is my sample code. To make it short, i just show only 1 combobox.

Private Sub CB_JobRegister_AfterUpdate()
Me.TB_JobRegQuery = "*"
DoEvents
If Not IsNull(Me.CB_JobRegister) Then
If Me.CB_JobRegister <> "" Then
Me.TB_JobRegQuery = Me.CB_JobRegister
End If
End If

DoEvents
Me.Refresh
End Sub

Private Sub Form_Load()
Me.TB_JobRegQuery = "*"
End Sub

Private Sub CM_Clearbutton_Click()

Me.CB_JobRegister = ""

'Me.Requery
'Me.ListJob.requery
Call CB_JobRegister_AfterUpdate

'Me.Refresh
End Sub

I've tried many ways but none of them worked. What happens is when i press on the clear button, it clears all the combobox but the listbox, ListJob, is empty. I tried to requery it but didn't work. Any way to get the listbox showing all the records again after the clear button's clicked.

Thank you in advance and look forward to hearing from you

 

[benjamenus]

Sorry for stating the obvious, but it's a good a place as any to start... Here are a couple of observations

the ListJob.Requery command is commented out

the 'requery' command is lowercase which suggests that the name of the control (ListJob) is not correct because when moving away from the line of code, it would become Sentence case (unless it was a comment - which it currently is)

I'd tackle those first, because it basically seems correct.

Good luck

 

[associates]

Hi, Benjamenus. Thank you for your reply.

Sorry, The requery should be Requery instead of requery. I forgot to put it an Uppercase for 'R' because i just typed it in in a rush.
Those that i commented out are the ones that i have already tried out but didn't work.

Your help is greatly appreciated. Thank you

 

[ProgramError]

Hi associates,

You need to reset the row source of the list box to its underlying query.

SELECT *.* FROM [tableName]

or whatever the sql was for listbox contents.





Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[TheAceMan1]

How are ya associates . . . . .

For the four comboxes, add a question mark [blue]?[/blue] to the [blue]Tag[/blue] property of each. To clear them & reset the Listbox try:

[blue]   Dim ctl As Control
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then ctl = Null
   Next

   Me!ListBoxName.Requery[/blue]

See Ya! . . . . . .

 

[associates]

Hi, Thank you to you both TheAceMan and ProgramError for your help. I very appreciated it.

It's working now... Thank you. However, I'm facing another problem. That is remember that i said i have 4 ComboBoxes(CB for ComboBoxes) for filtering the record items on the listbox.

They are like CB_invoice_No, CB_DateFrom, CB_DateTo, CB_Status and so on.

As i add in two Comboboxes to the form i.e CB_DateFrom & CB_DateTo, I'm encountering problem. For example, The listbox becomes empty(no records at all). Because i don't directly use the values of the comboboxes to execute query but rather textboxes. In another words, in the SQL statement for the listbox, i have as follows

invoice_no -> criteria: Like [Forms]![F1]![TB_InvoiceNoquery]
invoice_date -> criteria: Between [Forms]![F1]![TB_DateFromquery] And [Forms]![F1]![TB_DateToquery]
invoice_status -> criteria: Like [Forms]![F1]![TB_StatusQuery]

Private Sub Form_Load()
Me.TB_InvoiceNoquery = "*"
Me.TB_StatusQuery = "*"
Me.TB_DateFromquery = "*"
Me.TB_DateToquery = "*"
End Sub

The following is a code for CB_Datefrom. The same code goes for all the other comboboxes.
Private Sub CB_datefrom_AfterUpdate()
Me.TB_DateFromquery = "*"
DoEvents
If Not IsNull(Me.CB_Datefrom) Then
If Me.CB_Datefrom <> "" Then
Me.TB_DateFromquery = Me.CB_Datefrom
End If
End If

DoEvents
Me.Refresh
End Sub

And I got this error when switching between the form design and form view. It says " The expression is typed incorrectly, or it is too complex to be evaluated. For examples a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I don't know how to fix this because i think i have to use "between" in the sql especially when we want to look up invoices between x date and y date.

I hope i can understand my explanation above. Thank you in advance and look forward to hearing from you.

 

[TheAceMan1]

[blue]Because i don't directly use the values of the comboboxes to execute query but rather textboxes.[/blue]

[ol][li]Are these textboxes used for [blue]any other purpose?[/blue][/li]
[li]The textboxes are [blue]unbound[/blue] . . . [blue]Yes?[/blue][/li][/ol]

See Ya! . . . . . .

 

[associates]

Hi, TheAceMan. Thanks again for your reply.

Answer to your question no.1 is probably No. What happen is that there are a list of records listed in the combobox and user can make a selection from it or type in a value directly into the combobox which then invokes the CB_DateFrom_AfterUpdate. In the sub, I copy the value from the combobox CB_DateFrom to TB_DateFromquery. So when it comes to executing sql query, it looks up TB_DateFromquery for value to perform query action.

Sorry for making you confused. I mean i can get the value from CB_DateFrom and put it into the sql statement in the listbox as a criteria. But i got the same problem getting nothing in the listbox.

Or if you have any better idea of doing this, I'm more than happy to hear it.

I mean this whole thing is about getting the input from combobox for filtering and then perform sql query and finally output it into the listbox. The reason is that we have records of more than 5000 and there is a time when we're only interested in specific records.

The answer to your question No.2 is Yes. They are unbound. They are used only as values for sql query.

Thank you once again and look forward to hearing from you

 

[associates]

Hi,

Just a quick question in regards to the above issue. I think the error message that read "The expression is typed incorrectly, or it is too complex to be evaluated. For examples a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables" might be caused by the criteria that i put into the SQL statement.

For invoice_date -> in criteria: Between [Forms]![F1]![TB_DateFromquery] And [Forms]![F1]![TB_DateToquery].

I thought this is right sql query expression if I want to produce a list of records for a certain period of time.

Thanks a lot in advance.

 

[TheAceMan1]

associates . . . . .

There's no such date as [blue]*[/blue]:

[blue]   Between * AND 1/1/2005
   Between 1/1/2005 AND *
   Between * AND *[/blue]

See Ya! . . . . . .