Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Unhappy Unanswered: Keep PK field in subform constant

    Hi,

    In a subform, I have a primary key field that I don't want the user to see, but I have to put it in in order to save a new record. I have placed the field in the header part of the form with other labels and so forth. The subform is in datasheet view. What I would like to happen is that when a new record is being entered the ID will remain the same, instead of having to type it in again for each new record. Is there anyway this could be achieved?


    bajanElf
    "The extreme always make an impression." - Jeff Hardy

  2. #2
    Join Date
    Aug 2002
    Posts
    78

    Re: Keep PK field in subform constant

    Originally posted by bajan_elf
    Hi,

    In a subform, I have a primary key field that I don't want the user to see, but I have to put it in in order to save a new record. I have placed the field in the header part of the form with other labels and so forth. The subform is in datasheet view. What I would like to happen is that when a new record is being entered the ID will remain the same, instead of having to type it in again for each new record. Is there anyway this could be achieved?


    bajanElf
    What is tbl set up of mainform?
    What is tbl set up of Sub form?
    What is the field that relates the two?
    I am assuming this is a one to many relationship.
    can you give example? (As below)

    tblMain
    MAINID (autonumber) PK
    FName (Text)
    MI(text)
    LNAMe(text)


    tblSub
    SubID PK
    Referenceone (text)
    Referencetwo(text)

    Hope this makes sense.

    t

  3. #3
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    Table: Users
    UserID [pk] autonumber
    Name
    Address
    Phone

    Table: UserLoans
    UserID [key] drop down list box, pulling records from the main table
    BDSNo [key] kinda like ISBN#
    DateBorrowed
    DateReturned

    It's a one to many relationship. Users is the main table and main form and UserLoans is the child table and subform.
    "The extreme always make an impression." - Jeff Hardy

  4. #4
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by bajan_elf
    Table: Users
    UserID [pk] autonumber
    Name
    Address
    Phone

    Table: UserLoans
    UserID [key] drop down list box, pulling records from the main table
    BDSNo [key] kinda like ISBN#
    DateBorrowed
    DateReturned

    It's a one to many relationship. Users is the main table and main form and UserLoans is the child table and subform.
    Set your tables up like this

    Table: Users
    UserID [pk] autonumber
    Name
    Address
    Phone

    Table: UserLoans
    UserID (number)
    BDSNo [PK] kinda like ISBN#
    DateBorrowed
    DateReturned

    Set the relationship in Relationships:
    Table Users UserID related to Table UserLoans UserID.
    Run through the Form SubForm Wizard to set it up.
    When you get to the part about the Defining which field links the main form and the sub form. use choose your own option. Link the User ID in main with the User ID in Sub.

    it will create Sub form in datasheet mode.

    Close the form

    Open the sub form created in design mode,
    right click on User ID field, change "Visible" property to no. do the same for the label.

    right click and go to properties for form. Change View to Single Form. I ahve enclosed and example. I am thinking this is what you want. This example is in Access 97. Obviously, I don't know the format of your BDSNo so I just used AutoNumber but I believe this is example of what you wanted.

    Please let me know if not
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2002
    Posts
    78
    OOOPPPSSS I forgot to put the Search Combo box in there however I believe you already know how to do that. If not please let me know.

  6. #6
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    OK, the subform I created is opened by a command button, so it's not on the main form. I added the UserID field and made visible to 'no'. But when I try to add a new record to the subform, I'm still getting the same error of index field cannot be null.
    "The extreme always make an impression." - Jeff Hardy

  7. #7
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    I got it to work

    In the before update event of the BDS# field i added the following code

    Private Sub BDS__BeforeUpdate(Cancel As Integer)

    Forms!subfrmUserLoans!UserID = Forms!frmUsers!UserID

    End Sub

    the UserID is still "invisible", so it's all good.

    Thanks

    bajan_elf
    "The extreme always make an impression." - Jeff Hardy

  8. #8
    Join Date
    Aug 2002
    Posts
    78

    Re: I got it to work

    I personally have not been able to open sub form by command button. someone is helping me with this and I should have answer next week hopefully. The directions I gave is fine so long as subform is in main form normall however. I will keep you posted with what I found out.

  9. #9
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    during the creating of a form using the form wizard, you can choose whether u want the datasheet view or have the subform show i na new window. so that is what i meant by the subform being opened by a command button. i did not want the subform on the main form because of space issuses so i went with the new window option.

    but the code i put in making the forms work now.


    bajan_elf
    "The extreme always make an impression." - Jeff Hardy

Posting Permissions

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