Results 1 to 14 of 14
  1. #1
    timesscript is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    34

    Post Go to new record on form load doesn't get there when fields are set in following code

    I have a small form that on load I want to go to a new record and set some fields to certain values.



    This code works fine:
    Code:
    Private Sub Form_Load()
        DoCmd.GoToRecord , , acNewRec
    End Sub
    but as soon as I modify it so:
    Code:
    Private Sub Form_Load()
        DoCmd.GoToRecord , , acNewRec
        Applied = False
    End Sub
    Instead of making a new record and setting the value of [Applied] to False, it will go to some record in the middle, and set the value to false, apparently. If I try to save, it says that the changes were not successful because they would set duplicate values (Which is true since I have 50+ records and the primary key it is showing is 25 or so, so obviously the same as one existing)

    Also, if I let this second code run, and then hit escape, it will then jump to a new record.

    How should I fix this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Applied is a yes/no field you want to set to False?

    Do this in the OnCurrent event.

    If Me.NewRecord Then Me!Applied = False

    Or maybe better, just set the DefaultValue property of control bound to Applied. No code.
    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
    timesscript is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    34
    I just tried that, having On Load only run DoCmd.GoToRecord , , acNewRec
    And having On Current assign the values of fields. It does the same thing, when I run it, it doesn't end up at a new record.

    Below is how the form looks when it runs:
    Click image for larger version. 

Name:	form.PNG 
Views:	11 
Size:	9.2 KB 
ID:	20992
    It appears to be at a new record, but you can see from the field "ID" (which is the primary key) that somehow the primary key is being autofilled with an earlier record.

    The fields that are being set do not exist in the form- they are in the background. Maybe that is my problem?

    Thanks.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Looks like form is set to not allow new records. What is the AllowAdditions property set to?

    Fields must be in the form's RecordSource but do not need to be displayed on form.
    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.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by timesscript View Post
    ...And having On Current assign the values of fields...
    What code did you use in the On Current event? I would use the statement June provided.

  6. #6
    timesscript is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    34
    Full disclosure is that I have more than one field being set, but it doesn't matter- if I only have one the same behavior occurs. With that out of the way, here is On Current:
    Code:
    If Me.NewRecord Then    
         Applied = False
        [App_StudentCommitment] = "unknown"
        [LeftSchool] = False
        [Graduated] = False
        Me!
    [List2] = "Inquirer"
    End If
    And on load - simply to get me to a new record:
    Code:
     DoCmd.GoToRecord , , acNewRec

  7. #7
    timesscript is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    34
    Fields are in the record source
    Allow Additions, deletions, edits, etc. are all yes

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You are getting strange behavior so it would probably be a good idea to make sure and work through that. I would try this in the On Current.

    If Me.NewRecord Then
    Me!Applied.Value = False
    End If

    And try to get it to work. If it does not, I would start looking into maybe getting rid of the module, compact and repair, and rebuilding all of the sub procedures.

    If it does work with the example code. I would, then, get rid of the code in the On Load and change the form's Data Entry property to Yes.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Odd behavior is often indicative of corruption. Sometimes C&R will fix. Sometimes nothing but rebuilding will fix.

    If you want to provide db 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.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    How much VBA do you have in the Module? Compact and repair is not going to solve this alone. If it was my DB and what you are describing start to happen to me, I would suspect a corrupt form. I would try to fix it by copying all of my VBA into notepad and then delete the module. Then I would rebuild all of the Sub Procedures, one by one.

    You can permanently remove the module by going to the form's properties and adjusting the property, "Has Module" to equal No. This will permanently delete all of your VBA for that form. After I saved the form with the new property setting, I would compact and repair the database.

    You can compact and repair your DB from the Ribbon, under Database Tools.

    When you go to paste your VBA back into your new module. You want to do it one sub procedure at a time. Use the intrinsic tools to create sub procedures for events like click events and paste your code in between.

    Do another compact and repair and see if that works for you.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Another thing to consider is how many controls you placed on your form. I did not think of this earlier. But, there is a limit and sometimes adding and deleting a bunch of controls can cause issues. If I spend a lot of time developing one form, I will periodically check how many controls I have placed on a form. I do this by adding a new control to the form and seeing what the default name is. I look at the number within the name and if it is over 100 I will start considering copying my controls to a new form. This is in addition to recreating the VBA (copying a control does not copy the VBA). Any form of substantial size, I will rebuild it before distributing it by copying my finished work to a new blank form.

  12. #12
    timesscript is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    34
    It is a very simple form, basically just 3 fields, a listbox and a couple buttons. So first I redesigned from scratch in the same access file. It did the same thing. I made a new database and tried it, and it worked fine. So I compacted and repaired, and now everything works. Hmm.

    Well, I'm glad it worked! Thanks to both of you for your help

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Glad things worked out for your without much hassle. Moving forward, while building VBA within modules, keep track of edits made in other modules and whether or not those edits have been saved. When you save your work, you want to avoid seeing the little dialog window that pops up that asks if you want to save changes in other forms, reports, etc. Avoid getting the prompt to "Save All" by saving your work before moving to another form, report, etc.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I presume there are other fields user will be inputting data into for new record.
    Use the DefaultValue property of controls and the code is not needed.
    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.

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

Similar Threads

  1. Replies: 12
    Last Post: 05-14-2015, 03:48 PM
  2. Form doesn't load when on server
    By missypooh1969 in forum Forms
    Replies: 7
    Last Post: 09-03-2014, 02:03 PM
  3. Replies: 2
    Last Post: 05-20-2014, 09:32 AM
  4. Replies: 8
    Last Post: 07-27-2012, 12:23 PM
  5. Replies: 3
    Last Post: 08-23-2011, 04:35 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