Hi,

I have a combo box with the next sql:

SELECT tblVisitorsBezoekers.BezoekerID, tblVisitorsBezoekers.BezoekerNaam AS [Naam bezoeker], tblVisitorsBedrijven.BedrijfNaam AS [Bedrijf bezoeker] FROM tblVisitorsBedrijven INNER JOIN tblVisitorsBezoekers ON tblVisitorsBedrijven.BedrijfID = tblVisitorsBezoekers.BezoekerBedrijf ORDER BY tblVisitorsBezoekers.BezoekerNaam, tblVisitorsBedrijven.BedrijfNaam;

Imagin the values in the combo looks like this:

Achak
Ahrou
Achraf
...

If I type 'a' in the combo the list should have all these names
If I type 'ac' it should return 'Achak' and 'Achraf' but NOT 'Ahrou'
If I type 'ro' it should only return 'Ahrou'
...

So basically I would like that what I typed is a 'like *WhatIType*' in the sql criteria and this updates on every character that is added or removed.
The list in the combo box should always remain 'dropped down' when any character has been typed. On a null value or empty string the list should be 'closed'.

Don't know if this is clear and if this can be done...

I am not sure what you mean when you say: "I would like that what I typed is a 'like *WhatIType*' in the sql criteria and this updates on every character that is added or removed."

DO you mean that if the user types "ac", that regardless of what is showing in the combo box, the "ac" is passed to the SQL string as in: "LIKE '*ac*'"?

If so, you will not get this from a combo box, since it is pulling specific records based on the BezoekerID field.

I would suggest a text box if you want to do this sort of search.

For the drop down effect, do this:

Private Sub YourCombo_KeyDown(KeyCode As Integer, Shift As Integer)
Me.YourCombo.Dropdown
End Sub

Did that help?


mmcdonal

Oh, also, I don't think you can get your combo box to respond to "ro" since you would need to type "ahro" to show that value. Again, use a text box here and an intermediate form like a continuous form. How many records are we talking about that would have "ro" in the Naam bezoeker?

mmcdonal

I don't really know how many would have 'ro' (or whatever I type) in the list, because the list will evolve, and this could be really quickly...

So yes, what I meant was that if I type something like 'ro' in the text box you are suggesting, than in the criteria of the sql it should 'add' something like:
WHERE NameOfField = like *ro*


Why I want this is that it would make it quicker to look someone up if you don't know the complete name anymore. If you only know the last part, you would have to go through the whole combo box values to find the right one (if it is the last one off course). So basically it should be like a filter on the values of tblVisitorsBezoekers.BezoekerNaam, but I still need the whole sql like I have put it earlier, because I need the primary key of tblVisitorsBezoekers.BezoekerID once a person is selected in the list...

Don't know if what I want is clear now?

Yes. The way I do this is to create a button to open a continuous form that is based on a query on the fields I want the user to search, then in the criteria line of the field I want them to have a text search for I enter something like this:

Like [Type the first few letters of the last name of the person whose login name you want:] & "*"

You would do:

Like "*" & [Type etc] & "*"

The button launches the form, and the form launches the query, and the query opens an input box to get the criteria.

Then on the continous form, I put a button next to each record to open a data entry form "WHERE [PKID] = " & Me.PKID (except I take the PK as a variable so it would be: "[PKID] = " & iPK

Does that help?


mmcdonal

I'm not sure we understand each other here, so I made some pictures so you can see what I mean...

Image1, combo is empty:
http://img515.imageshack.us/img515/5237/cboemptyvd9.jpg

Image2, combo has 1 letter typed:
http://img515.imageshack.us/img515/8...oinput1sy0.jpg

Image3, combo has 2 letters typed:
http://img369.imageshack.us/img369/6...oinput2lj5.jpg

Image4, combo has 3 letters typed:
http://img369.imageshack.us/img369/1...oinput3vj5.jpg

I hope this make more sense to you with the pictures. This is really working on a program I have at work and would like to know if it can be done in access 2002...

When typing something it kind of 'filters' the data in the combo box on the first column. This way you can find anything quickly even if you only know part of the name. Here I took the beginning of the name as an exemple, but if I would have taken the middle part of the name, it works too...

Do you think this can be done???

Just a little thing I forgot, on this exemple in that program, when I start typing, the combo opens by itself and then stays open and is 'filtering' the data with each lettre I add. It closes if I empty the combo box or if I select a value form the list...

It would be great if I could get it to react like that.

I am trying to work out a solution with a text box and a list box underneath, not really a combo box.

I can get the list box to populate itself on the On Load event, and I can pass a string to it froma variable that I set, like the letter "i" and it works to populate the list box based on your search criteria, like this:

Form On Load event:
Private Sub Form_Load()

sSQL = "SELECT AssetID, AssetDecription FROM tblAsset ORDER BY AssetDescription"

[Forms]![Form1].[MyListBox].RowSource = sSQL
End Sub

This populates the list box when the form opens.

Then if I do this it limits the list box to items with the letter "i" in it:

Dim sString As String

sString = "i"

sSQL = "SELECT AssetID, AssetDescription FROM tblAsset WHERE [AssetDescription] Like '" & "*" & sString & "*" & "' ORDER BY AssetDescription"

[Forms]![Form1].[MyListBox].RowSource = sSQL

The problem is, I cannot figure out how to take the value from a text bax and pass it to the string like this:

sString = Me.MyTextValue.Value

The problem is I cannot find an event that this will work on. I added Me.MyListBox.Requery and that didn't help.

I used On Change, On Dirty and On KeyDown. Can you figure this part out?



mmcdonal

I'll start with the thing you said and I'll try to complete the rest. If I find the solution I'll lett you know...

Thanks for your help so far...