Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29

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

  1. #16
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650

    How do I recreate the error in your db?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,292
    If the argument is optional, and is not included in the Call, wouldn't that be a null value?
    It can depend on the data type. Play with the following test code:

    Code:
    Function TestOptionals(Fname As String, Lname As String, Optional varAge As Long) As String
    Dim strMsg As String, strResult As String
    
    strResult = IsMissing(varAge)
    strMsg = "First: " & Fname & "   Last: " & Lname & "   Age: " & strResult
    MsgBox strMsg
    End Function
    
    Function IsMissing(varInput As Variant) As String
    If IsNull(varInput) Then IsMissing = "Null"
    If varInput = 0 Then IsMissing = "0"
    If IsEmpty(varInput) Then IsMissing = "Empty"
    If varInput = "" Then IsMissing = "Empty String" 'equal to ""
    End Function
    If I call this as

    testoptionals "Micron","TheTinyOne"

    and vary the type in the first function (e.g. variant, long, string), I get different results (different IF lines satisfy the test, or none at all). In some cases, I would have expected variant to accept 0 (not "0") based on this good article
    http://allenbrowne.com/vba-NothingEmpty.html

    but it raised an error. Anyway, playing with the functions might show how difficult it can be to grasp the nature of the variant data type.

  3. #18
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    13
    To recreate the crash, from the main menu click Final Audits > Start New Audit > Enter Shop Order (5 digits) and Release Number (any number), and select a part number (any PN) > select any name OTHER than mine > Begin Audit. If it doesn't crash, check StoreSerial. I had to create a work-around to allow any name to be entered, so I may have left that in place on accident. Keep in mind, it does not always crash. I am starting to think it may be too much data and is crashing bc it is taking too long to add a new record.

  4. #19
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    13
    Quote Originally Posted by June7 View Post
    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.
    Turns out this may be what it needed. There were a lot of misspelled variables, and it took a while to sort them all out/make sure each module has the variables it needed to run explicit, but so far, so good. Thanks.

  5. #20
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    13
    Quote Originally Posted by Micron View Post
    It can depend on the data type. Play with the following test code:

    Code:
    Function TestOptionals(Fname As String, Lname As String, Optional varAge As Long) As String
    Dim strMsg As String, strResult As String
    
    strResult = IsMissing(varAge)
    strMsg = "First: " & Fname & "   Last: " & Lname & "   Age: " & strResult
    MsgBox strMsg
    End Function
    
    Function IsMissing(varInput As Variant) As String
    If IsNull(varInput) Then IsMissing = "Null"
    If varInput = 0 Then IsMissing = "0"
    If IsEmpty(varInput) Then IsMissing = "Empty"
    If varInput = "" Then IsMissing = "Empty String" 'equal to ""
    End Function
    If I call this as

    testoptionals "Micron","TheTinyOne"

    and vary the type in the first function (e.g. variant, long, string), I get different results (different IF lines satisfy the test, or none at all). In some cases, I would have expected variant to accept 0 (not "0") based on this good article
    http://allenbrowne.com/vba-NothingEmpty.html

    but it raised an error. Anyway, playing with the functions might show how difficult it can be to grasp the nature of the variant data type.
    This article was very helpful. I haven't messed around with the code yet, but I didn't know about the IsMissing vs. IsNull. I switched to IsMissing for all my optionals, and haven't had a problem so far. Let me know if you find any other issues with the program.

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,292
    IsMissing is a UDF (user defined function).
    Function IsMissing(varInput As Variant) As String

    A built in function such as IsNull would not be prefaced by "Function" or "Private Function" or "Public Function". While I'm at it, I'll point out that IsNull (something) is vba; Is Null is sql, not code.

    EDIT:
    Let me know if you find any other issues with the program.
    I didn't download the db, figuring I was kind of late to that party, plus you're in good hands with pbaldy. I only threw that code together as a sampler on dealing with variations of your function's optional parameter data type. I wouldn't use that for checking that variables or controls have values. For that, it would be more like (controls example)

    Code:
    Public Function IsNullEmpty(ctl As Control) As Boolean
    IsNullEmpty = False 'ensure function holds false to begin with
    If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
    End Function
    Last edited by Micron; 09-20-2018 at 11:48 AM. Reason: added info

  7. #22
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    Quote Originally Posted by Micron View Post
    you're in good hands with pbaldy
    I appreciate the kind words, however misplaced they may be.

    Feel free to download, I've been pretty busy with a project for our Vegas office and haven't been able to look at this.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    Download what from where?

    As for item 4 in post 10 - why use macro and not just stick with VBA? I don't use macros. For one reason, macros are harder to debug. Instead of event calling macro which calls function, a function can be called directly from event property. Or VBA event sub calls sub/function.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  9. #24
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    Sorry, forgot this was the thread where the file was too big and jbrickner emailed it to me. Not mine to forward, so perhaps he can send it to others?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,973
    I don't provide email. If can't attach to post after reducing, compacting, zipping, then upload to a fileshare site such as Box.com and post link.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  11. #26
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    13
    No worries all, The issue has been solved. Using Option Explicit helped me to isolate a number of misspelled errors which were bogging down the program. Once it reached the point where the incomplete data accumulated too much, adding a new record was more than the program could handle. I have tested this on someone else's screen and everything worked great. Thank you for the help.

    June7, I use macros because this database is on a network. When I use class modules, and they try to run the program on the other side of town, it slows the program down so much that it won't function at all. VBA gets the job done, but I have to call the code in a macro in order to streamline it.

  12. #27
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    13
    Quote Originally Posted by Micron View Post
    IsMissing is a UDF (user defined function).
    Function IsMissing(varInput As Variant) As String

    A built in function such as IsNull would not be prefaced by "Function" or "Private Function" or "Public Function". While I'm at it, I'll point out that IsNull (something) is vba; Is Null is sql, not code.

    EDIT:
    I didn't download the db, figuring I was kind of late to that party, plus you're in good hands with pbaldy. I only threw that code together as a sampler on dealing with variations of your function's optional parameter data type. I wouldn't use that for checking that variables or controls have values. For that, it would be more like (controls example)

    Code:
    Public Function IsNullEmpty(ctl As Control) As Boolean
    IsNullEmpty = False 'ensure function holds false to begin with
    If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
    End Function
    In the article that you provided a link to, IsMissing() was used to determine if an optional argument is used. This allowed me to remove the
    Code:
     If (variable) <>"" and not IsNull(variable) Then
    and replace it with
    Code:
    if IsMissing(variable) = false then
    This, along with the option explicit are the only things I changed, and it worked.

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,292
    glad we could help
    Good luck going forward.

  14. #29
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,650
    Glad you got it sorted. Sorry to have dropped the ball. I was on a project yesterday and on the road to visit a daughter all day today.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Page 2 of 2 FirstFirst 12
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, 05:29 AM
  2. Access Crashing without and error message
    By tonygg in forum Access
    Replies: 7
    Last Post: 11-30-2015, 05:14 PM
  3. Error on AddNew to an empty recordset
    By kowalski in forum Programming
    Replies: 7
    Last Post: 05-22-2013, 05:17 PM
  4. Replies: 1
    Last Post: 11-13-2012, 06:25 PM
  5. How to code in AddNew Button In Access DBA
    By ganeshvenkatram in forum Access
    Replies: 0
    Last Post: 07-07-2011, 01: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
  •  
Tech Forums: Microsoft Office Forums