Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    62

    Unanswered: Forms to add records

    Hi,
    is it possible to add a number in a form so that it creates additional records to a table.
    For example, if I have two boxes on a form, one for males and one for females. If I put "5" in males and "4" in females will nine new records be created in the table. These 9 new records would say m,m,m,m,m and then f,f,f,f?

    Thanks for help in advance,
    Sue

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    With code you can do anything.

    Everything is event driven. When do you want it to add these records? When the user pushes a button?

    Here is the psuedo code for that

    function addRecords

    message "do you want to add x males and Y females?"
    if yes then
    make recordset for table in question.
    For Loop for the guys x times
    addnew record
    GenderField = M
    update
    next guy

    For Loop for the girls y times
    addnew record
    GenderField = F
    update
    next girl
    set recordset to nothing
    give message to user that you are done.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If you are not into doing code as jpshay has given yoy you might try this.

    First make a copy of your table and then delete all but two records. One record will be for F and one will be for M.

    Now make a query that appends this table to the table that supports your form.

    You now make a macro that runs the append query. You then make another macro that has only a RunMacro action and it runs the first macro. In this second macro you have control how many times it run.

    So in the Count section put in =[Forms]![MyForm]![Male]

    If you now run the second macro (your form must be open) it will run 8 times if you have entered an 8 in the M field.

    You would make two append queries of the copy of your table and one would be restricted to F and the other to M.

    You make another macro so you have two sets of them but one macro runs them both.

    So if you have 8 in M and a 3 in F then 11 records will append with 8 having M and 3 having F

    Mike

  4. #4
    Join Date
    Apr 2004
    Posts
    62
    oo I understand that a bit better! I shall give it a go and let you know what happens..thank you both!
    cheers
    Sue

    p.s. yes my coding is pretty horrendous!

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    What jpshay said about being able to anything with coding is correct.

    However you can just about always get there with a combination of queries and macros. Although if want open and communicate with Word for example then you need the code.

    Mike

  6. #6
    Join Date
    Apr 2004
    Posts
    62

    a little more help please?!?!

    It think this is exaclty what I need!
    I've tried your method and I think I understand what is supposed to be happening but it doesn't work. I've followed what you suggested but can't see why it's not working. The append 'works' but it just puts in the one of the form and doesn't loop it.

    any ideas?
    thanks Sue

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Sue,

    When you say it does not loop it do mean the macro is only running once?

    Do you have the macro that runs the append query......being run by another macro, that is, a RunMacro action. What about Repeat Count for the RunMacro action. That is where you will need

    =[Forms]![MyForm]![The field where the 8 is entered]

    You might also like to do SaveRecord as the first line of the macro. Get that from do menu item action.

    Mike

  8. #8
    Join Date
    Apr 2004
    Posts
    62
    I'm not sure whether the macro is running '8' times! It gives me a warning dialogue box that ask whether I want to append it and I say yes and it comes up several times. Would this mean is is running several times?

    I've got the RunMacro macro and I've checked that the expression in the count section is as you've said.

    I don't understand the Save record bit!! sorry!

    Could it have anything to do with the set up of the table?

    ID Strain Litter Male Female
    1 thing 12 3 2

    This is table1.
    Table2 is a copy with just the Male and Female columns. Is this right?
    The end result, which I want would look like this:

    ID Strain Litter Sex M
    1 thing 12 M
    2 m
    3 m
    4 m
    5 m
    6 f
    7 f
    8 f
    9 f
    10 f

    I think I'm confused about the '2 records' I need to leave in the copied table!
    I do appreciate your help!!
    Sue

  9. #9
    Join Date
    Apr 2004
    Posts
    62
    the layout has changed on the tables I copied:
    I would like the final table to look like this:
    Strain........Litter.....Sex
    Field............1.........m
    .............................m
    ............................f
    ..............................f
    .............................f
    ............................f.

    That's a bit clearer I hope!

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I'm not sure whether the macro is running '8' times! It gives me a warning dialogue box that ask whether I want to append it and I say yes and it comes up several times. Would this mean is is running several times?

    Yes, it is running. What you do now is to start the first action line of the macro with SetWarnings and set for No.

    I don't understand the Save record bit!! sorry!

    In Access 95 you have a macro action called DoMenuItem. This allows you to slectd different actions that you could run from the tool bar. For the Menu name you will select records and then select SaveRecord form the next menu. This action is at the start of the macro and it makes sure that when you enter your 8 that this is saved before the rest of the macro runs.

    I think I'm confused about the '2 records' I need to leave in the copied table!

    The 2 records, one for F and one for M are there for the append query to have something to run.

    I have assumed that when you want 8 more M records added then those 8 records will have the basic data you need for your M record and then you would add other specific data after the records have been added. Same deal for the F records. So the two records in the other table contain the basic data you migh have for every M or every F record.

    Mike

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    the layout has changed on the tables I copied:
    I would like the final table to look like this:
    Strain........Litter.....Sex
    Field............1.........m
    .............................m
    ............................f
    ..............................f
    .............................f
    ............................f.

    That's a bit clearer I hope!


    The layout of the copied table should be identical.

    Try going to File and Save as Export and then select Within the Current data base. Then change the default table name to what your original copy was so that your queries will still be running off the same table.

    Mike

  12. #12
    Join Date
    Apr 2004
    Posts
    62

    ha ha

    I finally got that too work!! I don't know what I had been doing before, can't tell what has changed but it works now. Only thing now is it is possible to
    a) instead of the number going in the record can it say 'M' or 'F'.
    b) have the F records running underneath the M records within the same field? i.e
    Sex
    m
    m
    m
    f
    f
    f
    instead of
    Male............Female
    8...................2
    8...................2
    8...................2
    8
    8
    8
    8

    thank you for help so far - I think I'm slowly getting there!
    Sue

  13. #13
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I finally got that too work!! I don't know what I had been doing before, can't tell what has changed but it works now. Only thing now is it is possible to
    a) instead of the number going in the record can it say 'M' or 'F'.


    The macro needs the 8 to do the count. You could have one unbound text box for M and one for F where you enter the number. So let's say you enter your 8 you then have a macro SetValue action that sets the value of the other field to M or F as the case might be. You could do this when the unbound text box loses focus or you do it with the macro that runs the append queries. If using the macro that runs the append queries then the first action line after saverecord might be to SetValue the M or F field with the M or F. By the way Access 95 requires letters or writing in the Expression of SetValue to be between " " as in "F". Numbers do not have commas. I think later versions of Access such as 97 will just have F in the Expression. Basically, if you master SetValue and also "condtions" you can do just about anything but it requires a bit of playing around. Perhaps like writing a letter. You know the words but is a case of putting them together the right way for a good letter.

    b) have the F records running underneath the M records within the same field? i.e
    Sex
    m
    m
    m
    f
    f
    f
    instead of
    Male............Female
    8...................2
    8...................2
    8...................2
    8
    8
    8
    8


    If I am understanding you correctly what you want is one field for Sex rather than two fields as in one for Male and one for Female.

    You can do that OK but it will probably require you to make other changes. For example you might have had a query that displayed all the Female records where the litter had 4. If you now change Male and Female to one field then you would need to change that query.

    I think it is reasonable to say that most often the best way is to have variable data such as F and M in one field than a field for each piece of variable data. One reason for this is in the case when other variable data comes into play. About the most extreme version of variable data in one field would be different surnames!!!! So having a field for both Male and Female would be like a very small version of having a separate field for each different surname

    Now I don't know too much about cats so forgive me if my example is bad but you will get the idea Let's say that you introduce a third sex which is neuter or sexless, lets call it S. You now can place the S cases in your Sex field where as with a Male and Female field you would need to add a third field called Sexless.

    Remember that if you have the variable data in one field then Access can easily between its queries and/macros separate the data out.

    Mike

Posting Permissions

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