Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Command button won't work (XP)

    I've built a form to track students and the self-study courses they've completed in our training labs. The main form displays information about the student (name, payroll ID, etc.); the subform displays the courses the student has completed. In the form footer of the subform, I put a command button that's supposed to allow the user to add a new course completion to the student's record. When I click on the button, however, I get an error message box: "You can't go to the specified record."

    Here's the code behind the command button:

    Private Sub cmdAddCourse_Click()
    On Error GoTo Err_cmdAddCourse_Click

    DoCmd.GoToRecord , , acNewRec

    Exit_cmdAddCourse_Click:
    Exit Sub

    Err_cmdAddCourse_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddCourse_Click

    End Sub

    I don't know whether that's enough information to diagnose the problem. I'll be grateful for any advice.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button won't work (XP)

    Lucas,

    Make sure that you're not already positioned on a new record.

    Can you go to a new record in the subform by using the navigation buttons, or by clicking the New Record button on the toolbar?

    If so, does the command button work if you replace the DoCmd.GoToRecord , , acNew line by RunCommand acCmdRecordsGoToNew ?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Command button won't work (XP)

    Hello, Hans: Matter o' fact, the little button to create a new record is grayed out. Unh...?

    I've got most of my student's personal data in this DB, but only a few have any completed courses in their records. I'm looking right now at one such student record, which has four course completions showing in the subform. Those completions are there because I entered them directly into the table, tblStudentCourses. That table is a linking table that forms a many-to-many relationship between tblStudents and tblCourses. I'm sure I just don't understand enough about this stuff to see what I'm doing wrong.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button won't work (XP)

    Lucas,

    What is the record source of the subform?
    If the record source is a query, open that query in design view, then switch to SQL view. Copy the SQL you see to the clipboard and paste it into a reply.
    If the record source is an SQL string, you can copy it directly, and paste it into a reply.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Command button won't work (XP)

    Record source is a query (qryStudentCourses). Here is the SQL from that query:

    SELECT tblStudentCourses.PID, tblStudents.EmplID, tblStudents.SSN, tblStudents.LastName, tblStudents.FirstName, tblStudents.MI, tblStudents.Nickname, tblCourses.HandoutNbr, tblCourses.CourseTitle, tblCourseDetails.Credit, tblStudentCourses.ClassNbr, tblStudentCourses.DateComplete, tblStudentCourses.LevelID
    FROM tblStudents INNER JOIN ((tblCourseDetails INNER JOIN tblCourses ON tblCourseDetails.HandoutNbr = tblCourses.HandoutNbr) INNER JOIN tblStudentCourses ON tblCourses.CourseKey = tblStudentCourses.CourseID) ON tblStudents.PID = tblStudentCourses.PID
    GROUP BY tblStudentCourses.PID, tblStudents.EmplID, tblStudents.SSN, tblStudents.LastName, tblStudents.FirstName, tblStudents.MI, tblStudents.Nickname, tblCourses.HandoutNbr, tblCourses.CourseTitle, tblCourseDetails.Credit, tblStudentCourses.ClassNbr, tblStudentCourses.DateComplete, tblStudentCourses.LevelID
    ORDER BY tblStudentCourses.PID, tblCourses.CourseTitle;

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button won't work (XP)

    Lucas,

    The reason that you can't edit or add records in the subform is that this query is a Totals query (witness the GROUP BY). Open the query in design view, and you will see that the Totals button is "down" (and that the Totals option in the View menu is checked). Click the Totals button so that it is not "down" any more (and that View | Totals is not checked), then save the query. See if you can enter a new record in the subform now.

    P.S. I don't think you need tblStudents in this query, since all student info is in the main form, you don't need it in the subform.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Command button won't work (XP)

    Hans: Did all that, but I'm still getting the error message ("You can't go to the specified record"). I think I understand what you're saying about not needing the student information in the subform. Do you think if I were to remove that extra stuff this thing'll work?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button won't work (XP)

    Lucas,

    I don't know if removing tblStudents from the query will work, but it's worth a try (keep a copy of the original to be on the safe side). The SQL would look like this (I haven't actually tested it, obviously):

    SELECT tblStudentCourses.PID, tblCourses.HandoutNbr, tblCourses.CourseTitle, tblCourseDetails.Credit, tblStudentCourses.ClassNbr, tblStudentCourses.DateComplete, tblStudentCourses.LevelID
    FROM (tblCourseDetails INNER JOIN tblCourses ON tblCourseDetails.HandoutNbr = tblCourses.HandoutNbr) INNER JOIN tblStudentCourses ON tblCourses.CourseKey = tblStudentCourses.CourseID
    ORDER BY tblStudentCourses.PID, tblCourses.CourseTitle;

    If you open this query directly from the database window, is the New Record button still disabled?

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

    Re: Command button won't work (XP)

    If the user is entering the course using a combobox, you don't need tblcourses in the query either, and removing the inner join should make your query (and subform) updateable. You'll need to insure that the necessary linking keys get populated in tblStudentCourses, but that can easily be done with a bit of code or even with the default value of the controls.
    Charlotte

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Command button won't work (XP)

    Charlotte: Thanks for your advice. Your comments got me to thinking about how there might be other and better ways to tackle this job.

    For several years, we've been using an off-the-shelf application to track our students and courses. We've found that this product doesn't exactly meet our needs, and now we (that is, I--the person who knows a little about Access and wants to know more and is willing to spend hours working on this project!) are trying to build our own custom program. The existing system opens to display a tabbed form. The first tab is "People," where we find the student whose record we want to work with. We double-click the name to open yet another tabbed form. We go to the "Completions" tab to see list of the stuff the student has done. In the footer of that page is the Add... button (and others, such as Delete... and Modify...). The Add... button launches another form that displays all available courses. We double-click the course we want to add to the student's record, check the date and all that, and presto!--the new course gets added to the student's list.

    That's the kind of thing I'm trying to construct. However, if you'll examine the screen shot I attached to my last reply to Hans, you'll see I haven't employed tabs so far. First I want to get my brain around the current problem. But I'll be grateful if you'd look at that screen shot and offer any suggestions for improvement.

    Thank you very much!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button won't work (XP)

    1. For the query to be updateable, the field on the "one" side of each join must be the primary key field, or at least a unique key field, of the table it's in. For example, CourseKey must be the primary key in tblCourses.

    2. I use Paint (or Photo Editor) to save a screen shot as a .png file. This yields a very good quality/compression ratio for the type of screenshots we usually attach to posts (for photographs, .jpg is usually better)

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Command button won't work (XP)

    If you open this query directly from the database window, is the New Record button still disabled?

    Yes, it's disabled. Well...I'm gonna monkey around with the query. I'm sure I'm making this more complicated than it needs to be. Allow me to attach a screen shot of the form as it appears now. That won't tell you anything about the underlying data sources, but perhaps you can suggest a better way to construct this thing.

    Speaking of screen shots, I notice the original file size of this screen shot was 992 kb!! Zipping it brought it down to within the limits for the forum, but I've seen where you and others have actually posted screen shots within a post. How do you do that?

    Thanks for all your help!
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command button won't work (XP)

    Hi Lucas,

    I'm afraid that your screenshot doesn''t tell us much (we already knew that you can't add new recotrds to the subform.) The record source is what determines how the subform behaves.

Posting Permissions

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