1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Append from a Form

Discussion in 'Business Applications' started by ste2223, Apr 7, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    I have a database which contains 4 tables. In one of the tables I have all the info reguarding the Geckos (Names, Sex, Parents etc...). In another table I have a list of the eggs that gecko has laid. The Egg table contains some of the values I want appending to the Geckos Table such as, Mother, Father, HatchDate to create a new record.

    I want to put a command button on the Geckos Form that basically prompts for an EggID and opens up a form with most of the data filled in (the data mentioned above in the Egg Table). The all the user has to do is fill in the blanks.

    I have no idea on how to do this. I'm guessing I would have to use an Append Query but thats as far as I have got. Any help, suggestions or alternative meathods would be great. The DB is too big to upload on here but I can email to you (PM me you email address).

    Thanks in advance.
    Ste
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    ste, unfortunately I can't work with Access 2007 format databases, only Access 97-2003.
    The way that you have described your database strongly suggests that it is not designed correctly as the Tables should not contain "Duplicate" data.
    As I am not sure what you start with, i.e. the Gecko data or the Egg data I am not sure what changes should be made.
    But normally the Master table would be the Parents and the Sub table would be the children (or eggs in your case).
    But if you don't know the Gecko data until you get an egg that could be a problem.
    Back to your problem in either case, the Sub Table should only contain a "Reference" to the master table and it is the Key field from the Master Table.
    Can you post a ScreenPrint of your Table Relationships?
     
  3. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    Thanks for the reply. I basically want to convert the info I have about the 'Egg' into a 'Gecko'. I prob have gone all wrong with this but heres the pic you wanted.

    [​IMG]

    I can provide pics of any other part of the DB. Just ask and I'll upload them.

    Thanks again.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I assume that the each Gecko record is for just one Gecko?
    As it is set up at the moment the GeckoID only Identifies the Mother Gecko of the Egg. That relationship could be taken care of by a Main/Subform Link, but how do you then identify the Father Gecko.
    It looks like you should have one or two Sub Tables which would be "Many to Many" joining tables.
    If one table was used the new Table would contain
    EggID
    ParentType (Mother or Father)
    GeckoID

    If a 2 table set up was used you could have an EggMother Table and an EggFather table which would both contaiin
    EggID
    GeckoID

    How do you handle the Gecko Table's Mother and Father, because they should be in the Gecko Table as well?
     
  5. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    Yes each Geckos has his/her own record. I identify the father of the egg from a query. I'm not very good with DB design and I havn't a clue what your talking about :confused:. I have just saved a copy of the DB in Access 2002-2003 if this helps?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes that helps, if you zip it up you should be able to post it as an Attachment using the Go Advanced and Manage Attachment buttons.
    Well most table relationships are One to Many, i.e. One record in the maintable that has a key field that can be used Many times in another field. An Example would be an AddressID, it can be used in another table for as many people who live at that address. But when you have a Gecko that can have more than one Egg and an Egg that can have more than one Parent Gecko it is better to use a 3rd table to "match" them up.
    I can create such a table for you.
    But you could have 2 tables one for Mother Geckos and one for Father Geckos, it is up to you which way we do it.
    Do you keep any note of the what happens to the Eggs, i.e. do you hatch any of them or sell them?
     
  7. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    Not sure which method would be best. As for the eggs, we hatch them. They stay in the egg table until they hatch then they are flagged as hatched and added to the geckos table (well thats the way I want to do it). I have attached a copy of the DB. Note it does contain some VBA code which shows/hides a subform depending on if the gecko is female or male.
     

    Attached Files:

  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    In your first post, were you talking about showing the Mother & Father gecko's names in the Combo box on the Egg form?
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Is the HatchedGecko to be used for the Geckos name after hatching and then transferred to the Gecko table?
     
  10. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    I want the HatchedGecko from the Egg table to be the GeckoID from the gecko table. Basically so I can look throught the Egg table and see the gecko each egg hatched into.

    When an egg is laid I fill in the Mother, Father, LaidDate and Location. When an egg hatches I want to be able to click a button and the Mother and Father fields on the Geckos Form are already filled in. I also want it to add the new GeckoID to the HatchedGecko field in the egg table (as mentioned above). The hatched button would also need to be ticked, which is also in the egg table.

    So the button would do the following:
    Create a new Gecko record
    Fill in the mother and father fields (in the gecko table/form)
    Add the new GeckoID to the HatchedGecko field in the egg table
    Tick the Hatched field in the Egg Table
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You do not need a button to do that, it can be when you enter the Hatched Date
     
  12. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    How?
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You can use the After Update Event of the Hatch Date field.
    Do you want me to do that?
    It will have to be tomorrow as the wife doesn't let me work in the evenings and it is now 6:30pm here.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Have you considered using a Tabbed Mainform with Subforms for this application?
     
  15. ste2223

    ste2223 Thread Starter

    Joined:
    Sep 2, 2004
    Messages:
    197
    If you do it then I can take a look at it and figure out how it works (I like to learn new things lol).

    Not sure what a Tabbed Mainform is.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/915424

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice