Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    36

    Unanswered: Select Distinct and the New Record Button

    Why is it that when I change my query to a SELECT DISTINCT query, I lose the ability to add new records through my form that is sourced by that query? The New Record button is dimmed, but why?

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    The nature of the beast. The output of a query that uses DISTINCT is not updatable.

  3. #3
    Join Date
    Apr 2003
    Posts
    36
    Originally posted by Rockey
    The nature of the beast. The output of a query that uses DISTINCT is not updatable.
    So, if I have a form which is joining Students and Programs (and thereby showing the same student information more than once if the student is enrolled in more than one program), how can I just have a single instance of the student information come up on the form but still allow the users to enter new students from this form. The reason I'm joining with the Program table is to allow the user to only show (by way of updating the query filter) certain programs' student.

    THanks,

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    What is the structure/design of your tables?

  5. #5
    Join Date
    Apr 2003
    Posts
    36
    Originally posted by Rockey
    What is the structure/design of your tables?
    Hi.

    Still looking for assistance on this. The table structure is:

    tblStudents keyed on SID
    tblStudentPrograms keyed on SID, ProgramID

    I would like to give the users a parameter entry form that allows them to show only students meeting certain parameters (e.g., zip, ethnicity, etc.) Most parameters are attributes of the tblStudents table. However, Program is an attribute of tblStudentPrograms. I want them to be able to say - show me only students in Program X. However, when I do that, I need to join tblStudents and tblStudentPrograms. When I do that, I get multiple rows back in the query if a student is in more than one program. To get around that, I'm doing a 'SELECT DISTINCT'. When I do that, I have the issue that the users can no longer add new students on the form by merely using the new record indicator in the record bar.

    Should I put a command button on the form that allows entry of new students and leave the 'SELECT DISTINCT' in the query? If so, what is the command under the click event of the button.

    If not, are there other options that you can think of to provide this functionality.

    Thanks,

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    take a look at this:
    64.247.21.61/database/Students.zip

    I started this back in May - not sure if this will help. It's not exactly what you are after but I don't have time tonight.
    Email me direct if you can't get it. (I'm busy until next Mon)

    Matt

  7. #7
    Join Date
    Apr 2003
    Posts
    36
    You know how it is. You're waiting for an Access genius (like you) to respond and so you start dinkin' around. I decided to put a command button on my main student form. That button brings up a pop-up window that allows the users to enter attributes. Then when they hit OK, the click event strings together the attributes into a where clause and then reopens the student form with that where clause. That got me around the SELECT DISTINCT issue and I think is close to what you put together - i.e., putting together a where clause based on the parameter entry, vs. using the parameter entry to do a join across to the program table. The where clause for the attributes which are not directly in the student table are done as sub-selects into the appropriate table.

    Anyway, I think that'll work, but if you have any cautions, please let me know. Also, thanks for your help on this.

Posting Permissions

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