look-up field (1 Viewer)

[michael albert]

How can I create a look-up field which allows you to select more than one entry, i.e. I want to create a field which looks-up data in an employee table and which often will require several employees to be selected, the field will be employees assigned to a project, thus it will have multiple entries

Thanks
Mike


[This message has been edited by michael albert (edited 01-07-2000).]

 

[R. Hicks]

Michael,
Check your e-mail. I sent you a small database where you can filter by your form entries. Maybe you can adapt this example to do what you need.

Good Luck
RDH

 

[Travis]

What I normally do in this situation is use a list box, I set the Multi Select Property to Extended (This allows for the use of Shift and Ctrl like the windows explorer).

Then on the Sub running the search I use code similar to this:

Public Function IsSelected(frm As Form, ctl As Control) As String
Dim varItm As Variant
Dim stSQL As String
Dim stWhere As String

stSQL = "Select * from [tblTable1] "

For Each varItm In ctl.ItemsSelected
stWhere = "[Employee]='" & ctl.ItemData(varItm) & "' or "
Next varItm
stWhere = Left(stWhere, Len(stWhere) - 4) 'Removes last or
If Len(stWhere) > 0 Then stWhere = "Where " & stWhere
IsSelected = stSQL & stWhere

End Function

This function will return the SQL needed to query the data I'm searching for. Basically it will iterate through all of the rows in the listbox searching for items selected. This method works great when searching on a single field. Another advantage comes with using columns. Say you want to show the name but you want to search on the EmployeeID, you can have the EmployeeID number Hidden but use it instead by just change this line in the code above:

ctl.ItemData(varItm)

to this

ctl.ctl.Column(0, varItm) 'First Column (columns are by ordinal number)

Hope this helps.

 

[gilescis]

R. Hicks
Can you send me that same example to dcastaldo@skylandfire.com

Thanks