List Box Not Responding!

[mapat]

Hello,

I have a 'txtfieldA', and a 'ListBoxA' which is supposed to be linked to 'tableA'.
tableA has the following fields: PatientID, PLName, PFName.

Now as I type in the txtfieldA the person's last name I want that record to show up on the 'ListBoxA'.

Example:
If I have 3 Smiths on my table (SmithA, SmithB, and SmithC), when I type 'Smi' (or 'S', or 'Sm') on 'txtfieldA', the three of them should pop up on 'ListBoxA' as I'm typing the matching characters.

So far I have the following code on the "On Key Press" event of 'txtfieldA', and it's not responding:

Me.ListBoxA.RowSource = "SELECT [tPatients].[PatientID], [tPatients].[PLName] & ', ' & [tPatients].[PFName], [tPatients].[PHomePhone] FROM [tPatients] ORDER BY [PLName], [PFName] WHERE [tPatients].[PLName] LIKE [Forms]![Main]![txtfieldA];"
Me.ListBoxA.Requery

Can someone give me a hand on this one?
Thank you everyone

 

[boblarson]

Use the ON CHANGE event (not keypress) and you need to use the .TEXT property when doing this as it hasn't updated yet to have the value:

Me.ListBoxA.RowSource = "SELECT [tPatients].[PatientID], [tPatients].[PLName] & ', ' & [tPatients].[PFName], [tPatients].[PHomePhone] FROM [tPatients] ORDER BY [PLName], [PFName] WHERE [tPatients].[PLName] LIKE [B][COLOR="Red"]" & Chr(34)[/COLOR][/B] & [COLOR="red"][B]Me.txtfieldA.Text & "*" & Chr(34)[/B][/COLOR]
Me.ListBoxA.Requery

 

[pbaldy]

Try using the .Text property of the textbox. The .Value property won't have been updated at this point. You'll also need to add a wildcard to your criteria.

 

[The_Doc_Man]

You don't normally use a text-box to do something in a list box. However, it is possible.

Here's what I would do.

Never mind the keypress stuff. Make the list box have three columns, the last name, first name, and ID number. Make the widths of the last name and first name non-zero. Up to you as to whether you want the ID number (in the list box) to have zero width. If you use zero width, the ID is there but not visible.

Now just tab into the list box and enter the first couple of characters of the last name. It will instantly scroll to the first record matching the characters you've typed. Click in the list box to select the record you want. At that point, you can look at the list box properties. [LB].ListIndex gives you row that was selected. [LB].Column(0) would be the last name. [LB].Column(1) would be the first name. [LB].Column(2) would be the ID number.

If you REALLY wanted to do this the way you described, there is an efficiency issue in that you really need to "muck" with the [LB].RowSource to use the [TB] contents in the selection. You could reassign the [LB].RowSource in some [TB] event code. The string you would build to replace the row source to use the text box contents might look like

[LB].RowSource = "SELECT [LastName],[FirstName],[IDNum] FROM PatientList WHERE [LastName] LIKE '" &[TB] & "*' ;"

(Watch out for the extra single-quote characters in there.)

That wouldn't work as you originally described unless you want to dynamically rebuild the row source for every stinkin' keystroke in order to re-substitute for [TB] each time, followed by a REQUERY on the control after each keystroke. The overhead would be tremendous. To keep it simple, I'd say that the response for this would suck.

The middle ground would be to enter the partial last name in the [TB] and then TAB out of that box. In the [TB].LostFocus event routine, dynamically build the RowSource and do the requery once. A "bell and whistle" addition would be that in the LostFocus routine you could do a DCount of how many records would match the name you entered in [TB]. If zero, you could "refuse" the LostFocus by doing a [TB].SetFocus and pop up a message box that says "No patients have a last name that starts with the indicated sequence."

The reason this is awkward is that the List Box would directly do what you wanted. Doing this in a text box that FEEDS a list box is a lot more awkward. I also suggest that you search this forum for the topic "Cascading Combo Boxes" - which is very similar to what you described, though not exactly so.

BTW - using a list box vs. a combo box is a "six of one, half-dozen of the other" situation when you are not using the list box's MultiSelect feature. The only difference is whether the listbox shows multiple names before you try to make a selection in it. If you used a combo box, you would get a temporary drop-down with the same general properties, just a slightly different appearance.

 

[mapat]

You don't normally use a text-box to do something in a list box. However, it is possible.

Here's what I would do.

Never mind the keypress stuff. Make the list box have three columns, the last name, first name, and ID number. Make the widths of the last name and first name non-zero. Up to you as to whether you want the ID number (in the list box) to have zero width. If you use zero width, the ID is there but not visible.

Now just tab into the list box and enter the first couple of characters of the last name. It will instantly scroll to the first record matching the characters you've typed. Click in the list box to select the record you want. At that point, you can look at the list box properties. [LB].ListIndex gives you row that was selected. [LB].Column(0) would be the last name. [LB].Column(1) would be the first name. [LB].Column(2) would be the ID number.

If you REALLY wanted to do this the way you described, there is an efficiency issue in that you really need to "muck" with the [LB].RowSource to use the [TB] contents in the selection. You could reassign the [LB].RowSource in some [TB] event code. The string you would build to replace the row source to use the text box contents might look like

[LB].RowSource = "SELECT [LastName],[FirstName],[IDNum] FROM PatientList WHERE [LastName] LIKE '" &[TB] & "*' ;"

(Watch out for the extra single-quote characters in there.)

That wouldn't work as you originally described unless you want to dynamically rebuild the row source for every stinkin' keystroke in order to re-substitute for [TB] each time, followed by a REQUERY on the control after each keystroke. The overhead would be tremendous. To keep it simple, I'd say that the response for this would suck.

The middle ground would be to enter the partial last name in the [TB] and then TAB out of that box. In the [TB].LostFocus event routine, dynamically build the RowSource and do the requery once. A "bell and whistle" addition would be that in the LostFocus routine you could do a DCount of how many records would match the name you entered in [TB]. If zero, you could "refuse" the LostFocus by doing a [TB].SetFocus and pop up a message box that says "No patients have a last name that starts with the indicated sequence."

The reason this is awkward is that the List Box would directly do what you wanted. Doing this in a text box that FEEDS a list box is a lot more awkward. I also suggest that you search this forum for the topic "Cascading Combo Boxes" - which is very similar to what you described, though not exactly so.

BTW - using a list box vs. a combo box is a "six of one, half-dozen of the other" situation when you are not using the list box's MultiSelect feature. The only difference is whether the listbox shows multiple names before you try to make a selection in it. If you used a combo box, you would get a temporary drop-down with the same general properties, just a slightly different appearance.

I do want to change the RowSource on every key stroke. I've tried both syntax and neither one of them worked. So I'm still stuck. Thank you.

 

[mapat]

Use the ON CHANGE event (not keypress) and you need to use the .TEXT property when doing this as it hasn't updated yet to have the value:

Me.ListBoxA.RowSource = "SELECT [tPatients].[PatientID], [tPatients].[PLName] & ', ' & [tPatients].[PFName], [tPatients].[PHomePhone] FROM [tPatients] ORDER BY [PLName], [PFName] WHERE [tPatients].[PLName] LIKE [B][COLOR=Red]" & Chr(34)[/COLOR][/B] & [COLOR=red][B]Me.txtfieldA.Text & "*" & Chr(34)[/B][/COLOR]
Me.ListBoxA.Requery

Thanks for your response. This syntax is still not working.

 

[boblarson]

Thanks for your response. This syntax is still not working.

When you say it isn't working, what happens - an error message, nothing, what?

 

[Khalid_Afridi]

I use the following query as my record source to the list box:

SELECT tblLocation.Status, tblLocation.Location, tblLocation.LocationDetail, tblLocation.LocationStatus, tblLocation.ReqToActivate, tblLocation.ReqtdDte, tblLocation.ReqBy, tblLocation.sUserName, tblLocation.ReqName
FROM tblLocation
WHERE (((tblLocation.Location) Like "*" & [Forms]![SearchLocation_F]![txtSearchVal] & "*") AND ((tblLocation.LocationStatus)<>"wrong")) OR (((tblLocation.Location) Like "*" & [Forms]![SearchLocation_F]![txtSearchVal] & "*") AND ((tblLocation.LocationStatus) Is Null))
ORDER BY tblLocation.Status, tblLocation.Location;

and I have a text box name "txtSearch" on my form:

I have written the following code on my txtSearch on change event:

Private Sub txtSearch_Change()
    Dim vStrSearch As String
    vStrSearch = txtSearch.Text
    txtSearchVal.Value = vStrSearch
    Me.LocationsResults.Requery
End Sub

This work exactly what I want. It give the Location name with each stroke I type.

I suggest you to change your query accordingly and create a textbox control on your form and on change event of the form type the code and see what happens.

HTH

 

[pbaldy]

The clauses are out of order:

SELECT
FROM
WHERE
ORDER BY

 

[boblarson]

The clauses are out of order:

SELECT
FROM
WHERE
ORDER BY

Ah, that had escaped me (as it seems my brain gets mushier and mushier these days)...

 

[boblarson]

So Paul got it - it should really be:

Me.ListBoxA.RowSource = "SELECT [tPatients].[PatientID], [tPatients].[PLName] & ', ' & [tPatients].[PFName], [tPatients].[PHomePhone] FROM [tPatients] WHERE [tPatients].[PLName] LIKE " & Chr(34) & Me.txtfieldA.Text & "*" & Chr(34) ORDER BY [PLName], [PFName]
Me.ListBoxA.Requery

 

[mapat]

When you say it isn't working, what happens - an error message, nothing, what?

Well, no error message, the List box continues to be blank. I pasted the same code on to the "On Lost Focus" event and once I click out of the txtfield, it behaves the same way. Also tried storing the "txtfield.Text" value on a temp variable and used this var on the syntax instead and it does the same thing (List box stays blank)

Thanks

 

[boblarson]

Well, no error message, the List box continues to be blank. I pasted the same code on to the "On Lost Focus" event and once I click out of the txtfield, it behaves the same way. Also tried storing the "txtfield.Text" value on a temp variable and used this var on the syntax instead and it does the same thing (List box stays blank)

Thanks

Did you try with the revised SQL (because it was wrong as Paul pointed out? )

 

[mapat]

Wow,

Thanks. Based on your last syntax I realized that the ORDER BY clause was before the LIKE word. Now it works. Thanks everyone again

 

[boblarson]

Whew! Glad you got it working.