Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Texas, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query using Form Value (97)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query using Form Value (97)

    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?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Location
    Texas, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using Form Value (97)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query using Form Value (97)

    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.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query using Form Value (97)

    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.
    Regards
    John



  6. #6
    New Lounger
    Join Date
    Feb 2002
    Location
    Texas, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using Form Value (97)

    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:

  7. #7
    New Lounger
    Join Date
    Feb 2002
    Location
    Texas, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using Form Value (97)

    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;

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query using Form Value (97)

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

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using Form Value (97)

    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.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query using Form Value (97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •