Results 1 to 13 of 13
  1. #1
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18

    How to autofill fields based on previous entry

    Hello everyone,



    I have been following some suggestions i found online, but I'm afraid I just can't make them work as I do not fully understand them.

    Basically I have a form (frmContent) for a personal library with the following fields:
    cboPhysicalBook
    txtTitle
    txtGenre
    txtPageNumber
    txtPublicationYear
    As each story can have multiple thematic tags, I then have a continuous subform (sbfrmTags with cboTag) to enter them.

    The idea is that if a physical book only has one story inside, I click a button and it copies the title information from the physical book. But if the physical book has several stories, then I would click another button and it would copy the
    cboPhysicalBook
    txtGenre
    txtPublicationYear
    from the previous entry. - It's this second button I'm having problems with. I've tried several codes I found online, but I can't make it work.

    Next, I would like to apply the same principle to the subform: click a button and it would copy the entries from the previous entry. Only each entry can have one, two or more tags - so I'd need to find all the tag entries associated to the id of the frmContent?

    Any help would be much appreciated.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Allen Browne has an excellent website and great routine for doing exactly this http://allenbrowne.com/ser-57.html

  3. #3
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    Thank you for the link - it looks very promising. Due to lack of time, I'll study it at the weekend to apply it to my db.

    I'll let you know how it went next week.

  4. #4
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    I'm having some problems with Allen's routine:

    1) a new entry is created, but nothing is duplicated there being the following error:
    >> Run-time error '3061': Too few parameters. Expected 2.
    Then the debugger kicks in and highlights:
    >> DBEngine(0)(0).Execute strSql, dbFailOnError
    I have made a change to the code as I actually have three subforms that have to be duplicated. I simply added two more update queries after the first one, but I can't check if this works because the first if+query sets the debugger off.


    2) Despite the good explanation, I'm still a bit lost in what I am doing - namely the fields. I understood that first appear the field names (table) and then the form's control names (their source being the table field names identified first).
    Code:
    'Duplicate the main record: add to form's clone.
            With Me.RecordsetClone
                .AddNew
                    !LivroFisicoID = Me.LivroFisicoID
                    !SubGenero = Me.SubGenero
                    !AnoCriacao = Me.EscritoEm
                .Update
    Is this correct? Because, despite the duplication of the subforms not functioning, I assumed this part should be functioning (and it isn't).


    3) I'm also having trouble with this part of the code:
    Code:
    'Save the primary key value, to use as the foreign key for the related records.
                .Bookmark = .LastModified
                lngID = !OrderID
    My form is based on a table whose primary key is called ID, then it has relationships to three tables and the foreign IDs are called BookID in one, BookContentID in the other two. Should I set the foreign IDs to be called the same and then set the code as:
    Code:
    'Save the primary key value, to use as the foreign key for the related records.
                .Bookmark = .LastModified
                ID = !BookContentID
    Thanks for anyone helping me understand this.

    PS: sorry if I'm not very clear in my descriptions, not only is English not my native language, I'm also quite new to access.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    Thanks for your suggestion June7.

    This works, but only in the form. For the subform, I tried adapting so I could duplicate from the form (it did not work) and then I added a button to the subform - it duplicates the values, but it does not create a connection between the main form and the subform (key and foreign key).

    One of my subforms usually has two to three repeating entries. This continuous subform tracks the people related to the book, for example: owner Teresa; owner Sara; writer Tom. I assume the easiest way is to click next to these subform-entries so as to control which ones I want to copy to the next form-entry. But I would have to say that I want to copy the ID of the book plus add 1 to it. How can I do this?

    Or would it be best to create a new form-entry and from there say I want to copy the information from the previous entry (adding buttons next to each control to detail which information to copy)? But, in this case I would have to... use an update-query to get all the subform-entries related to the previous book and then copy them adding a +1 to the bookID?

    I'm trying to think logically based on what little I know of access. Are any of these feasible?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not really understanding data structure. I suspect you may need another table. Table for book info. Table for stories. And if you want to allow for possibility of a story published in more than one anthology, a junction table that relates books with stories.

    Do you have multiple copies of the same book and want to track each one individually, including owner? You don't own these books?

    Do you want to assign theme tags to books or stories or both?

    Why do you need to assign a sequential ID to each story?

    If you want to provide database for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    Actually, I have a lot of tables. Most are just for populating combo boxes, but then I have the main tables:
    - Physical Book (with height, length, etc., publishing house, paperback or, etc.)
    - Book Content (I have a lot of literary and scientifical anthologies, so here I assign the authors and etal, genre + subgenre, theme + subtheme tags, as well as page count, first published year)
    - People (first, middle and last names, possibility of identifying pseudonyms, biography, etc)

    Next come the linking tables:
    - Each book content is linked to a physical book
    - Every person is assigned a function related to a book or content (it's all in the same table because a person can be the coordinator of an anthology and writer of an article)
    and so on

    Sorry, but I'm not at my home computer, so I can't see all the details right now.

    The database is working - I even had it automate so that if the book I enter is not an anthology (and the content thus has the same title) I just click a button and copy the information from the physical book title to the content title.

    But entering all my +/-1500 books (plus some extra magazines whose articles I need to reference to quickly find information) is tedious work when almost every book has the same information. So I'm trying to automate it a bit.

    For example: most books are owned by the same two people, and most content has been read by the same two people. Also, I'm entering books in collections so Mistery-detective tags will be in use for tens of books before I move on to the next collection.

    I don't mind sending it to you, but I sent it back when I was having a silly navigator-form problem (I gave up on the navigation form, just wrecked havoc on some vba I have working otherwise) and you told me you couldn't open it because it's a 2016 Access version.
    (I think it was you, not sure and haven't gone back to check - if it wasn't you, then I'll send it. Otherwise, I'll just take a bit next... Friday, I'm in Midterm hell at my school so I'm doing extra hours. So by the end of the week I'll have the whole thing described carefully with the code I'm using added in as well).

    Thank you for your interest in my problem.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Could be I did try downloading your db in another thread, sounds familiar - hard to keep track.

    If you don't want to just carry forward values of newly created record to next new record, then the method of setting DefaultValue property may not be appropriate. Your idea of clicking an existing record to capture those values for a new record should be workable. More than one way to accomplish - set DefaultValue property or set global variables then move to new record row and populate fields or the Allen Browne code using RecordsetClone.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    Thanks for your pointers. I'll try to find some type to study Allen's code better. In the mean time, I've managed to quickly set up a description of my db's structure.

    Note: all the (tables in between brackets) in front of the fields are merely for menus, and are all set as subforms in a Maintenance Form called forth by a button in the BookContent Form.

    tblPhysicalBooks (the corresponding form is called forth by a button in BookContent)
    ID
    ISBN
    BookType (tblBookType)
    Title
    PublishingHouse (tblPubl)
    PublishingYear
    CoverType (tblCoverType)
    PurchaseYear
    PurchasePlace (tblPurch)
    Height
    Length
    Width
    Comment

    tblBookContent (main form with buttons to call forth the other central forms)
    ID
    PhysicalBookID
    Title
    SubGenre (tblSubGenre)
    CreationYear
    TotalPages

    tblPeople (the corresponding form is called forth by a button in BookContent)
    ID
    FirstName
    MiddleName
    LastName
    Pseudonym
    ofRealName
    BirthCountry (tblCountry)
    BirthYear
    Biography

    tblBookPeople (as a subform both in BookContent and PhysicalBook, although there are always different functions)
    ID
    PhysicalBookID
    BookContentID
    FunctionID (tblFunction)
    PersonID

    tblBookContentLanguage (as a subform in BookContent)
    ID
    ContentID
    LanguageFunction (tblLangF)
    Language (tblLanguage)

    tblBookContentTheme (as a subform in BookContent)
    ID
    BookContentID
    Theme (tblTheme)
    SubTheme (tblSubTheme)


  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    No offense, but IMO, you have a poor naming convention.
    Using "ID" and the PK (primary key) field name is/can be confusing. You have linking fields named "tblPhysicalBooks.ID" --> "tblBookContent.PhysicalBookID"
    .....What????? Not very obvious....


    Consider:

    tblPhysicalBooks (the corresponding form is called forth by a button in BookContent)
    PhysicalBooksID_PK (autonumber)
    BookTypeID_FK (number - long integer)
    PublishingHouseID_FK (number - long integer)
    CoverTypeID_FK
    (number - long integer)
    PurchasePlaceID_FK
    (number - long integer)
    ISBN
    Title
    PublishingYear
    PurchaseYear
    Height <<< reserved word in Access
    Length
    Width <<< reserved word in Access
    Comment

    tblBookContent (main form with buttons to call forth the other central forms)
    BookContentID_PK (autonumber)
    PhysicalBookID_FK (number - long integer)
    SubGenre_FK (number - long integer)
    Title
    CreationYear
    TotalPages

    tblPeople (the corresponding form is called forth by a button in BookContent)
    PeopleID_PK (autonumber)
    FirstName
    MiddleName
    LastName
    Pseudonym
    ofRealName
    BirthCountry (<< not sure if this is linked to a table)
    BirthYear
    Biography

    tblBookPeople (as a subform both in BookContent and PhysicalBook, although there are always different functions)
    BookPeopleID_PK (autonumber)
    PhysicalBookID_FK (number - long integer)
    BookContentID_FK (number - long integer)
    FunctionID_FK (number - long integer)
    PersonID_FK (number - long integer)

    tblBookContentLanguage (as a subform in BookContent)
    BookContentLanguageID_PK
    (autonumber)
    ContentID_FK
    (number - long integer)
    LanguageFunction (<< not sure if this is linked to a table)
    Language (<< not sure if this is linked to a table)

    tblBookContentTheme (as a subform in BookContent)
    BookContentThemeID_PK
    (autonumber)
    BookContentID_FK
    (number - long integer)
    Theme (<< not sure if this is linked to a table)
    SubTheme (<< not sure if this is linked to a table)

  12. #12
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    Hi Steve. Thank you for your pointers - they're very helpful. Just to make sure I understood - I should use _PK or _FK to identify the primary/foreign key instead of just ID, right? My "how to" access manual has a different approach to naming but yours seems much more informative.

    I didn't realise about the height/width being reserved words because I am using my native language with English Access - so it doesn't give me any red flags with reserved words.

    As for the other fields you refer, yes, they are all linked to a specific table.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I should use _PK or _FK to identify the primary/foreign key
    That is how I name fields. It is easy to look at the relationship window (if you use it) to see/set the relationships.
    It is easy to see that "PhysicalBookID_FK" is a foreign key from the "tblPhysicalBooks" table (PK field is named ("PhysicalBookID_PK").
    "ID" means that the field is a number (long integer) field. And in the case of "PK", it means (to me) that the field type is an autonumber. (note: I NEVER have a Text type field as a PK field.)

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    AutoNumber
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


    My "how to" access manual has a different approach to naming but yours seems much more informative.
    Most "how to" manuals use "ID" as a field name, but it gets confusing if you have 10 tables with "ID" as a field name.
    You should always take the time to give short, meaningful names to objects.
    Trying to follow code with procedures with names like
    Button2_Click()
    Text12_DblClick()
    ComboBox_AfterUpdate()

    is a real PITA! What do those routines do????

    Can you get an idea what these routines might do?
    btnFormClose_Click()
    tbLastName_DblClick()
    cboCompany_AfterUpdate()

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Autofill field from previous fields entered
    By jucooper1 in forum Forms
    Replies: 17
    Last Post: 12-31-2016, 11:29 AM
  2. New values added to autofill-entry based on table
    By MS_Access_User in forum Access
    Replies: 4
    Last Post: 10-30-2016, 12:51 PM
  3. Replies: 7
    Last Post: 03-05-2015, 07:34 AM
  4. Replies: 1
    Last Post: 02-14-2015, 02:29 AM
  5. Replies: 7
    Last Post: 02-10-2012, 08:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums