Running Queries in a form!!!!

[Kckronic21]

I am having a problem with getting my query to run through this form. I have an Advisor table with these fields: Advisor, Major, Phone Number, Section. The data in this table is displayed like this: Mr. Smith, Computer Technology, 586-3933, O-R. (This is a made up example) I also have a student form that was created by a student query that displays the information about the student. The student query has these fields: SSN, Last Name, First Name, Street Address, City, State, Zip, Phone Number, Major, Term, and Advisor. What I would like to do is after the user puts in the students major in the form, I want to run the Advisor Query so that it just displays the advisors in that particular major. I also wanted to know if there was a way to run a query that puts that correct advisor name in the form for the student after the user puts in the student’s last name and the student’s major in the form.

Thanks

 

[mymou]

Hi Kckronic21


As I think you know, a form can be based on a query. So as long as there is a relationship between [tblStudents] and [tblAdvisors] it should be easy to have student and advisor information on the same form. This should be established by the relationships in the database (or the query on which the form is based on).

EG.
SELECT Students.StudentName, Advisor.AdvisorName …….
FROM Advisor RIGHT JOIN Students ON Advisor.AdvisorID = Students.AdvisorID;

I would recommend that you look carefully at your database design (and the sample database – Northwind) to ensure that your design is standard. The lack of keys in your examples is worrying may lead to problems (or inefficientcies) later on.

To displays just the advisors in a particular major would require a combobox with a recordsource that is restricted by [forms].[form1].[txtmajor] (ie the text box where major is inputted). This will require to be refreshed (on click – requery – or something like that)

Rowsource
SELECT [Advisor].[ID], [Advisor].[number] FROM tblAdvisor WHERE ((([Advisor].[major])=[forms].form1.[txtmajor]));

Hope this is helpful.


Stew
"Make good use of bad rubbish."

 

[jbam]

You could build a "search form" with the appropriate criterian and then build a form based on what the user entered. As for your last question, it is unclear here if "form" means an Access form or an Access report or a hardcopy "form."