Search forms

[chris17]

Please how can I use a form with two text box to open another form using the criteria in th text boxes.

[Rod]

You will need to resort to some VBA coding. If you are unfamiliar with VBA and event procedures then post back and we will help you.

[chris17]

Yes dear, I ain't familiar with VBA. I need two text boxes to search and pull a specified data on a form.

[Rod]

Hold for a while - it's late here. I'll get back to you tomorrow - my tomorrow.

[chris17]

Copy that. Thank you very much.

[chris17]

Pls Rod, while getting back to me, Please i have a report that i want to filter failed courses but i want the failed courses to be hidden when passed by the student. I dont want the course failed to removed because it ll be used some other place but want to hide it in the failed course report if passed. Thanks as i expect your help.This is an illustration for more understanding.

[Rod]

I'll look into your filtered report asap. Meanwhile I need some clarification about the form opening.

1. Do you wish to open the same form with different information depending upon the values in the listboxes, or
2. Open a different form depending upon the listbox values?

Moreover should this opened form:

1. Be a separate form opened on top of the form containing the listboxes, or
2. Be shown in a pane of the form containing the listboxes, i.e. a subform?

[Rod]

Concerning the report I can think of no simple filter. Rather the report needs to be based upon two queries, one for passed courses and one for failures. These two queries may be amalgamated through what is termed a UNION. Furthermore the failures may be restricted to only those courses not in the result set for passes. It is this failure query that is the one to work on first. It will be a NESTED query with something like the following syntax.

SELECT * FROM tblResults WHERE Score = "F" AND [Course_Code] NOT IN(
SELECT [Course_Code] FROM tblResults WHERE Score <> "F")

I assume that "F" is the only fail mark; if not then the WHERE clauses will be a little different. Use your own names. I've included brackets for the hyphenated name to be on the safe side; I never use hyphens.

Once this is working for you, we'll replace the asterisk with field (attribute) names as these make for more clarity with a UNION query.

PS Just thought of a complication. I assume this is to be done per student. My syntax will not cope with this as written. Anyway get the above working and we'll refine it.

[chris17]

Thanks dear. How was ur night? I am taking the option of opening a different form with its content depending on the list boxes.

[chris17]

ROD am very grateful for the repor, u saved me a lot today. I ve bee working on the report for 3 months and now u did put me thru. Thank You very much

[chris17]

Its on different form

[chris17]

Hi Rod, do you still have any solution for me on the search form.

[Rod]

You're still being very brief in your answers. This leaves me with too many options. However I shall assume there is one other separate form and the content of the listboxes determines the content of this other form.

There are two ways to open a form in Access. The one that is learned first off is to use the OpenForm method of the DoCmd object (or the OpenForm macro). (In fact if you want a custom dialog/message/input form this is the only methos you can use.) The other way is to instantiate a new form object from its class. Once learned this is a much easier/more elegant way to code especially since you get the full help from Intellisense. It is also the only way to get multiple copies of the same form.

OK, I shall base the remainder of this post on the OpenForm method.

Let's assume there is a command button on your original form and when you click on that button the new form is opened and populated. In the Click event of the button I would expect to see something like the following.

If IsNull(Me.lstOne) Then Exit Sub
If IsNull(Me.lstTwo) Then Exit Sub
DoCmd.OpenForm "MyNewForm"
Forms("MyNewForm").MyControl = "xyz"

The above is very basic. You may include filters, WHERE clauses, OpenArgs, etc. in the OpenForm method to control how the form opens and what it shows. The final line above illustrates how to poke a value into a control on the form once it is opened.

Is it possible that the form is already open? If so what should happen?