Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15

    Access Crashing during AddNew, but only when certain string Arguments are used. No Error Code

    Hey, all, this is my first thread, and I am pretty new to VBA, so bear with me if it is a little rough around the edges.

    I am having an issue with Access crashing during an attempt to add a new record to a Recordset. The code below is similar to one used in other systems I have coded, and when I use my name as strInspector, it works properly with no problems. However, if i use any of the other inspector names (selected from a drop-down box) it crashes the program. There is no error warning, and no alert. It simply crashes and reboots the program. Any suggestions?

    Code:
    Function StoreSerial(SN, SO, RN, PN, Optional strInspector As String)
    
    If SNexists(SN) = True Then 'SNsxists returns true if SN is already in database
        Exit Function
    End If
    
    
    Dim SSc As ADODB.Connection
    Dim SSs As New ADODB.Recordset
    Set SSc = CurrentProject.Connection
    SSs.ActiveConnection = SSc
    SSs.Open "SerialNumbers", , adOpenDynamic, adLockOptimistic
    
    
    If strInspector <> "" And Not IsNull(strInspector) Then
        SSs.AddNew Array("SerialNumber", "ShopOrder", "ReleaseNumber", "PartNumber", "PrimaryInspector"), Array(SN, SO, RN, PN, strInspector) 'crashes here, but only when strInspector is certain names
    Else
        SSs.AddNew Array("SerialNumber", "ShopOrder", "ReleaseNumber", "PartNumber"), Array(SN, SO, RN, PN) 'never crashes when this one is run instead.
    End If
    
    
    CloseSS:
    Set SSc = Nothing
    SSs.Close
    SSs.ActiveConnection = Nothing
    End Function


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's a new method on me. Do the failing names have apostrophes, commas, or anything different than names that work?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Nope. I tried it with a number of different names, and even tried it with initials. Stepping into the code, I can verify that the name is passes successfully all the way up to the where the new record is added, but once I try to actually add the record, it will crash unless it is a specific name.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here to play with?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Apparently not. I am new to this, so please check my process for attaching: Click Go Advanced > Attachment > Add New > Browse
    Correct?
    I get an upload status all the way until it is completed, but nothing shows up in my "Manage Attachments"

    Update: Never Mind. it is too big to upload. Didn't realize there was a 500 KB limit.

    Below is the code I use on the form, which calls StoreSerial. Again, I am learning through experience so bare with me if I make any newbie errors in the code:

    Code:
    Function NewInspectionList(SO, RN, PN, Optional PrimeInsp As String)'Generates a list of inspections based on PN.  Identifies each inspection as linked to SN
    SN = GenerateSerialNumber(SO, RN)
    If SN = "" Or IsNull(SN) Then
        Exit Function
    ElseIf IsOpen("StandardfinalAudit") = False Then
        Exit Function
    End If
    
    
    If PrimeInsp <> "" And Not IsNull(PrimeInsp) Then
        StoreSerial SN, SO, RN, PN, PrimeInsp 'Crashes when PrimeInsp is anything other than my name
    Else
        StoreSerial SN, SO, RN, PN 'never crashes
    End If
    
    
    'Autofill Audit Form
    Forms!StandardFinalAudit!txtSerial = SN
    Forms!StandardFinalAudit!txtPN = PN
    Forms!StandardFinalAudit!txtrn = RN
    Forms!StandardFinalAudit!txtSO = SO
    Forms!StandardFinalAudit!txtDate = Date
    
    
    'If Folding, run foldinglist
    If IsFoldingBlade(PN) = True Then
        FoldingList SN, PN
        Exit Function
    End If
        
    Dim NILc As ADODB.Connection
    Dim NILs As New ADODB.Recordset
    Set NILc = CurrentProject.Connection
    NILs.ActiveConnection = NILc
    NILs.Open "Links", , adOpenKeyset, adLockOptimistic
    NILs.Filter = "PartNumber = '" & PN & "'"
    
    
    If NILs.EOF And NILs.BOF Then
        GoTo CloseNIL
    Else
        NILs.MoveFirst
    End If
    
    
    While Not NILs.EOF
        AddInspection SN, PN, NILs.Fields("Inspection")
        NILs.MoveNext
    Wend
    
    
    CloseNIL:
    Forms!StandardFinalAudit.Filter = "SerialNumber = '" & SN & "'"
    
    
    If Err <> 0 And Msg = "" Then
        ErrMsg Err.Description, "NewInspectionList"
        Err = 0
    End If
    
    
    Set NILc = Nothing
    NILs.Close
    NILs.ActiveConnection = Nothing
    End Function
    Last edited by jbrickner; 09-19-2018 at 06:23 AM. Reason: added parent code

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You may able to attach after a compact/repair and then zipping. I think that limit is 2mb.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Keep getting an error. file won't upload, but I'm not sure why.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is it under 2mb compacted and zipped? You can email it to me if it still won't attach.

    <deleted>
    Last edited by pbaldy; 09-19-2018 at 04:31 PM.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Be aware that only Variant data type can hold Null. PrimeInsp is declared as a String and can never contain Null so testing for Null is not necessary as the procedure would error long before.

    Data types must be separately and explicitly declared or variables default to Variant type. That means SO, SN, RN, PN are all Variant type.

    All code modules should have Option Explicit to force declaration of variables. This will help discover mis-typings of variables in code. The variable SN is not declared in NewInspectionList.

    NewInspectionList will perform fine as a Function, however, it could be a Sub - unless you are calling it from a macro.
    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
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Quote Originally Posted by June7 View Post
    Be aware that only Variant data type can hold Null. PrimeInsp is declared as a String and can never contain Null so testing for Null is not necessary as the procedure would error long before.

    Data types must be separately and explicitly declared or variables default to Variant type. That means SO, SN, RN, PN are all Variant type.

    All code modules should have Option Explicit to force declaration of variables. This will help discover mis-typings of variables in code. The variable SN is not declared in NewInspectionList.

    NewInspectionList will perform fine as a Function, however, it could be a Sub - unless you are calling it from a macro.
    Couple of questions/info:
    1. If the argument is optional, and is not included in the Call, wouldn't that be a null value? or would it default to a zero-length string? When I step in it says the value is "empty", so how do I check for that?

    2. SN, SO, RN, and PN are all global variables that are called out in another portion of my code. I did not include those but they are: SN (string), SO (string), RN (integer), and PN (string).

    3. I have all my modules set to Option Compare Database. I always declare, but are you saying that it would be better to change these to Explicit? Keep in mind that I have tried stepping into this code. The value holds all the way up until I try to add the new record. when I run the AddNew method, the program crashes.

    4. I use this from a macro, so it needs to be a function.

  11. #11
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    Is it under 2mb compacted and zipped? You can email it to me if it still won't attach.

    <deleted>
    It is 5 MB. I must have misread it earlier, because I was pretty sure it was smaller than that. But Compact/Zipped it is 5
    I emailed it to you, so please let me know if you find anything.
    Last edited by pbaldy; 09-19-2018 at 04:31 PM. Reason: Deleted my email address to prevent spammers getting it

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. Empty is an empty (zero-length) string. You already test for empty string just don't need to also test for Null.

    2. okay

    3. You should have both Option lines in module headers, at a minimum. There can be other declarations in header but should always be those two. Can adjust Access settings to automatically include Option Explicit. In the VBE > Tools > Options > check Require Variable Declaration.
    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.

  13. #13
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Thanks June7. I added the Require Variable Declaration. Do i need to go through an add "Option Explicit" to all my modules? Does this go on the same line as "Option Compare Database" or on a separate line?

  14. #14
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    Ok, you two. See if this sheds any light. I run the code regular, and it crashes (sometimes), but if I use the exact same values for the different variables, and step into the code, it will run all the way through. Any thoughts?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You will have to manually add to already existing modules.

    Each Option declaration on separate line.

    No idea. Have to analyze db.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Error And Crashing Access
    By kd2017 in forum Queries
    Replies: 10
    Last Post: 08-30-2017, 06:29 AM
  2. Access Crashing without and error message
    By tonygg in forum Access
    Replies: 7
    Last Post: 11-30-2015, 06:14 PM
  3. Error on AddNew to an empty recordset
    By kowalski in forum Programming
    Replies: 7
    Last Post: 05-22-2013, 06:17 PM
  4. Replies: 1
    Last Post: 11-13-2012, 07:25 PM
  5. How to code in AddNew Button In Access DBA
    By ganeshvenkatram in forum Access
    Replies: 0
    Last Post: 07-07-2011, 02:50 AM

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
  •  
Other Forums: Microsoft Office Forums