Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Not Able to Save Records In Related Forms

Discussion in 'Information Technology' started by ksenya.lim@gmail.com, Jul 28, 2006.

  1. ksenya.lim@gmail.com

    ksenya.lim@gmail.com
    Expand Collapse
    Guest

    Hey,
    I have this problem: I created a few forms based on several related
    tables. It is possible to open all these forms from the main one using
    a criteria: DoCmd.OpenForm, stDocName,,,stLinkCriteria. All the forms
    should display a record that corresponds the primary key value typed in
    the main form. When a user adds new data ro a record it is either can't
    be saved ("..would create duplicate values in the index, primary key or
    realationship") or it can be saved only in the tables but doesn't
    appear in the forms. Thank you very much for your help!
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Hard Talk When Our Quirks Define Us (A Parable Revisited) Hard Talk Mar 12, 2016
    Memorial to the remarkable Sikh soldiers of WWI Sikh Personalities Oct 31, 2015
    India Chilling & Unwatchable: Film Documentary "india’s Daughter" Breaking News Mar 6, 2015
    Movies Ashdoc's short review---Expendables 3 Theatre, Movies & Cinema Aug 29, 2014

  3. BruceM

    BruceM
    Expand Collapse
    Guest

    How is stLinkCriteria defined? What are the tables (brief description and
    key fields), and how are they related?

    <ksenya.lim@gmail.com> wrote in message
    news:1152109846.903355.144730@m73g2000cwd.googlegroups.com...
    > Hey,
    > I have this problem: I created a few forms based on several related
    > tables. It is possible to open all these forms from the main one using
    > a criteria: DoCmd.OpenForm, stDocName,,,stLinkCriteria. All the forms
    > should display a record that corresponds the primary key value typed in
    > the main form. When a user adds new data ro a record it is either can't
    > be saved ("..would create duplicate values in the index, primary key or
    > realationship") or it can be saved only in the tables but doesn't
    > appear in the forms. Thank you very much for your help!
    >
     
  4. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    It sounds like the foreign key is not getting properly populated. In a
    form/subform arrangement, Access automatically populates the foreign key in
    the subform as long as the master/child links are properly set. You don't
    have that option with popup forms. You need to populate the foreign key
    yourself. Just add a single line of code to the popup form's BeforeInsert
    event:
    Me.somefieldname = Forms!originalform!somefieldname

    I suggest the BeforeInsert event because that event doesn't fire until the
    user actually begins typing in the form. This prevents you from dirtying
    the record and avoids confusion if the user elects to close the form without
    actually adding a record.
    <ksenya.lim@gmail.com> wrote in message
    news:1152109846.903355.144730@m73g2000cwd.googlegroups.com...
    > Hey,
    > I have this problem: I created a few forms based on several related
    > tables. It is possible to open all these forms from the main one using
    > a criteria: DoCmd.OpenForm, stDocName,,,stLinkCriteria. All the forms
    > should display a record that corresponds the primary key value typed in
    > the main form. When a user adds new data ro a record it is either can't
    > be saved ("..would create duplicate values in the index, primary key or
    > realationship") or it can be saved only in the tables but doesn't
    > appear in the forms. Thank you very much for your help!
    >
     
  5. ksenya.lim@gmail.com

    ksenya.lim@gmail.com
    Expand Collapse
    Guest

    The forms in my case are based on tables which have one-to-one and
    one-to-many relationships and also some forms that are based on the
    same table. With forms that have one-to-many relationship everything is
    ok - records can be saved and data appears in the text or combo boxes
    after a user reopens a form. I encounter this problem only with the
    forms that have one-to-one relationship or with forms based on the same
    table. The primary key is DossierNo. The code is:
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Employment"

    stLinkCriteria = "[Dossier No]=" & Me![Dossier No]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit

    Thank you very much!



    Pat Hartman(MVP) wrote:
    > It sounds like the foreign key is not getting properly populated. In a
    > form/subform arrangement, Access automatically populates the foreign key in
    > the subform as long as the master/child links are properly set. You don't
    > have that option with popup forms. You need to populate the foreign key
    > yourself. Just add a single line of code to the popup form's BeforeInsert
    > event:
    > Me.somefieldname = Forms!originalform!somefieldname
    >
    > I suggest the BeforeInsert event because that event doesn't fire until the
    > user actually begins typing in the form. This prevents you from dirtying
    > the record and avoids confusion if the user elects to close the form without
    > actually adding a record.
    > <ksenya.lim@gmail.com> wrote in message
    > news:1152109846.903355.144730@m73g2000cwd.googlegroups.com...
    > > Hey,
    > > I have this problem: I created a few forms based on several related
    > > tables. It is possible to open all these forms from the main one using
    > > a criteria: DoCmd.OpenForm, stDocName,,,stLinkCriteria. All the forms
    > > should display a record that corresponds the primary key value typed in
    > > the main form. When a user adds new data ro a record it is either can't
    > > be saved ("..would create duplicate values in the index, primary key or
    > > realationship") or it can be saved only in the tables but doesn't
    > > appear in the forms. Thank you very much for your help!
    > >
     
  6. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    Did you put the code that I suggested into the BeforeInsert event of the
    "Employment" form?
    It doesn't matter how the tables are related.

    <ksenya.lim@gmail.com> wrote in message
    news:1152165467.125733.199830@l70g2000cwa.googlegroups.com...
    > The forms in my case are based on tables which have one-to-one and
    > one-to-many relationships and also some forms that are based on the
    > same table. With forms that have one-to-many relationship everything is
    > ok - records can be saved and data appears in the text or combo boxes
    > after a user reopens a form. I encounter this problem only with the
    > forms that have one-to-one relationship or with forms based on the same
    > table. The primary key is DossierNo. The code is:
    > Dim stDocName As String
    > Dim stLinkCriteria As String
    >
    > stDocName = "Employment"
    >
    > stLinkCriteria = "[Dossier No]=" & Me![Dossier No]
    > DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
    >
    > Thank you very much!
    >
    >
    >
    > Pat Hartman(MVP) wrote:
    >> It sounds like the foreign key is not getting properly populated. In a
    >> form/subform arrangement, Access automatically populates the foreign key
    >> in
    >> the subform as long as the master/child links are properly set. You
    >> don't
    >> have that option with popup forms. You need to populate the foreign key
    >> yourself. Just add a single line of code to the popup form's
    >> BeforeInsert
    >> event:
    >> Me.somefieldname = Forms!originalform!somefieldname
    >>
    >> I suggest the BeforeInsert event because that event doesn't fire until
    >> the
    >> user actually begins typing in the form. This prevents you from dirtying
    >> the record and avoids confusion if the user elects to close the form
    >> without
    >> actually adding a record.
    >> <ksenya.lim@gmail.com> wrote in message
    >> news:1152109846.903355.144730@m73g2000cwd.googlegroups.com...
    >> > Hey,
    >> > I have this problem: I created a few forms based on several related
    >> > tables. It is possible to open all these forms from the main one using
    >> > a criteria: DoCmd.OpenForm, stDocName,,,stLinkCriteria. All the forms
    >> > should display a record that corresponds the primary key value typed in
    >> > the main form. When a user adds new data ro a record it is either can't
    >> > be saved ("..would create duplicate values in the index, primary key or
    >> > realationship") or it can be saved only in the tables but doesn't
    >> > appear in the forms. Thank you very much for your help!
    >> >

    >
     
  7. ksenya.lim@gmail.com

    ksenya.lim@gmail.com
    Expand Collapse
    Guest

    Yes, thank you for your help! It worked!

    Pat Hartman(MVP) wrote:
    > Did you put the code that I suggested into the BeforeInsert event of the
    > "Employment" form?
    > It doesn't matter how the tables are related.
    >
    > <ksenya.lim@gmail.com> wrote in message
    > news:1152165467.125733.199830@l70g2000cwa.googlegroups.com...
    > > The forms in my case are based on tables which have one-to-one and
    > > one-to-many relationships and also some forms that are based on the
    > > same table. With forms that have one-to-many relationship everything is
    > > ok - records can be saved and data appears in the text or combo boxes
    > > after a user reopens a form. I encounter this problem only with the
    > > forms that have one-to-one relationship or with forms based on the same
    > > table. The primary key is DossierNo. The code is:
    > > Dim stDocName As String
    > > Dim stLinkCriteria As String
    > >
    > > stDocName = "Employment"
    > >
    > > stLinkCriteria = "[Dossier No]=" & Me![Dossier No]
    > > DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
    > >
    > > Thank you very much!
    > >
    > >
    > >
    > > Pat Hartman(MVP) wrote:
    > >> It sounds like the foreign key is not getting properly populated. In a
    > >> form/subform arrangement, Access automatically populates the foreign key
    > >> in
    > >> the subform as long as the master/child links are properly set. You
    > >> don't
    > >> have that option with popup forms. You need to populate the foreign key
    > >> yourself. Just add a single line of code to the popup form's
    > >> BeforeInsert
    > >> event:
    > >> Me.somefieldname = Forms!originalform!somefieldname
    > >>
    > >> I suggest the BeforeInsert event because that event doesn't fire until
    > >> the
    > >> user actually begins typing in the form. This prevents you from dirtying
    > >> the record and avoids confusion if the user elects to close the form
    > >> without
    > >> actually adding a record.
    > >> <ksenya.lim@gmail.com> wrote in message
    > >> news:1152109846.903355.144730@m73g2000cwd.googlegroups.com...
    > >> > Hey,
    > >> > I have this problem: I created a few forms based on several related
    > >> > tables. It is possible to open all these forms from the main one using
    > >> > a criteria: DoCmd.OpenForm, stDocName,,,stLinkCriteria. All the forms
    > >> > should display a record that corresponds the primary key value typed in
    > >> > the main form. When a user adds new data ro a record it is either can't
    > >> > be saved ("..would create duplicate values in the index, primary key or
    > >> > realationship") or it can be saved only in the tables but doesn't
    > >> > appear in the forms. Thank you very much for your help!
    > >> >

    > >
     

Share This Page