open a form depending on search results

[Geordie2008]

Morning all,

Is the following possible?

I have a form which searches for an employee by name. Currently it opens the "frmMAIN" (which has c. 30 fields) based on the search results and the user has to flip through until they get the correct person.

E.g. - Filter on Smith, M and the user would get:

Smith, Michael
Smith, Michelle
Smith, Mark

I would like to display the results of the search in a tabular form with only the 2 fields showing (First Name, Last Name) and then allow the user to select the correct person, which will then open up the "frmMAIN"...

I have designed the tabular form as I would like it, (using the form wizard). How do I build into this form the functionality to select a particular record? (e.g. Mark Smith) and then I can use this as the filter on "frmMAIN" to open only 1 record...

Thanks,
Mandy

 

[KenHigg]

On the first form I would have a combo box with three columns (fields). The first is hidden and the bound column and would be the primary key field, The second an third would be the last name and the first name. These would be displayed and would be sorted on last name. Then use this as the filter for the second form.

 

[Geordie2008]

Hi Ken,

How would the user select the appropriate name they wish to filter on?

For example if they wanted to select Mark Smith out of the options below...

Smith, Michael
Smith, Michelle
Smith, Mark

Is there a way to have a radio button etc? Or a way to select the row?

I am probably just not understanding your solution properly...

Regards
Mandy

 

[KenHigg]

See post 22 here:

http://www.access-programmers.co.uk/forums/showthread.php?p=687779#post687779

The combo box provides the filter for the list box sql statement, same as you would use it as a criteria in the form query. The key is setting up the combo box row source, column count, col width and bound column properties..

 

[KenHigg]

See post 22 here:

http://www.access-programmers.co.uk/forums/showthread.php?p=687779#post687779

The combo box provides the filter for the list box sql statement, same as you would use it as a criteria in the form query. The key is setting up the combo box row source, column count, col width and bound column properties..

 

[gemma-the-husky]

mandy

in a combo box, you just click the row you want ... ie Smith, Mark

the problem may be that all this will return is Smith, which might then lead you to the wrong Smith ... what you really need is a unique ID in the table, so that when you click Smith, Mark you are REALLY selecting the ID corresponding to Smith, Mark

 

[Geordie2008]

This works really nicely as a combo box..

Only question is..... is it possible to have this as a free text box rather than a combo box?

(I have c.2000 different surnames, so its not userfriendly to provide a drop-down box..)

When I tried to do this, the code wouldn't let me type in it without keep asking me to pass the value through for the list-box...

Other than that, this solution looks perfect for me... Oh and I do have a unique ID on the name, its an autonumber however, I also have a staffID, although this is not always populated. I guess I could get problems if I have 2x Mark smith for example.... I'll have a think how to get around this one.... it may be that I include another field (such as department) to help the user select the correct "Mark Smith".

Many thanks,
Mandy