USing Access 20007 code in Access 2003 db

[Kay]

HI,

I thought I posted this message earlier, but I can't find it on the site, so
I apologize if this is a repeat. I have imported a module from Microsoft
Developer Site to use a text box to search a list box. Is there any reason
why the vb will not work in 2003? I have implemented as microsoft suggested,
but while the text box will show the value of the item i choose in the list
box, the text box does not find the value that I type in it in the list box.
The sample database works perfectly. I use the find method example, because
I must base the listbox off of a query, so that i can reference another
unbound text box on my form that passes my phone index data. My search
textbox is called txtProviders, the list box is lstUnionALL
Code against the form and controls are as follows: I can post the code for
the basSeach module if necessary.

Option Compare Database
Option Explicit
Private mc As clsMonthCal

Private Sub lstUnionAll_AfterUpdate()
UpdateSearch Me.txtProviders, Me.lstUnionAll
End Sub

Private Sub txtProviders_Change()

Dim varRetval As Variant

varRetval = SearchRecordset(Me.txtProviders, Me.lstUnionAll, "Providers")
' You could check for errors like this:
' In this case, though, you just don't care.
'
'If IsError(varRetval) Then
' MsgBox "Error: " & varRetval


End Sub

Private Sub txtProviders_Exit(Cancel As Integer)
UpdateSearch Me.txtProviders, Me.lstUnionAll

End Sub

 

[Kay]

Still me...I should have mentioned this as well and it is the reason my
search is not working, So the real question is "Is there a way to use this
feature with my form?"

My form is a parameter form with multiple list boxes, but the list box we
want to search is the problem. The list box is unbound but works with a
phone index since my table has over 280,000 records. The query that runs the
list box has the criteria needed to work with another unbound text box that
controls the phone index.
Like [forms]![frmSearchDatabase]![txtlastnamefilter] & "*"

If i remove the criteria the Microsoft solution works, but then the phone
index does not. Anyway at all I can resolve this. thanks!

 

[David C. Holley]

IIf there's a list box with zip codes, and you manually type the zip code to
find it in the list box, what's the point of having the list box to begin
with? If you're using it as a Query By Form, you can alter the code to pull
the value from the text box instead of the list box. If validation is an
issue, you can do a DLookup() on the underlying table to determine if it
exists prior to using the value in the query.

If you need to see other fields, you can use the text box as the criteria
for the list box. For example, entering a zip code and returning all street
addresses in that zip code for the specific one to be selected.

Still me...I should have mentioned this as well and it is the reason my
search is not working, So the real question is "Is there a way to use this
feature with my form?"

My form is a parameter form with multiple list boxes, but the list box we
want to search is the problem. The list box is unbound but works with a
phone index since my table has over 280,000 records. The query that runs
the
list box has the criteria needed to work with another unbound text box
that
controls the phone index.
Like [forms]![frmSearchDatabase]![txtlastnamefilter] & "*"

If i remove the criteria the Microsoft solution works, but then the phone
index does not. Anyway at all I can resolve this. thanks!

HI,

I thought I posted this message earlier, but I can't find it on the site,
so
I apologize if this is a repeat. I have imported a module from Microsoft
Developer Site to use a text box to search a list box. Is there any
reason
why the vb will not work in 2003? I have implemented as microsoft
suggested,
but while the text box will show the value of the item i choose in the
list
box, the text box does not find the value that I type in it in the list
box.
The sample database works perfectly. I use the find method example,
because
I must base the listbox off of a query, so that i can reference another
unbound text box on my form that passes my phone index data. My search
textbox is called txtProviders, the list box is lstUnionALL
Code against the form and controls are as follows: I can post the code
for
the basSeach module if necessary.

Option Compare Database
Option Explicit
Private mc As clsMonthCal

Private Sub lstUnionAll_AfterUpdate()
UpdateSearch Me.txtProviders, Me.lstUnionAll
End Sub

Private Sub txtProviders_Change()

Dim varRetval As Variant

varRetval = SearchRecordset(Me.txtProviders, Me.lstUnionAll,
"Providers")
' You could check for errors like this:
' In this case, though, you just don't care.
'
'If IsError(varRetval) Then
' MsgBox "Error: " & varRetval


End Sub

Private Sub txtProviders_Exit(Cancel As Integer)
UpdateSearch Me.txtProviders, Me.lstUnionAll

End Sub

 

[Kay]

David,

Hopefully, this answers your questions:

The end-user receives limited information about the provider. They may not
get any form of unique identifier, but will match the name to other facts,
like the address and credentials. They need the listbox to see that
information. Second, a list box displays only around 65,000 records and even
in alphabetical listings of significantly lower, it is time consuming to
scroll through the list. the index tabs of the phone book chunk the data
into more manageable recordsets. Third, the text box only needs to act as an
auto-complete and select the first record in the list for those characters.
The final selection the user makes from the listbox sets the criteria for the
query or report.

Simply, I want the text box to autocomplete as I type and select the
corresponding record without intefering with the criteria expression that is
already behind the datasource for the listbox. It works as Microsoft
suggests as long as I do not have the criteria in the list box, but I need
the criteria in the list box to make the index tabs filter the list of 285000
based on the alpha character I choose.

Still me...I should have mentioned this as well and it is the reason my
search is not working, So the real question is "Is there a way to use this
feature with my form?"

My form is a parameter form with multiple list boxes, but the list box we
want to search is the problem. The list box is unbound but works with a
phone index since my table has over 280,000 records. The query that runs the
list box has the criteria needed to work with another unbound text box that
controls the phone index.
Like [forms]![frmSearchDatabase]![txtlastnamefilter] & "*"

If i remove the criteria the Microsoft solution works, but then the phone
index does not. Anyway at all I can resolve this. thanks!

HI,

I thought I posted this message earlier, but I can't find it on the site, so
I apologize if this is a repeat. I have imported a module from Microsoft
Developer Site to use a text box to search a list box. Is there any reason
why the vb will not work in 2003? I have implemented as microsoft suggested,
but while the text box will show the value of the item i choose in the list
box, the text box does not find the value that I type in it in the list box.
The sample database works perfectly. I use the find method example, because
I must base the listbox off of a query, so that i can reference another
unbound text box on my form that passes my phone index data. My search
textbox is called txtProviders, the list box is lstUnionALL
Code against the form and controls are as follows: I can post the code for
the basSeach module if necessary.

Option Compare Database
Option Explicit
Private mc As clsMonthCal

Private Sub lstUnionAll_AfterUpdate()
UpdateSearch Me.txtProviders, Me.lstUnionAll
End Sub

Private Sub txtProviders_Change()

Dim varRetval As Variant

varRetval = SearchRecordset(Me.txtProviders, Me.lstUnionAll, "Providers")
' You could check for errors like this:
' In this case, though, you just don't care.
'
'If IsError(varRetval) Then
' MsgBox "Error: " & varRetval


End Sub

Private Sub txtProviders_Exit(Cancel As Integer)
UpdateSearch Me.txtProviders, Me.lstUnionAll

End Sub