access populate combo box help needed

hi there, i am trying to get the following to occur:

user types in search string hits search button in a form, if there are any records that match the search string a list/combo (which one should i use by the way ) box is displayed and populated with all the matching records. the user then selects which of the records to display using the now visible list/combo box

i need to be able to access partial match records ie:
search string = mi

returns:
michael
mike
amie

etc

the code i have so far makes the text box visible (i'm using a text box because i don't know how to populate a list/combo box) and populates the new box with whatever the user types in the search box .

it's the select statement and populating the list/combo box in my vba script that i need help with.

anyone got any ideas on this

here is the code that i have so far that updates the new box to the search string:

Private Sub cmdwildname_Click()
Dim strwildname As String
Dim strQuery As String
'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtwildname]) Or (Me![txtwildname]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtwildname].SetFocus
Exit Sub
End If
'---------------------------------------------------------------
txtwildname.SetFocus
strwildname = txtwildname.Text
txtwildname.Text = ""
lstwildname.Visible = True

lstwildname.SetFocus
lstwildname.Text = strwildname
End Sub

and here is the code with the select which only ever returns the first record in the database, can someone please tell me how to do the select correctly.

Private Sub cmdwildname_Click()
Dim strwildname As String
Dim strQuery As String
'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtwildname]) Or (Me![txtwildname]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtwildname].SetFocus
Exit Sub
End If
'---------------------------------------------------------------
txtwildname.SetFocus
strwildname = txtwildname.Text
txtwildname.Text = ""
lstwildname.Visible = True

strQuery = "SELECT * FROM books WHERE author='" & strwildname & "';"

lstwildname.SetFocus
lstwildname.Text = authorname
End Sub

Answer this question

access populate combo box help needed

Martin Moe

Hi mate,

To do the query your looking for you need to do two things, you need to use a wild card character...

So instead of searching 'mi' you search for 'mi*', this is saying give me all records that start with mi and contains any number and combinations of characters after mi.

'*mi*' for example searches for all records that contain mi regardless of how many characters are before or after the mi.

The other thing you need to do is instead of using = in your query use LIKE...

For example:

SELECT * FROM [books] WHERE [author] LIKE 'mi*'

Will return the records your looking for.

----

To set the results of this query to a listbox (I'm assuming your using Access) you set the ListBox's 'RowSourceType' property to Table/Query (default value) and the set the 'RowSource' property to the SQL statement.

So for example on your form you have a list box, say myListBox, then to display the results you do this...

myListBox.RowSource = "SELECT * FROM [books] WHERE [author] LIKE 'mi*'"

-----

I hope all that helps, I'm feeling fairly spaced, a classic case of too much work not enough money.
johnnyXNA

Looks like you got the code help you needed! Just wanted to chime in on the issue of which control (listbox/combobox) to use. I'd recommend using a listbox here. The controls serve essentially the same function, but it's important to consider which one will make the most sense to the end user. It's a fine distinction so I'll try to elaborate a bit.

I think a Combobox is the right control to use if you're, for example, letting the user add a new field to a database, and you're asking them to pick what data type that field will contain. In other words, I think it's a control best suited for use when there are only a limited number of choices that could be selected.

If you're presenting a list of multiple possible entries, each of which will then lead to some other action being taken by the program, a Listbox is probably a better choice. Since you're dynamically populating your control with (theoretically) any number of records, the user will be much more comfortable selecting them from a Listbox as opposed to having to open the Combobox dropdown control.

I don't think there's a "right answer", but personally I'd go with a Listbox.
zde
cheers for the help

i was so wrong with putting the database connect in.

as soon as i got rid of that it worked fine.

thanks heaps
access populate combo box help needed

Answer this question