Query using Form Value (97)

Topic

New Reply

I have a form to enter student information; in that form, I have a combo box to list the parents name. I want to use the student’s last name as a query criteria for the combo box. However, its query result is empty. Here’s my query:

SELECT DISTINCTROW GUARDIANS.GUARDIAN_ID, GUARDIANS.LAST_NAME & “,” & GUARDIANS.FIRST_NAME & ” ” & GUARDIANS.MIDDLE_NAME AS Expr1
FROM GUARDIANS, students
WHERE (((GUARDIANS.LAST_NAME)=forms![frmStudents]![Last_name]));

Can someone tell me what’s wrong with the query. Thanks.

Reply | Quote

Replies

You don’t need Students in there, so take it out. It’s giving you a cartesian product even though you aren’t using any fields from that table.

What do you mean, its query result is empty? Are you talking about the combobox being empty or the query not returning records if you run it from the database window?

Reply | Quote

I took out the students in the query, that was left over from before I tried to use the last name from the form.
The combo box came back empty, but if I ran it from SQL window, it prompts for the last name and after I enter a last name, it comes back with a list.

Reply | Quote

What do you mean by empty? Is the dropdown list empty or just the textbox portion before you make a selection? If you’re seeing a dialog pop up, it means that Access can’t resolve the form reference you’re trying to use. Either you misspelled the form or control name or one or both don’t exist.

Reply | Quote

When you ran it from the sql window was the form (with the last name on it) open in the background?

It needs to be. If it was open, go to design view, and use the expression builder to recreate the reference to the form field. Using the expression builder guarantees that you spell the field and form names correctly.

Reply | Quote

When I have the form opened and run the query, I got the run correct result. However, the combo box where I put this query as Row Source returns with an empty list. Here’s the exact query I use:

Reply | Quote

Here’s the query again:
SELECT DISTINCTROW GUARDIANS.GUARDIAN_ID, GUARDIANS.LAST_NAME & “,” & GUARDIANS.FIRST_NAME & ” ” & GUARDIANS.MIDDLE_NAME AS Expr1
FROM GUARDIANS
WHERE (((GUARDIANS.SPOUSE_LAST_NAME)=forms![frmStudents]![Last_name]))
ORDER BY GUARDIANS.LAST_NAME, GUARDIANS.FIRST_NAME;

Reply | Quote

SPOUSE_LAST_NAME? Where did that field come from? I thought you were trying to compare lastnames? Is SPOUSE_LAST_NAME always populated?

Reply | Quote

I think if you change the spouse’s last name to guardian’s last name, the query will work for you as listed below:
SELECT DISTINCTROW GUARDIANS.GUARDIAN_ID, GUARDIANS.LAST_NAME & “,” & GUARDIANS.FIRST_NAME & ” ” & GUARDIANS.MIDDLE_NAME AS Expr1
FROM GUARDIANS
WHERE (((GUARDIANS.LAST_NAME)=forms![frmStudents]![Last_name]))
ORDER BY GUARDIANS.LAST_NAME, GUARDIANS.FIRST_NAME;
Also, once you select the student’s last name, you may have to requery the guardian’s drop-down box. For instance, when you open the form, the student’s last name is empty therefore, the guardian’s last name will be based on an empty box. If you set the after update event of the student’s last name to:
Me.NameOfTheGuardianLNameBox.Requery
This will cause the guradian last name to requery to the new entry in the student’s last name.

Reply | Quote

What Andrew said about the Requery should do the trick.
Pat

Reply | Quote