Search by Name from Combo Box Help

[DawsonUK]

Hi,

I was wondering if the following was possible, and if anyone could help on it.

Basically, my database holds details of nurses, where on the search screen users can search by course, specialism, status, personal tutor, and name. In the name box, users can only select items in the list, and when they press a go button it jumps to their record in a new form. What I was wondering was, could it be possible for this box to be used to search for items not in the list? For example, if a user enters "Dawson" and presses Go, because its not in the list, opens search results from a query.

The combo box has four fields, ID, Name, Course, Specialism
The ID field is not viewable, but is bound to the combo box.

Do you think something like this would be feasable? It could save so much grief for me!

David

 

[vix666]

I think you need to do a query and get the query to activate when you click a button. In the query window select the fields you want to be displayed and then for example under the field Name enter this into the criteria box

[forms]![frmname]![cbo_Name]

this means the query will take the value from the combo box. If your combo box property of limit to list is set to no then a user will be able to enter other things into it.

Hope this helps

Vicky

 

[DawsonUK]

Hi, thanks for the quick reply, but this doesnt help my problem.

The problem is, the combo box contains say 3000 names. Instead of the staff scrolling through them, they want to be able to type in "Smith" and click go to view all Smiths. Even though the Go button is set to open the View Details form of the current selected person from the combo.

Is there a way in code to determine whether a user entered value is in the list or not?

Thanks,
David

 

[vix666]

Hmm would you be able to just have a txt box where they enter the name they are looking for instead of scrolling through that list as the query would pick up anyone with the surname Dawson?

As for coding a way to check whether it is in the list or not it can be done, but im unsure how - sorry

 

[hymn]

Couldn't you add another field to the search screen and add additional code for that field?

Hope this helps
Hymn

 

[DawsonUK]

I would, but its quite full aready, and I'm not too keen on having two "Search By Name" fields. Surely if must be possible to have some sort of a If statement alon the lines of...

If Me![name] is an item in list
Then open view details form
Else
Open search search results

Any ideas?

Thanks, David

 

[TonyJollans]

Hi David,

You must already have the Limit To List property set to No for Users to be able enter names not in the list. Can you not use the NotInList event to do whatever you need to when they do this?

Enjoy,
Tony

 

[DawsonUK]

Hi Tony, thanks for the reply.

Yes, this would allow me to enter a name and search for it, which is what I'm after, but what the combo box does right now is go straight to that record when the Go button is pressed. I'll show you a cut down version of the combo box....

ID Name
1 Bob
2 Dave
3 Jim

Now, the ID is hidden. But when a user selects say Dave, the entry is bound to the ID. So when I use the following code....

Dim condition, formname as String
formname = "Edit Details"
condition = "Me![ID] = Forms![Search form]![name]"
DoCmd.OpenForm formname, , , condition

This jumps to the selected record. I want to combo box to continue doing this, but if the user enters a Value not in the list, it opens a search results box. I understand how to make the search box, and how this one works, but I'm unsure how to combine them together.

Ie, a user can click on Dave and view his record.
Or search for "Da" and a new form would show all results, eg Dave, David, Dawson etc.

What I'm trying to figure out is a piece of code which can determine whether the entered item is in the combo box or not. If it is, it opens the record, if not, opens search results.

I hope this clarifies what I'm trying to do a bit better.

Thanks for all the replies so far,

David

 

[TonyJollans]

I think this will do what you want ..

Make Name the Bound Column.
Set your column Widths to 0,5 or something similar which makes Name visible
Set the combo Limit To List property to No.
In your button code put something along these lines ..

If IsNull(Me.Combo.Column(0)) Then

    ' Not in List
    ' So check if blank

    If IsNull(Me.Combo.Column(1)) Then

        ' No input
        ' Issue error message

    Else

        ' Name not in list
        ' Do your search stuff

    End if
Else

    ' Valid name input
    ' Do what you did before

End If

Enjoy,
Tony