Welcome to SPN

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

Sign Up Now!

Auto add to table

Discussion in 'Information Technology' started by gb_S49, Jul 28, 2006.

Tags:
  1. gb_S49

    gb_S49
    Expand Collapse
    Guest

    I am a complete novice to access, so apologies for the simplicity of the
    question.
    I have 2 tables.
    Table one contains a list of names etc.
    Table 2 contains many columns but the second column contains numbers 1-31,
    entries per name (table 1)
    Is there a way that when I add a new entry to table 1, it will automatically
    create a additional 31 records (numbered 1-31) in table 2?
    i hope this makes sense
     
  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. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    Guest

    What you're asking for might be a little advanced. First of all, you can't
    really automate anything if you directly enter records into a table. You'll
    need to create a form based on your table and perform inserts there. The form
    will allow you to automate things like table inserts.

    To do the insert, I would recommend looking into Append queries. The hard
    part for you will be to increment the 1-31 thingy, but I would take it one
    step at a time.

    Barry

    "gb_S49" wrote:

    > I am a complete novice to access, so apologies for the simplicity of the
    > question.
    > I have 2 tables.
    > Table one contains a list of names etc.
    > Table 2 contains many columns but the second column contains numbers 1-31,
    > entries per name (table 1)
    > Is there a way that when I add a new entry to table 1, it will automatically
    > create a additional 31 records (numbered 1-31) in table 2?
    > i hope this makes sense
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    In the AfterInsert Event in the form U can put
    (select Properties /Event / After Update Event / "builder" / Event
    Procedure)


    '--------------------------------------------------

    Sub Form_AfterInsert()
    Dim Db AS DAO.Database
    Dim QDef AS DAO.QueryDef
    Dim i AS Long
    Dim SQL AS String

    Set Db = Access.CurrentDb ' Pointer to DB
    SQL="PARAMETERS pNAME TEXT, pVal Long;" & VBA.VbCrlF & _
    "INSERT INTO TABLE2 ([NAME],Field2) VALUES (pName,pVal)"
    Set Qdef = Db.CreateQueryDef(VBA.vbNullString,SQL) ' Creates a temporary
    Query to insert records

    Qdef.Parameters("pName").Value = Me("Name").Value ' Retrieve the Value of
    the Forms Control "Name" & Assign to Query Parm
    For i = 1 To 31
    Qdef.Parameters("pVal").Value = i
    Qdef.Execute DAO.DbSeeChanges 'Insert one row of data
    Next ' Loop 1..31
    Set Qdef = Nothing
    Set Db = Nothing ' Cleanup Pointers
    End Sub

    '-------------------------------------

    PS Change Field & Control Names to whatever U Use
    HTH

    Pieter


    "gb_S49" <gbS49@discussions.microsoft.com> wrote in message
    news:82FC4748-3131-450F-8F1E-0F64AB9C27CE@microsoft.com...
    >I am a complete novice to access, so apologies for the simplicity of the
    > question.
    > I have 2 tables.
    > Table one contains a list of names etc.
    > Table 2 contains many columns but the second column contains numbers 1-31,
    > entries per name (table 1)
    > Is there a way that when I add a new entry to table 1, it will
    > automatically
    > create a additional 31 records (numbered 1-31) in table 2?
    > i hope this makes sense
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    gb_S49 wrote:
    > I am a complete novice to access, so apologies for the simplicity of
    > the question.
    > I have 2 tables.
    > Table one contains a list of names etc.
    > Table 2 contains many columns but the second column contains numbers
    > 1-31, entries per name (table 1)
    > Is there a way that when I add a new entry to table 1, it will
    > automatically create a additional 31 records (numbered 1-31) in table
    > 2?
    > i hope this makes sense


    It would appear that properly have one table to the individual and then
    as second related table for some sort of event(s) that take place on a daily
    bases. Right now you appear to be recording the data on a monthly bases.
    Maybe it might be better to record those events as the happen and record the
    actual date they occur. That way you will not have blank records created,
    and you will be able to query for a month, a week or any time period you
    might need or want. No need to delete older data and you can have that for
    reference.

    Also I get suspicious when someone has "many columns" That may be a
    candidate for additional normalization. Also remember that databases don't
    have columns, they have fields that may be displayed in columns. Thinking
    in terms of columns and not fields tends to make one not notice that the
    data needs to be normalized. :)

    Good Luck

    --
    Joseph Meehan

    Dia duit
     
  6. gb_S49

    gb_S49
    Expand Collapse
    Guest

    Pieter,
    Thank you

    "Pieter Wijnen" wrote:

    >
    >
    > In the AfterInsert Event in the form U can put
    > (select Properties /Event / After Update Event / "builder" / Event
    > Procedure)
    >
    >
    > '--------------------------------------------------
    >
    > Sub Form_AfterInsert()
    > Dim Db AS DAO.Database
    > Dim QDef AS DAO.QueryDef
    > Dim i AS Long
    > Dim SQL AS String
    >
    > Set Db = Access.CurrentDb ' Pointer to DB
    > SQL="PARAMETERS pNAME TEXT, pVal Long;" & VBA.VbCrlF & _
    > "INSERT INTO TABLE2 ([NAME],Field2) VALUES (pName,pVal)"
    > Set Qdef = Db.CreateQueryDef(VBA.vbNullString,SQL) ' Creates a temporary
    > Query to insert records
    >
    > Qdef.Parameters("pName").Value = Me("Name").Value ' Retrieve the Value of
    > the Forms Control "Name" & Assign to Query Parm
    > For i = 1 To 31
    > Qdef.Parameters("pVal").Value = i
    > Qdef.Execute DAO.DbSeeChanges 'Insert one row of data
    > Next ' Loop 1..31
    > Set Qdef = Nothing
    > Set Db = Nothing ' Cleanup Pointers
    > End Sub
    >
    > '-------------------------------------
    >
    > PS Change Field & Control Names to whatever U Use
    > HTH
    >
    > Pieter
    >
    >
    > "gb_S49" <gbS49@discussions.microsoft.com> wrote in message
    > news:82FC4748-3131-450F-8F1E-0F64AB9C27CE@microsoft.com...
    > >I am a complete novice to access, so apologies for the simplicity of the
    > > question.
    > > I have 2 tables.
    > > Table one contains a list of names etc.
    > > Table 2 contains many columns but the second column contains numbers 1-31,
    > > entries per name (table 1)
    > > Is there a way that when I add a new entry to table 1, it will
    > > automatically
    > > create a additional 31 records (numbered 1-31) in table 2?
    > > i hope this makes sense

    >
    >
    >
     

Share This Page