List Box Filter Question

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

On my form I have a un bound text box [InvoiceID] and in my List box I have
all Invoices with the same [InvoiceID] how do I filter InvoiceID as it will
show the same as my Form Opened
 
On my form I have a un bound text box [InvoiceID] and in my List box I have
all Invoices with the same [InvoiceID] how do I filter InvoiceID as it will
show the same as my Form Opened
Click to expand...

Your message is a bit confusing.
There is more than 1 record with the same InvoiceID? How do you keep
track of them? Perhaps you meant CompanyID or something like that, as
one company can indeed have more than one Invoice.

If the text box [InvoiceID] is unbound, it does not contain any data
until you enter a value AFTER the form opens.

Perhaps you can use the [InvoiceID] control's AfterUpdate event to
fill the List box.

Code the control's AfterUpdate event:
Me.[ListBoxName].Rowsource = "Select TableName.* from TableName Where
TableName.[InvoiceID] = " & Me.[InvoiceID]

The above assumes the [TableID] field in the table is a Number
Datatype.

Change InvoiceID to whatever the actual name of the table field is.

Open the form. Enter the wanted value into the [InvoiceID] control.
The List box will display the appropriate invoice number records

An even better solution would be to use a combo box to select the
wanted invoiceID from. This way only actual numbers will be available,
so no inadvertent wrong numbers will be entered. You can use the Combo
wizard to fill the combo box with the InvoiceID values from the table.
 
Thanks Fred, what if my List box is getting its info from a query..But this
is erroring ...Regards Bob
Private Sub lstShowPercentage_AfterUpdate()
Me.[cmbClientTotal].RowSource = "Select qryClientOnly.* from qryClientOnly
Where"
qryClientOnly.[InvoiceID] = " & Me.[InvoiceID]"

End Sub
fredg said:
On my form I have a un bound text box [InvoiceID] and in my List box I
have
all Invoices with the same [InvoiceID] how do I filter InvoiceID as it
will
show the same as my Form Opened
Click to expand...

Your message is a bit confusing.
There is more than 1 record with the same InvoiceID? How do you keep
track of them? Perhaps you meant CompanyID or something like that, as
one company can indeed have more than one Invoice.

If the text box [InvoiceID] is unbound, it does not contain any data
until you enter a value AFTER the form opens.

Perhaps you can use the [InvoiceID] control's AfterUpdate event to
fill the List box.

Code the control's AfterUpdate event:
Me.[ListBoxName].Rowsource = "Select TableName.* from TableName Where
TableName.[InvoiceID] = " & Me.[InvoiceID]

The above assumes the [TableID] field in the table is a Number
Datatype.

Change InvoiceID to whatever the actual name of the table field is.

Open the form. Enter the wanted value into the [InvoiceID] control.
The List box will display the appropriate invoice number records

An even better solution would be to use a combo box to select the
wanted invoiceID from. This way only actual numbers will be available,
so no inadvertent wrong numbers will be entered. You can use the Combo
wizard to fill the combo box with the InvoiceID values from the table.
Click to expand...
 
Bob Vance said:
On my form I have a un bound text box [InvoiceID] and in my List box I
have all Invoices with the same [InvoiceID] how do I filter InvoiceID as
it will show the same as my Form Opened
Click to expand...


I'm not sure what you mean by "show the same as my Form Opened". Could you
explain that?

Are you asking about how to filter the list box so that it shows only the
records that match the [InvoiceID] text box? For that, FredG's advice looks
good to me, but I see from your reply that you got the code slightly wrong.
You posted this code:
Private Sub lstShowPercentage_AfterUpdate()
Me.[cmbClientTotal].RowSource = "Select qryClientOnly.* from qryClientOnly
Where"
qryClientOnly.[InvoiceID] = " & Me.[InvoiceID]"

End Sub
Click to expand...

But it should be like this:

'----- start of code -----
Private Sub lstShowPercentage_AfterUpdate()

Me.[cmbClientTotal].RowSource = _
"Select * from qryClientOnly Where [InvoiceID] = " & _
Me.[InvoiceID]

End Sub
'----- end of code -----

That is assuming that InvoiceID is a numeric field. If it's text, you need
to put quotes around the value, like this:

Me.cmbClientTotal.RowSource = _
"Select * from qryClientOnly Where InvoiceID = '" & _
Me.InvoiceID & "'"

I simplified the query and code slightly to remove some elements that are
unnecessary in this particular case.

I'm curious, though. You told us about a text box, and a list box that you
want to filter based on the text box. But the controls your code names are
"lstShowPercentage" (in the place where I'd expect you to be referring to
the text box) and "cmbClientTotal" (in.the place where I'd expect you to be
referring to the list box). But the prefix "lst" usually refers to a list
box, and "cmb" usually refers to a combo box. So either you are using
misleading prefixes, or at least one of us is confused.
 
Back
Top