Welcome to SPN

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

Sign Up Now!

Retrieving auto number value immediately

Discussion in 'Information Technology' started by millie.patel@gmail.com, Jul 28, 2006.

  1. millie.patel@gmail.com

    millie.patel@gmail.com
    Expand Collapse
    Guest

    Hi,

    I am working on a form right now.
    I have three tables:
    tbl_projects
    --projectID
    --projectname
    --projectdescription
    --projectmanager
    tbl_products
    --productID
    --productname
    --productshortname
    --productcategory
    tbl_projectproducts
    --projectID
    --productID

    The form is used to enter a project, it has multiple fields (i.e.
    project name, project manager, project description).

    Only when the project is entered into the database, can I retrieve the
    autonumber (primary key) to use to insert values into the
    projectproducts table.

    Is there a way to retrieve the value on the same form? so that i can
    enter the product info right away, besides going to a new page?

    thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Is Autocomplete Evil? Some Women Take A Hard Look at Google ! Breaking News Nov 7, 2013
    Sikh News AutoZone sued for firing Everett Sikh Breaking News Sep 29, 2010
    India Singh: Centre Ready To Give Kashmir Autonomy Breaking News Aug 11, 2010
    India Paste tariff cards behind driver’s seat: Transport dept to automen Breaking News Jul 21, 2010
    Gursharan Kaur: India's First Lady's Autobiography Sikh Personalities Jun 1, 2010

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Millie,

    you can force a save like this:

    if me.dirty then me.dirty = false

    if you are using the AfterUpdate event of a control (perhaps
    projectname), it is obviously dirty, so you can do this:

    me.dirty = false
    msgbox me.id_controlname

    even though you may have more to fill out, you can get the
    ID right away (as long as you don't have other fields that
    need to be filled before the record saves)

    just out of curiosity, why do you need the ID? Why not have
    tbl_projectproducts be the recordsource for a subform with
    projectID in LinkMasterFields and LinkChildFields?

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    millie.patel@gmail.com wrote:
    > Hi,
    >
    > I am working on a form right now.
    > I have three tables:
    > tbl_projects
    > --projectID
    > --projectname
    > --projectdescription
    > --projectmanager
    > tbl_products
    > --productID
    > --productname
    > --productshortname
    > --productcategory
    > tbl_projectproducts
    > --projectID
    > --productID
    >
    > The form is used to enter a project, it has multiple fields (i.e.
    > project name, project manager, project description).
    >
    > Only when the project is entered into the database, can I retrieve the
    > autonumber (primary key) to use to insert values into the
    > projectproducts table.
    >
    > Is there a way to retrieve the value on the same form? so that i can
    > enter the product info right away, besides going to a new page?
    >
    > thanks!
    >
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Millie

    The autonumber value should be created and assigned the moment you begin to
    create a new record. You don't need to wait until it is saved. The earliest
    you can get it then is the form's BeforeInsert event.

    The most common way to handle a setup like this is with a form and subform.
    The form is bound to tbl_projects and the subform to tbl_projectproducts.
    The subform should be continuous and needs to contain only a combo box bound
    to productID, with its RowSource based on tbl_products. Set both the
    LinkMasterFields and LinkChildFields for your subform control to projectID.

    Then you can add a new project and select associated products, all from the
    one form.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand


    <millie.patel@gmail.com> wrote in message
    news:1149538886.072984.248900@h76g2000cwa.googlegroups.com...
    > Hi,
    >
    > I am working on a form right now.
    > I have three tables:
    > tbl_projects
    > --projectID
    > --projectname
    > --projectdescription
    > --projectmanager
    > tbl_products
    > --productID
    > --productname
    > --productshortname
    > --productcategory
    > tbl_projectproducts
    > --projectID
    > --productID
    >
    > The form is used to enter a project, it has multiple fields (i.e.
    > project name, project manager, project description).
    >
    > Only when the project is entered into the database, can I retrieve the
    > autonumber (primary key) to use to insert values into the
    > projectproducts table.
    >
    > Is there a way to retrieve the value on the same form? so that i can
    > enter the product info right away, besides going to a new page?
    >
    > thanks!
    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    In my version, the autonumber value is not available on the
    BeforeInsert*, but it is on the BeforeUpdate event...it is
    also available on the first control's BeforeUpdate and, of
    course, AfterUpdate events

    * I thought it would be since, in code, you can get it as
    soon as you do .AddNew, but I tried that before I posted
    previously and it doesn't apparently work the same way with
    the form event -- kinda makes sense though, since it is
    before the record is inserted

    '~~~~~~

    creating a continuous form with checkboxes to pick products

    To add on to what Graham said about the form setup.
    Handling the records in a continuous subform is, of course,
    the way to go. Another thing I often like to do is
    something like this:

    subform RecordSource:
    SELECT pp.projectID, pp.productID,
    p.productname, p.productshortname,
    p.productcategory,
    IIF(isnull(pp.productID),false,true) as chkUsed
    FROM tbl_projectproducts as pp
    RIGHT JOIN tbl_products as p
    ON pp.productID = p.productID
    WHERE pp.projectID = forms!mainform!projectID
    OR pp.projectID Is Null

    chkUsed will be a checkbox at the beginning of each row.
    Every product will be listed and the ones that are actually
    records in tbl_projectproducts will be checked.

    The form itself will not be updateable, but you can use the
    click event of the checkbox to append or delete records from
    tbl_projectproducts and then requery the form

    I am not quite sure about the SQL since I didn't test it --
    you may need to make a couple of adjustments


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Graham Mandeno wrote:
    > Hi Millie
    >
    > The autonumber value should be created and assigned the moment you begin to
    > create a new record. You don't need to wait until it is saved. The earliest
    > you can get it then is the form's BeforeInsert event.
    >
    > The most common way to handle a setup like this is with a form and subform.
    > The form is bound to tbl_projects and the subform to tbl_projectproducts.
    > The subform should be continuous and needs to contain only a combo box bound
    > to productID, with its RowSource based on tbl_products. Set both the
    > LinkMasterFields and LinkChildFields for your subform control to projectID.
    >
    > Then you can add a new project and select associated products, all from the
    > one form.
     
  6. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Crystal

    > chkUsed will be a checkbox at the beginning of each row. Every product
    > will be listed and the ones that are actually records in
    > tbl_projectproducts will be checked.
    >
    > The form itself will not be updateable, but you can use the click event of
    > the checkbox to append or delete records from tbl_projectproducts and then
    > requery the form


    This is precisely what I've been doing since Access 2.0.

    Who needs "complex data" eh? :)

    Cheers,
    Graham
     
  7. millie.patel@gmail.com

    millie.patel@gmail.com
    Expand Collapse
    Guest

    thanks all! all your messages were very very helpful!

    :)

    Graham Mandeno wrote:
    > Hi Crystal
    >
    > > chkUsed will be a checkbox at the beginning of each row. Every product
    > > will be listed and the ones that are actually records in
    > > tbl_projectproducts will be checked.
    > >
    > > The form itself will not be updateable, but you can use the click event of
    > > the checkbox to append or delete records from tbl_projectproducts and then
    > > requery the form

    >
    > This is precisely what I've been doing since Access 2.0.
    >
    > Who needs "complex data" eh? :)
    >
    > Cheers,
    > Graham
     
  8. strive4peace

    strive4peace
    Expand Collapse
    Guest

    you're welcome, Millie ;)

    if you are going to use the checkboxes, you should add this
    field to tbl_projectproducts

    ProjProdID, autonumber

    this will give you a unique way to identify each record

    in the recordset, you can use this:

    IIF(IsNull(ProjProdID),0,ProjProdID)

    and then, if ProjProdID = 0, you know you need to add the
    record when the checkbox is ticked, or if >0, remove it when
    the checkbox is ticked ... then, after you issue SQL:

    currentdb.tabledefs.refresh
    DoEvents
    me.chkUsed.requery


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    millie.patel@gmail.com wrote:
    > thanks all! all your messages were very very helpful!
    >
    > :)
    >
    > Graham Mandeno wrote:
    >
    >>Hi Crystal
    >>
    >>
    >>>chkUsed will be a checkbox at the beginning of each row. Every product
    >>>will be listed and the ones that are actually records in
    >>>tbl_projectproducts will be checked.
    >>>
    >>>The form itself will not be updateable, but you can use the click event of
    >>>the checkbox to append or delete records from tbl_projectproducts and then
    >>>requery the form

    >>
    >>This is precisely what I've been doing since Access 2.0.
    >>
    >>Who needs "complex data" eh? :)
    >>
    >>Cheers,
    >>Graham

    >
    >
     
  9. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Graham,

    I love Access! I, too, have been using it since 2.0 -- and
    continue to learn great new things all the time!

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Graham Mandeno wrote:
    > Hi Crystal
    >
    >
    >>chkUsed will be a checkbox at the beginning of each row. Every product
    >>will be listed and the ones that are actually records in
    >>tbl_projectproducts will be checked.
    >>
    >>The form itself will not be updateable, but you can use the click event of
    >>the checkbox to append or delete records from tbl_projectproducts and then
    >>requery the form

    >
    >
    > This is precisely what I've been doing since Access 2.0.
    >
    > Who needs "complex data" eh? :)
    >
    > Cheers,
    > Graham
    >
    >
     
  10. millie.patel@gmail.com

    millie.patel@gmail.com
    Expand Collapse
    Guest

    i could have subforms -- but was just wondering if there was a
    different way of doing it -- im not too familiar with using subforms :(

    but all your information was helpful!
    thanks!

    strive4peace wrote:
    > Hi Millie,
    >
    > you can force a save like this:
    >
    > if me.dirty then me.dirty = false
    >
    > if you are using the AfterUpdate event of a control (perhaps
    > projectname), it is obviously dirty, so you can do this:
    >
    > me.dirty = false
    > msgbox me.id_controlname
    >
    > even though you may have more to fill out, you can get the
    > ID right away (as long as you don't have other fields that
    > need to be filled before the record saves)
    >
    > just out of curiosity, why do you need the ID? Why not have
    > tbl_projectproducts be the recordsource for a subform with
    > projectID in LinkMasterFields and LinkChildFields?
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > millie.patel@gmail.com wrote:
    > > Hi,
    > >
    > > I am working on a form right now.
    > > I have three tables:
    > > tbl_projects
    > > --projectID
    > > --projectname
    > > --projectdescription
    > > --projectmanager
    > > tbl_products
    > > --productID
    > > --productname
    > > --productshortname
    > > --productcategory
    > > tbl_projectproducts
    > > --projectID
    > > --productID
    > >
    > > The form is used to enter a project, it has multiple fields (i.e.
    > > project name, project manager, project description).
    > >
    > > Only when the project is entered into the database, can I retrieve the
    > > autonumber (primary key) to use to insert values into the
    > > projectproducts table.
    > >
    > > Is there a way to retrieve the value on the same form? so that i can
    > > enter the product info right away, besides going to a new page?
    > >
    > > thanks!
    > >
     
  11. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Retrieving auto number value immediately -- Main/Subform

    Hi Millie,

    There are other ways, but knowing how to use subforms is
    important. Subforms would be the easiest way.

    The subform is created just as a form is -- it is called a
    subform because of the way it is used.

    make a from based on tbl_projectproducts

    DefaultView --> Continuous Forms

    since this will be used as a subform, you would have the
    projectID field and its Visible property could be No (you
    will not need it to show)

    When I have controls that are not visible, I make the
    backcolor black and the text white so they stand out more
    when you are in the design view

    Then, as Graham said, you could use a combobox for ProductID

    Name --> ProductID
    RowSource -->
    SELECT productID, productname
    FROM tbl_products
    ORDER BY productname
    ColumnCount --> 2
    ColumnWidths --> 0;2
    ListWidth --> 2
    (this adds up to the sum of the column widths)

    since the ID column will have a width of 0, it won't show,
    but that is what will be stored

    The main form will be based on tbl_projects

    to this form, add a subform control

    SourceObject --> formname for tbl_projectproducts
    LinkMasterFields --> projectID_controlname
    LinkChildFields --> projectID_controlname

    the link fields must be ON the respective forms

    since you are new to this, forget about the checkbox
    stuff... you can print that information and read it later
    when you are more comfortable with subforms

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    millie.patel@gmail.com wrote:
    > i could have subforms -- but was just wondering if there was a
    > different way of doing it -- im not too familiar with using subforms :(
    >
    > but all your information was helpful!
    > thanks!
    >
    > strive4peace wrote:
    >
    >>Hi Millie,
    >>
    >>you can force a save like this:
    >>
    >>if me.dirty then me.dirty = false
    >>
    >>if you are using the AfterUpdate event of a control (perhaps
    >>projectname), it is obviously dirty, so you can do this:
    >>
    >>me.dirty = false
    >>msgbox me.id_controlname
    >>
    >>even though you may have more to fill out, you can get the
    >>ID right away (as long as you don't have other fields that
    >>need to be filled before the record saves)
    >>
    >>just out of curiosity, why do you need the ID? Why not have
    >>tbl_projectproducts be the recordsource for a subform with
    >>projectID in LinkMasterFields and LinkChildFields?
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>millie.patel@gmail.com wrote:
    >>
    >>>Hi,
    >>>
    >>>I am working on a form right now.
    >>>I have three tables:
    >>>tbl_projects
    >>>--projectID
    >>>--projectname
    >>>--projectdescription
    >>>--projectmanager
    >>>tbl_products
    >>>--productID
    >>>--productname
    >>>--productshortname
    >>>--productcategory
    >>>tbl_projectproducts
    >>>--projectID
    >>>--productID
    >>>
    >>>The form is used to enter a project, it has multiple fields (i.e.
    >>>project name, project manager, project description).
    >>>
    >>>Only when the project is entered into the database, can I retrieve the
    >>>autonumber (primary key) to use to insert values into the
    >>>projectproducts table.
    >>>
    >>>Is there a way to retrieve the value on the same form? so that i can
    >>>enter the product info right away, besides going to a new page?
    >>>
    >>>thanks!
    >>>

    >
    >
     

Share This Page