Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Command button for a filtered form

    Hi everyone I have a question, when I go to insert a command button I use the wizard to open a form to find specific data to display. Now on th form I put the button contains the data for the parent, the form to link it to the child. So if the parent has several children then you can look up the parent the hit the command button that says child and theres all the kids by that parent. this is based off a familyId which was put in place by the original person who created the database. now when I go into design form and press the button I get an error message saying "invalid column name acostajennifer", the primary key consist of firstname and last name together. So if a participants name is John Doe then doejohn is the primary key (I would rather use a numbering system for this), what I'm wondering is would this work if I replaced the primary key with a numbering system instead. the last database I created was able to do this successfully but I used a numbering system not just lettering, so I'm wondering if thats the issue?


    if I could get some insight please
    thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The primary key is mainly used to prevent duplication of that value in the table. Having a primary key on a field which concatenates firstname and lastname could become probematic if say, there are 2 John Doe names which are NOT really duplicates (ie. one lives in Madison, the other lives in Seattle). It wouldn't be my choice for using as a primary key (unless perhaps city/zip was added to the concatenation.)

    It doesn't really matter if the primary key is on a number versus a text field (it depends on how you utilize that number field). For example, I have a 'subjectID' number type field as the primary key in my table where the subjectID must never be duplicated in that table. In another table, the primary key is on a text type field.

    The error you're getting is not due to the primary key being on a text field versus an integer type field. It's most likely due to either the field being renamed, deleted off the form, or you need to debug/compile and compact/repair the mdb. (Did you debug/compile the code after creating your button?)

    What I DO strongly encourage though is having an autonumber type field in your data tables if you don't have one. Only because I'll typically see problems with forms which are often updated and don't have an autonumber field in the table (again, not related to your specific error.)
    Last edited by pkstormy; 04-10-10 at 19:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you pk, I am aware of what the primary key is for. The problem is that I have tried compacting and repairting and thats not working. Also the field has not been changed so I narrowed it down to the primary key cause I couldnt think of anything else. In another database thats based off an INT doesnt have that problem. So thats why I'm asking

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Out of curiosity, does the specific table have an autonumber type field? (again, just out of curiosity.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also, did you debug/compile the code? (different than compact/repair).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by pkstormy View Post
    Out of curiosity, does the specific table have an autonumber type field? (again, just out of curiosity.)
    yes I see an autonumber in it

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by pkstormy View Post
    Also, did you debug/compile the code? (different than compact/repair).
    no, how do you do that??

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In the VBA editor, Debug/Compile.

    You should also post your code. From the sound of the error, it sounds like the value is being mistaken for a field name.
    Paul

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    debugging is not working either

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you miss this?

    Quote Originally Posted by pbaldy View Post
    You should also post your code.
    Paul

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    sorry pdaldy, is this correct??


    Code:
    Private Sub Command346_Click()
    On Error GoTo Err_Command346_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "Student_frm"
        
        stLinkCriteria = "[Family ID]=" & Me![Family ID]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_Command346_Click:
        Exit Sub
    
    Err_Command346_Click:
        MsgBox Err.Description
        Resume Exit_Command346_Click
        
    End Sub

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the family ID is text, try:

    stLinkCriteria = "[Family ID]='" & Me![Family ID] & "'"
    Paul

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Pbaldy thank you so much I have been struggling with this. You are WONDERful. tHANK YOU. Finally I am SO HAPPY!!!!!!!!!!!!!!!

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Ah, I just stepped in when pk was offline. Glad we got it sorted out.
    Paul

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you so much, I have been struggling with this for months. Your Wonderful

Posting Permissions

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