Results 1 to 11 of 11

Help with creating prefilled record through code with subforms

  1. #1
    Rah is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5

    Question Help with creating prefilled record through code with subforms


    I'm new to coding in Access but keen to learn more and would appreciate some help. Sorry if I break any forum regulations, still learning.

    Here is the scenario.

    We do a lot of data entry in our business, and often the next record we enter has a lot of the same values as the one you've just entered.

    So I'm trying to get the 'Add New Record' button in our form to automatically pull across details from the record just entered into the new record - to save time on double entering data.

    I've attached a picture of what a filled out record looks like compared to a fresh load of a record.

    I've figured out how to pull across info from the main form into the new record (for example all the 'vessel, date, location' etc fields up the top of our form).

    However, I'm having trouble
    • Putting details into the subforms (for example the 'seagrass' form)
    • Adding new rows in the subform (the seagrass form for example always has 3 records entered with the same observer)
    • Also, having issues with setting the value for objects that have a space in the name.. for example the "exclude from biomass" tickbox, I always have issues trying to set the value of that box through code


    Here is the code that I've come up with

    Green text all works fine, red is error, blue (while it does set focus of the cursor to the 'observer' box in the seagrass form) does nothing but runs

    Code:
    Private Sub Command98_Click()
        Dim ID As Long
        Dim ObCount As Integer
    
        ID = Site
        ObCount = 0
    
        DoCmd.GoToRecord , , acNewRec
    
        Site = ID + 1
        Date = DLookup("Date", "SITE DETAILS", "Site=" & ID)
        Location = DLookup("Location", "SITE DETAILS", "Site=" & ID)
        Substrate = DLookup("Substrate", "SITE DETAILS", "Site=" & ID)
        Vessel = DLookup("Vessel", "SITE DETAILS", "Site=" & ID)
        Heli = DLookup("Helicopter", "SITE DETAILS", "Site=" & ID)
        Cam = DLookup("Camera", "SITE DETAILS", "Site=" & ID)
        Gra = DLookup("Grab", "SITE DETAILS", "Site=" & ID)
        Walking = DLookup("Walking", "SITE DETAILS", "Site=" & ID)
        Dive = DLookup("Diver", "SITE DETAILS", "Site=" & ID)
        Grass = DLookup("Seagrass", "SITE DETAILS", "Site=" & ID)
        Exclude from Biomass = DLookup("Exclude from Biomass", "SITE DETAILS", "Site=" & ID) - this results in error 
        
        Seagrass.SetFocus
    
        Do Until ObCount = 4
        Observer = DLookup("Observer", "BIOMASS Ranks", "Site=" & ID)
        Form.NewRecord - this errors but what I'm trying to achieve is adding a new row to this form, then I would need to know how to select and set the new 'Observer' box
        ObCount = ObCount + 1
        Loop
        
    
    
    End Sub

    I would really appreciate any help. Thanks in advance
    Attached Thumbnails Attached Thumbnails example fresh load.PNG   example filledout form.PNG  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,885
    One simple approach is to set DefaultValue property of a control with value just entered. Then each next record will adopt that value until a different value entered and DefaultValue is changed. Use control AfterUpdate event to set DefaultValue property. Review http://www.theaccessweb.com/forms/frm0012.htm

    If you want first record of a session to start with values from an existing record, that would require looking up latest record in table.
    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.

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    322
    Hmm, this should help...
    http://allenbrowne.com/ser-57.html
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  4. #4
    Rah is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5
    Thanks for both of your help.

    I've tried (in my own way) to incorporate the advice you've given me but I'm just not getting anywhere.

    Would someone mind giving an example of how I might

    Setfocus to the Seagrass sub form, set the observer field to XYZ name, and then add 2 new rows with the same name.

    I'm just not wrapping my head around it. Sorry.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,885
    I think Allen Browne's article is an example of that. If you want to provide db for analysis, follow instructions at bottom of my post.

    But why don't you just do data entry? How many fields/controls would you need to carry forward the same data? What do you not understand about the suggested options?

    Wth the DefaultValue option, at least one control must require user input in order to initiate new record and trigger filling controls with default value.
    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
    Rah is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5
    We would enter approx 10,000 or so records a year where the details only vary slightly but >80% of the content is usually the same as the previous entry. So I'm trying to reduce time spent / room for human error by automating a lot of the steps.

    But the coding required is over my head.

    I've attached the DB - the form in question is 'DATA ENTRY' and I'm trying to do it all with the small 'command' button.

    It's hard to say exactly what I don't understand about the suggested options - I just tried to incorporate them in but I didn't really understand what was going on and just kept getting errors.

    Thanks for your help so far and in advance for any additional help.
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,885
    To use dynamic default value, code in Observer combobox AfterUpdate:

    Private Sub Observer_AfterUpdate()
    Me.Observer.DefaultValue = """" & Me.Observer & """"
    End Sub

    Open form, select observer and enter other data into record, go to next new record. The observer from previous record should already show in the combobox.
    Do similar code for any controls you want value to carry forward. For number type fields like Rank:

    Me.Rank.DefaultValue = Me.Rank

    Slight correction from my earlier statement - default values will show but if you want to create a new record, user must enter value into at least one control or run code to save record.


    Should define primary key field in SITE DETAILS table. There are multiple site records with the same site number. Why isn't Site an autonumber field?

    Advise not to use space nor punctuation/special characters (underscore only exception) in naming convention. Should not use reserved words as names for anything (Date is a reserved word). Also, all upper case is harder to read.




    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
    Rah is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5
    Thanks for all your help.

    I'm a complete novice here so a lot of what you're saying is meaning absolutely nothing to me.

    I tried adding the

    Private Sub Observer_AfterUpdate()
    Me.Observer.DefaultValue = """" & Me.Observer & """"
    End Sub

    And running my code but that didn't make any difference to what happened.

    I could set a primary key if it's necessary but I don't think it is. Typically there wouldn't be multiple records with the same site number but there just is at the moment because I've been testing and trialing trying to get my auto code to work which has created duplicate records.

    The reason the site number isn't auto (although it is somewhat auto via my button that does prefilling) is because when we're doing field work there is instances where people might do accidental double stamps or stamps for things that don't need to be recorded and there for the site is manually defined to correspond to our paper work.

    I can go through and remove spaces and reserved words as suggested in naming convention.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,885
    I have no idea what you mean by 'stamp' and 'double stamp'. Regardless, this should have nothing to do with defining a primary key field.
    Yes, you should set primary key.

    My suggestion is focused on the subform because you said you wanted to set value into Observer. If you want to repeat data for site info on main form, that is different issue. Code for setting DefaultValue could be used there as well, but if you want to set values for first new record of a session, that will require code similar to what you have.

    I changed Site to autonumber and your code to:
    Code:
        With Me
            .Date = DLookup("Date", "SITE DETAILS", "Site=" & ID)
            .Location = DLookup("Location", "SITE DETAILS", "Site=" & ID)
            .Substrate = DLookup("Substrate", "SITE DETAILS", "Site=" & ID)
            .Vessel = DLookup("Vessel", "SITE DETAILS", "Site=" & ID)
            .Heli = DLookup("Helicopter", "SITE DETAILS", "Site=" & ID)
            .Cam = DLookup("Camera", "SITE DETAILS", "Site=" & ID)
            .Gra = DLookup("Grab", "SITE DETAILS", "Site=" & ID)
            .Walking = DLookup("Walking", "SITE DETAILS", "Site=" & ID)
            .Dive = DLookup("Diver", "SITE DETAILS", "Site=" & ID)
            .Grass = DLookup("Seagrass", "SITE DETAILS", "Site=" & ID)
            .Seagrass.Form.Observer = Ob
    
    .Seagrass.Form.Observer.DefaultValue = """" & Ob & """"
    .Time.SetFocus End With

    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
    Uncle Gizmo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    22
    How about my Subform Record Duplicator?

    https://www.niftyaccess.com/subform-record-duplicator/

    I'd be happy to help install it...

    Sent from my Pixel 3a using Tapatalk

  11. #11
    Rah is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5
    Thanks June7 - I've made some tweaks to what you suggested and problem solved. Much appreciated and thanks again for all your help.

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

Similar Threads

  1. Replies: 10
    Last Post: 07-26-2018, 06:46 AM
  2. Entering data using a partial prefilled form
    By stevehoyle in forum Forms
    Replies: 28
    Last Post: 10-04-2013, 12:38 AM
  3. VBA code for creating new record
    By Naveen Marapaka in forum Programming
    Replies: 3
    Last Post: 12-23-2012, 02:13 PM
  4. Help Creating Forms & Subforms
    By tlasenby in forum Forms
    Replies: 1
    Last Post: 08-22-2012, 09:02 AM
  5. need help with prefilled form
    By Exotic in forum Forms
    Replies: 3
    Last Post: 07-08-2012, 12:10 PM

Tags for this Thread

Posting Permissions

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