change in text box applies filter to combo box to show only those

[Billp]

Hi,

Looking at the On Change of a text box to apply a filter to a combo box.
The text box is populated when the form is a new record.
The text would be for example V00007 (>L00000)
I remove the trailing numbers to leave the alpha.
Example "V"
The alpha is the filter to the combo so that it filters all records LIKE as
the first letter will always be a letter.

So far I have.
Private Sub txtQuote_Number_Change()

Dim strNewNumberPrefix As String

strNewNumberPrefix = Left(Me![txtQuoteNumber], 1)

Dim s As String
If Not IsNull(Me![txtQuoteNumber]) Then
s = strNewNumberPrefix
End If

cboSER_Exists.RowSource = s

End Sub

So we filter out everything that doesn't start with "V" so that s has to be
LIKE for the row source.

Also and not only does it have to be like, but also show only those records
that have an empty check box called "QuoteNumberRef" which is in the table
that the combo is linked too.

I am lost - but hope to be found.
I have scanned the other posts and as usaul they are not quite the same.

Help would be appreciated.

Best regards
Bill

 

[[MVP]]

Hi,

Looking at the On Change of  a text box to apply a filter to a combo box.
The text box is populated when the form is a new record.
The text would be for example  V00007 (>L00000)
I remove the trailing numbers to leave the alpha.
Example "V"
The alpha is the filter to the combo so that it filters all records LIKE as
the first letter will always be a letter.

So far I have.
Private Sub txtQuote_Number_Change()

Dim strNewNumberPrefix As String

strNewNumberPrefix = Left(Me![txtQuoteNumber], 1)

Dim s As String
If Not IsNull(Me![txtQuoteNumber]) Then
        s = strNewNumberPrefix
        End If

        cboSER_Exists.RowSource = s

End Sub

So we filter out everything that doesn't start with "V" so that s has to be
LIKE for the row source.

Also and not only does it have to be like, but also show only those records
that have an empty check box called "QuoteNumberRef" which is in the table
that the combo is linked too.

I am lost - but hope to be found.
I have scanned the other posts and as usaul they are not quite the same.

Help would be appreciated.

Best regards
Bill

Hi,

OnChange event of text box would not return character(s) typed in -
instead it will return previous value. Try it with:

Private Sub txtQuote_Number_Change()
MsgBox txtQuote
End Sub

but as long as it has focus, new value can be read with:

Private Sub txtQuote_Number_Change()
MsgBox txtQuote.Text
End Sub

So I think you should replace Me![txtQuoteNumber] with Me!
[txtQuoteNumber].Text

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Billp]

Thank you,

How do I filter by LIKE?

If the alpha is isolated, how do I make it part of the filter e.g

row source LIKE "V"

Thanks
Bill

Hi,

Looking at the On Change of a text box to apply a filter to a combo box.
The text box is populated when the form is a new record.
The text would be for example V00007 (>L00000)
I remove the trailing numbers to leave the alpha.
Example "V"
The alpha is the filter to the combo so that it filters all records LIKE as
the first letter will always be a letter.

So far I have.
Private Sub txtQuote_Number_Change()

Dim strNewNumberPrefix As String

strNewNumberPrefix = Left(Me![txtQuoteNumber], 1)

Dim s As String
If Not IsNull(Me![txtQuoteNumber]) Then
s = strNewNumberPrefix
End If

cboSER_Exists.RowSource = s

End Sub

So we filter out everything that doesn't start with "V" so that s has to be
LIKE for the row source.

Also and not only does it have to be like, but also show only those records
that have an empty check box called "QuoteNumberRef" which is in the table
that the combo is linked too.

I am lost - but hope to be found.
I have scanned the other posts and as usaul they are not quite the same.

Help would be appreciated.

Best regards
Bill

Hi,

OnChange event of text box would not return character(s) typed in -
instead it will return previous value. Try it with:

Private Sub txtQuote_Number_Change()
MsgBox txtQuote
End Sub

but as long as it has focus, new value can be read with:

Private Sub txtQuote_Number_Change()
MsgBox txtQuote.Text
End Sub

So I think you should replace Me![txtQuoteNumber] with Me!
[txtQuoteNumber].Text

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[[MVP]]

Thank you,

How do I filter by LIKE?

If the alpha is isolated, how do I make it part of the filter e.g

row source LIKE "V"

Thanks
Bill

Hi,
Looking at the On Change of  a text box to apply a filter to a combo box.
The text box is populated when the form is a new record.
The text would be for example  V00007 (>L00000)
I remove the trailing numbers to leave the alpha.
Example "V"
The alpha is the filter to the combo so that it filters all records LIKE as
the first letter will always be a letter.
So far I have.
Private Sub txtQuote_Number_Change()
Dim strNewNumberPrefix As String
strNewNumberPrefix = Left(Me![txtQuoteNumber], 1)
Dim s As String
If Not IsNull(Me![txtQuoteNumber]) Then
        s = strNewNumberPrefix
        End If
        cboSER_Exists.RowSource = s
End Sub
So we filter out everything that doesn't start with "V" so that s hasto be
LIKE for the row source.
Also and not only does it have to be like, but also show only those records
that have an empty check box called "QuoteNumberRef" which is in the table
that the combo is linked too.
I am lost - but hope to be found.
I have scanned the other posts and as usaul they are not quite the same.
Help would be appreciated.
Best regards
Bill

Hi,

OnChange event of text box would not return character(s) typed in -
instead it will return previous value. Try it with:

Private Sub txtQuote_Number_Change()
  MsgBox txtQuote
End Sub

but as long as it has focus, new value can be read with:

Private Sub txtQuote_Number_Change()
  MsgBox txtQuote.Text
End Sub

So I think you should replace Me![txtQuoteNumber] with Me!
[txtQuoteNumber].Text

Regards,
Branislav Mihaljev
Microsoft Access MVP

Hi,

You can use Like with single quotes and jokers, something like:

Filter = "Like '" & txtQuoteNumber.Text & "*'"

which returns for example:

Like 'V*'

= filter string where first character is V

Like '?V*'

= filter string where second character is V

Like '*V*'

= filter string where any character is V

Like '*V'

= filter string where last character is V

In your case I think this will work:

Like "'" & Left(Me![txtQuoteNumber], 1) & "*'"

BTW I just saw I have made one mistake in previous message, here is
the correction:

Private Sub txtQuote_Number_Change()
MsgBox txtQuote_Number.Text
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP