Searching

[gjameson via AccessMonster.com]

I have this listbox with 2 columns. One for the ID, and another one for the
names ordered alphabeticaly. I have a separate textbox for entering an Id and
a name. Searching for ID only s not a wildcard search I enter the complete ID
and this goes to my form. This works already. These controls are on the same
page. I would like for the results to be entered into the listbox enabling me
to do wildcard searches on the name only, then pick which one I want
displayed on my form. What would be the best way to implement this?

 

[Guest]

Have the rowsource of the ListBox use the textbox as a criterion with
something like:
Select Name, Id From MyTable Where Name Like Forms!MyForm!MyTextbox & "*"
Order By Name

In the OnChange event of the textbox, call Me.Refresh.

Barry

 

[gjameson via AccessMonster.com]

I have so many combinations of this I can not get anything to work.
db= Customer

Textbox=scrCustomer
unbound

ListBox = SearchList
RowSouceType=Table/Query
RowSource=Customer

Here is the query I have been playing with:
SELECT DISTINCTROW Customer.ID, Customer.ABREV_NAME FROM Customer WHERE (
(Customer.ABREV_NAME) Like '" & [scrCustomer] & "*');

When I try to use anything other than what I have in my controls for Listbox,
nothing shows up. Where does the query go excatly?


Confused........

Have the rowsource of the ListBox use the textbox as a criterion with
something like:
Select Name, Id From MyTable Where Name Like Forms!MyForm!MyTextbox & "*"
Order By Name

In the OnChange event of the textbox, call Me.Refresh.

Barry

I have this listbox with 2 columns. One for the ID, and another one for the
names ordered alphabeticaly. I have a separate textbox for entering an Id and

[quoted text clipped - 3 lines]

to do wildcard searches on the name only, then pick which one I want
displayed on my form. What would be the best way to implement this?

 

[Guest]

Put the Select statement in the rowsource of the ListBox. Also, I think you
might need an additional single-quote just before the asterisk.

Barry

I have so many combinations of this I can not get anything to work.
db= Customer

Textbox=scrCustomer
unbound

ListBox = SearchList
RowSouceType=Table/Query
RowSource=Customer

Here is the query I have been playing with:
SELECT DISTINCTROW Customer.ID, Customer.ABREV_NAME FROM Customer WHERE (
(Customer.ABREV_NAME) Like '" & [scrCustomer] & "*');

When I try to use anything other than what I have in my controls for Listbox,
nothing shows up. Where does the query go excatly?


Confused........

Have the rowsource of the ListBox use the textbox as a criterion with
something like:
Select Name, Id From MyTable Where Name Like Forms!MyForm!MyTextbox & "*"
Order By Name

In the OnChange event of the textbox, call Me.Refresh.

Barry

I have this listbox with 2 columns. One for the ID, and another one for the
names ordered alphabeticaly. I have a separate textbox for entering an Id and

[quoted text clipped - 3 lines]

to do wildcard searches on the name only, then pick which one I want
displayed on my form. What would be the best way to implement this?

 

[gjameson via AccessMonster.com]

Ok, I put this in the rowselect, nothing is showing in the listbox. I tried
to add the extra single quote but it errored on it. Does the form need to be
bound?

Gerald

Put the Select statement in the rowsource of the ListBox. Also, I think you
might need an additional single-quote just before the asterisk.

Barry

I have so many combinations of this I can not get anything to work.
db= Customer

[quoted text clipped - 29 lines]

 

[Guest]

What is scrCustomer? This should point to the textbox.
I think the quotes were wrong if you are entering this directly in the
listbox's rowsource. Try it this way:

SELECT DISTINCTROW Customer.ID, Customer.ABREV_NAME FROM Customer WHERE
((Customer.ABREV_NAME) Like Forms!frmCustomers!txtCustomer & "*");

I'm guessing about the names of you form and textbox.

Barry

Ok, I put this in the rowselect, nothing is showing in the listbox. I tried
to add the extra single quote but it errored on it. Does the form need to be
bound?

Gerald

Put the Select statement in the rowsource of the ListBox. Also, I think you
might need an additional single-quote just before the asterisk.

Barry

I have so many combinations of this I can not get anything to work.
db= Customer

[quoted text clipped - 29 lines]

to do wildcard searches on the name only, then pick which one I want
displayed on my form. What would be the best way to implement this?

 

[gjameson via AccessMonster.com]

Barry,

Thanks for your help. This works great.

I have one more question. Every time I type in my textbox, the listbox
upadates like it should, but the textbox highlights the letter I typed. Not
allowing me to type another letter until I move the cursor over withthe mouse.
Is this something I have live with or can this be changed?

I am using :

Private Sub scrCustomer_Change()
Me.Refresh
End Sub

What is scrCustomer? This should point to the textbox.
I think the quotes were wrong if you are entering this directly in the
listbox's rowsource. Try it this way:

SELECT DISTINCTROW Customer.ID, Customer.ABREV_NAME FROM Customer WHERE
((Customer.ABREV_NAME) Like Forms!frmCustomers!txtCustomer & "*");

I'm guessing about the names of you form and textbox.

Barry

Ok, I put this in the rowselect, nothing is showing in the listbox. I tried
to add the extra single quote but it errored on it. Does the form need to be

[quoted text clipped - 12 lines]

 

[Guest]

Yeah, that happens as a result of the refresh. To fix it, put this after the
refresh staement:
Me.scrCustomer.SelStart=Len(Me.scrCustomer)

SelStart tells it where to start the selection, in this case you want to
start a zero-character selection at the end of the text.

Barry

Barry,

Thanks for your help. This works great.

I have one more question. Every time I type in my textbox, the listbox
upadates like it should, but the textbox highlights the letter I typed. Not
allowing me to type another letter until I move the cursor over withthe mouse.
Is this something I have live with or can this be changed?

I am using :

Private Sub scrCustomer_Change()
Me.Refresh
End Sub

What is scrCustomer? This should point to the textbox.
I think the quotes were wrong if you are entering this directly in the
listbox's rowsource. Try it this way:

SELECT DISTINCTROW Customer.ID, Customer.ABREV_NAME FROM Customer WHERE
((Customer.ABREV_NAME) Like Forms!frmCustomers!txtCustomer & "*");

I'm guessing about the names of you form and textbox.

Barry

Ok, I put this in the rowselect, nothing is showing in the listbox. I tried
to add the extra single quote but it errored on it. Does the form need to be

[quoted text clipped - 12 lines]

to do wildcard searches on the name only, then pick which one I want
displayed on my form. What would be the best way to implement this?