Run a query based on user input to a text box

[Topcat668]

I am creating a database to manage Auction Bids for a live radio station
phone auction weekend.

I have a form where the user is entering names as the bidders call in. I
want to be able to have entry into a text box cause a query to run and
populate a list box so that if a caller has been entered before, the user can
click on an existing name.

So for example, if the first keystroke is A , then the list box will be
populated with all names beginning with A. If the next keystroke is l to
give Al then the list box will be repopulated with only names beginning with
Al (e.g. Alice, Alison etc).

I can create the query and the VBA to run on a keystroke but every time the
focus returns to the text box, all the text already entered is highlighted
and the next key stroke overwrites any data already entered. Hence if the
first key is A, all A names are displayed, thent he next keystroke l shows
all names beginning with l as the A has been overwritten.

Please help!

 

[Ken Snell \(MVP\)]

For that textbox, add VBA programming to its GotFocus event:

Private Sub NameOfTheTextBox_GotFocus()
Me.NameOfTheTextBox.SelStart = Len(Me.NameOfTheTextBox.Value)
Me.NameOfTheTextBox.SelLength = 0
End Sub

 

[Topcat668]

Cheers Ken!

I found that the GotFocus event wasn't firing so I used the routine at the
end of the KeyUp event I'm using to re-fill the text box. Routine in total
looks:

Private Sub Text2_KeyUp(KeyCode As Integer, Shift As Integer)

Me.Refresh

Me.List0.RowSource = "SELECT Table1.NAme FROM Table1 WHERE
(((Table1.NAme) Like """ & Me.Text2.Value & "*" & """));"

Me.List0.Requery

Debug.Print Me.List0.RowSource, Me.Text2.Value

If Not IsNull(Me.Text2.Value) Then

Me.Text2.SelStart = Len(Me.Text2.Value)
Me.Text2.SelLength = 0

End If

End Sub

I'd normally use real names for my controlsa, but this was a fast 'tester'
form only.

I had to use the refresh method else the value of the text box was always
reported as null or whatever the form oppened with. IS htis right or is ther
ea better way to force the form to be aware thatthe text box value has been
changed even though the update event hasn't occurred?

Ta anyway!!

Tim

 

[Ken Snell \(MVP\)]

Code looks ok, except you can delete the requery step for the list box. When
you change the RowSource of a listbox (or a combobox), the control is
automatically requeried.
--

Ken Snell
<MS ACCESS MVP>

 

[Topcat668]

Cheers Ken, most helpful, much obliged!

Code looks ok, except you can delete the requery step for the list box. When
you change the RowSource of a listbox (or a combobox), the control is
automatically requeried.