Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Unanswered: Best way to enter records with most data the same

    I apologise if this is very easy as its been a while since I've used Access.

    In very simple terms I have a database with Field1 Field2 Field3 and Field4. There are times if a user is inputting 100 records at a time where Field1, Field2 and Field 3 are the same but Field4 is different. I was hoping to find the best way to do this so I could perhaps use a form with drop down lookups for Fields 1-3 and then have the ability to allow the user to enter data for field4. As and when they finish entering the data for field4 it adds the record and moves onto a new record for them to scan the next field4 (keeping fields 1-3 the same). When the user has finished he/she can come out of the form and perhaps repick another set of values for fields 1-3.

    Im probably missing something simple or trying to run before I can walk (LOL)

    Guidance is greatly appreciated. I have thought about trying to create an update query or using a form with a subform but im not sure..

  2. #2
    Join Date
    Jun 2007
    Posts
    6
    Ive just had a thought (very dangerous).

    Would I be able to create a default table (say tbl_DefaultValues) that stores the default values of Field1 (called DefaultField1), Field2(called DefaultField2), and Field3(DefaultField3).

    Can I then create another input form and have Field1 with a default value of =[tbl_DefaultValues]![DefaultField1],a default value for Field2 of =[tbl_DefaultValues]![DefaultField2] and so on or is it not that simple? I tried but got #Name errors so I may be barking up the wrong tree.

    Many apologies if this is so simple.....

  3. #3
    Join Date
    May 2009
    Posts
    258
    Your second thought is closer to what you will want:

    1. Create a table with Field1, Field2, and Field3 (all three fields as the primary key)
    2. Create a second table with Field1, Field2, Field3, and Field4 (no need for primary key, but suggest indexing the first three fields)
    3. Go into the database relationships, add the two tables, and create a one-many relationship between them on Field1, Field2, and Field3. You can specify cascading updates/deletes if you want (helpful if Field1, Field2, and Field3 will ever be changed.
    4. Save and close the relationships window
    5. Open the first table and click Insert|Subdatasheet...
    6. Double-click the second table
    7. You'll now have a [+] on the left side of each record in the first table, clicking that will allow you to manage the Field4 values in the second table
    8. Save the first table


    Now all you need to do is add one value in the subdatasheet and the record will be populated in the second table.

    A form wouldn't be necessary, but you could easily create one if required.

    Regards,

    Ax

  4. #4
    Join Date
    Jun 2007
    Posts
    6

    Give it a go

    Thank you so much for the help. I will try it now. Many thanks once again its so greatly appreciated.

  5. #5
    Join Date
    Jun 2007
    Posts
    6
    Well ive certainly had a good go at this but am experiencing a few novice issues....

    I created table 1 with field1 field2 field3 and made field1 field2 field3 as the primary key (the key sign is beside all three). I created table 2 with field1, field2, field3, field4. I went into database relationships and created a one-many relationship on field1 field2 field3

    When I inserted the subdatasheet I appear to be only able to link on one field and im struggling to work out how to add more link fields so that I only have to manage entering the data into field4. It currently expects me to enter field2 and field3 as well as field4

    Many thanks in anticipation.

  6. #6
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Hi

    please find attached, Very basic example database,

    on the course form there is a Field 1 - 3 drop downs, I think this is what you mean.

    look at the design of the course table in design view, go to the Field 1 2 or 3, go to the lookup tab, on the row source you will see the sql join

    Hope this helps
    Attached Files Attached Files

  7. #7
    Join Date
    Jun 2007
    Posts
    6

    Smile

    Hi there Mark,
    Not quite what I was after (I think unless I have misunderstood).

    I have over 100 data items that have similar data to log at a time where field1 field2 field3 are the same but field4 are different. So in theory I was hoping the user could pick the data for field1 , field2, field3 once and then input just field4 (by adding a new record) until such time as they decide to change any of the field1 field2 or field3 data. I am just struggling to be able to do this part and can see the benefit of a subdatasheet (which I can create a form if needed) but I can only seem to get one field to be consisitent all teh way through. Unfortuantely I am not proficiant VBA programmer by any means (or not all lol). Ideally I would have thought the user could have seen a form that they choose the main defaults (ie set fields 1-3) and then they go ahead and enter the field4 information, click add new record (or tab ). But I know I want to run before I can walk.......I may have completely misunderstood your database and if I have I really do apologise.

    Jen
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    perdsonally I wouldnt' go downt he route of creating a table.. seemns an odd way to do it.

    what I'd do is to declare some global variables in your form (one for each value/control/column)
    when ever you update the data transfer the values formt he controls to the varaible

    if you wish place a button on the form which copies the value from variables to the controls

    that button could also force a docmd.addrecord
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Sry Jen

    I have completly miss understood what you were after, the Basic solution I gave you is No good.

    Not sure how you would go about doing the Jaded Developers solution, may be he coud show us both an example, from which we could both learn

    Mark

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Jaded devloper is kindof jaded... I may have a play a leetle later on
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    perhaps I'm not as jaded as i though I was

    this isnt the finished article but it does show what can be done
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    For each control whose data you want to “carry forward,” you can use the AfterUpdate event to set the DefaultValue for the field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

    Code:
    Private Sub YourControlName_AfterUpdate()
       Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Jun 2007
    Posts
    6
    Well thank you so much for all your guidance. I got it to work a treat! At first I did a form with some default values and used a button on the form that once clicked would save the current record, then add a new record with the default values set up on the 3 fields. The user then just needed to scan in the last bit of information . Then I used the afterupdate value.
    It still amazes me how many ways you can do things and I am so conscious that I want to run before I can walk.

    Ive learnt a lot thank you so much.
    Now moving onto my next challenge of getting the user to scan 100 pieces of information into a despatch table and create an update query that will update certain fields for only those records in the despatch table (or at least that is my first plan). I feel a little more confident now! (But I think this may be temporary lol!) Thank you once again.

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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