Searching Subform Problem 1

[wonderwhy]

I tried to search for a previous answer to this question but the search feature is not working at the moment on this site.

Please help if you can.

I have a form that tracks clinical trials and a subform to choose the physician(s) participating in the trial. The detailed physician information is stored in a separate table and entered into a separate form. The subform on the clinical trials form simply allows the user to choose the related physicians for the main form (it stores only the field "PriInvID" in the Clinical Trials table but users see the physician name in the combo box when choosing related physicians).

Here's the problem. The user would like to be able to search for clinical trial records for a specific physician. You cannot click on the "PriInvID" field in the subform and search by physician name.

What is the best and/or easiest way to provide the searching capability by physician?

Thank you in advance for your help!

Julia

 

[DougP]

"Best", by far, "Easiet" not really, but the results are worth it

See this FAQ
faq702-1177

DougP, MCP

 

[wonderwhy]

DougP,

Thanks for your link to the FAQ but this solution doesn't allow me to have a primary key (which I do - I have many related tables in my database).

Any other ideas?

 

[wonderwhy]

Hello. Does anyone have another idea for my question above?

Thank you!

 

[OnTheFly]

This is how I would do it.
Create a method (i.e. a search command button, menu item, double click event, etc.) that brings up a form that the user can provide the criteria for who they want to find trials for. This form would return to you your key value for the physician (using a combo box that returns the physician ID). You can then use that ID to query your records for a list of clinical trials. Show these in another form with a comand button that is essentially a Go To button. This form would provide you with the primary key value clinical trial record that you can then bring up on the main form.

Kind of a round about way but it works well and is fairly fast since it is all run through code.

Hope this helps.

OnTheFly

 

[wonderwhy]

Thanks, OnTheFly. I used your solution. I added a button the main form that brings up a small form with a combo box and button. The user can choose the physcian/clinical trial from the unbound, multicolumn combo box (the key field is hidden) and the button runs a macro that opens the main form to the matching record chosen in the combo box (by key field). Since this creates a filter, I have also added a button to the top of the form that removes the filter when the user wants to see all of the records.

 

[wonderwhy]

I have a question regarding the Find form that I created (see above). This forms works really well for me. But, it does not work so well for the user that I created it for. We are both using this database which is saved to a shared folder on our company network.

I can use the form to Find records (choosing the record from the combo box and clicking on the FIND button). I can search for one record after another with no problems. But, the user cannot do the same. If she uses the combo box and button to find a record and goes back to the form to look for another record it will not find it. She has to use the "Show All Records" button (mentioned above) between each search.

We have compare all of the Options (under the Tools menu) and all are the same and we are using the same version of Access.

Any ideas????

Thank you!

 

[wonderwhy]

Hello out there... can anyone help?

Thank you.

 

[OnTheFly]

Sorry I did not get back to you right away. I was out of town the last week.

As for the problem, I don't know why one would work while the other does not. Is this a split database application where the program (front-end) portion is local and is attached to the data (back-end) which is shared? If not, there could be issues there if the program is open on both machines at the same time. However, if this was the case you would probably be getting some kind of error.

The only thing I can think of, to avoid the user having to do the extra step of removing the filter, is to code the filter button so that it actually runs the un-filter process first and then immediately runs the code to filter the form.

Hope this helps.

OnTheFly

 

[Rolliee]

Use VB code to do exactly what you want. I understand this to be display a value from a table that is not bound to your form. This is easily done with code. If you want ehe code, ask and I will supply either it or a sample or both.

You may wish to do this on the oncurrent event making your record manipulations faster.

rollie@bwsys.net

 

[ordendelfai]

Hi Rollie,

I am interested in this thread as well. However, I think the round about way mentioned above is most likely inefficient compared to other methods. If you could elaborate a better more efficient way to do this with VBA, that would be great

~Orden

 

[Rolliee]

Using a command button,

1. Open the recordset that contains both the ID and the Doc's name. This could be a combo box. Save the ID

2. Open a form or subform with the same SQL language that includes all the records with the desired ID. Use that as the criteria. Now you can look at all the data that pertains to that ID.

I often use this as a 'details' form - triggered by the main form to show specific data. Closing this form causes a return to the main form to repeat the process with another record.

rollie@bwsys.net