Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How do I recreate the error in your db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    15
    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
    15
    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
    15
    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
    12,737
    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 12:48 PM. Reason: added info

  7. #22
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 2007-2019
    www.BaldyWeb.com

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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.
    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.

  9. #24
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 2007-2019
    www.BaldyWeb.com

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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.
    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.

  11. #26
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15
    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
    15
    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
    12,737
    glad we could help
    Good luck going forward.

  14. #29
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 2007-2019
    www.BaldyWeb.com

  15. #30
    jbrickner is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    15

    Update: Final Solution

    With all the fixes I was given here, I found the root cause and a solution that fixed everything. I wanted to post it so anyone that has this same issue can have a solution too:

    -Problem: Transferring data from one table to the next using recordsets. This works great if there are only a few hundred records in the new table, but when you get to the higher quantities (mine was over 70,000 records), even filtering these records does not speed up the process. When you pile several recordsets on top of each other (different recordsets running in different routines) it bogs the system down. Add in the "little bits" of variable data that get left over each time you run the code and it will lock up the system. Compact/repair works for a little while (since it gets rid of the excess "little bits") but it will crash again sooner or later.

    -Solution: Use an append query to add data to the new table, and a delete query to remove data from the old table. You can do this easily by creating the query you want to run in QBE, then switching to SQL view and copy-pasting the string into your code. Make sure you do the necessary modifications to the string so it runs in VBA. Also, if you don't want a warning to pop up every time you add/delete data using this code, you can add in "docmd.setwarnings false". Just make sure you set them back to true when you are done.

    Example (recordset)
    Code:
    Function ArchiveAudit(SN)
    'Move all inspections for SN to InspectionArchive table to maintain efficient system flow
    Dim AAc As ADODB.Connection
    Dim AAs As New ADODB.Recordset
    Set AAc = CurrentProject.Connection
    AAs.ActiveConnection = AAc
    AAs.Open "CompletedInspections", , adOpenDynamic, adLockOptimistic
    AAs.Filter = "SerialNumber = '" & SN & "'"
    
    
    If AAs.EOF And AAs.BOF Then
        GoTo CloseAA
    Else
        AAs.MoveFirst
    End If
    
    
    Dim count As Integer 'Used to convert null string to ""
    Dim ttlFields As Integer: ttlFields = AAs.Fields.count - 1 'fields minus 1 accounts for index 0
    
    
    While Not AAs.EOF
        count = 0
        For count = 0 To ttlFields
            If AAs.Fields(count).Type = adVarWChar And IsNull(AAs.Fields(count)) Then
                AAs.Fields(count) = ""
            End If
        Next count
        AAs.Update
        AddTransfered SN, AAs.Fields("PartNumber"), AAs.Fields("InspectionName"), AAs.Fields("Acceptable"), AAs.Fields("IncludeInReport"), AAs.Fields("Override"), "InspectionArchive", AAs.Fields("Measured"), AAs.Fields("ApprovedBy"), AAs.Fields("Documentation"), AAs.Fields("Comments")
        If ArchiveSuccessful(SN, AAs.Fields("InspectionName")) = True Then
            AAs.Delete adAffectCurrent
            AAs.Update
        Else
            Debug.Print SN
        End If
        AAs.MoveNext
    Wend
    
    
    CloseAA:
    If Err <> 0 Then
        ErrMsg Err.Description, "ArchiveAudit"
        Err = 0
    Else
        RecordArchived SN, True
    End If
    
    
    Set AAc = Nothing
    AAs.Close
    AAs.ActiveConnection = Nothing
    End Function
    Example (Query - Same Function as above)
    Code:
    Function ArchiveAudit(SN)
    'Move all inspections for SN to InspectionArchive table to maintain efficient system flow
    Dim ArchSql As String
    Dim DelSql As String
    ArchSql = "INSERT INTO InspectionArchive ( SerialNumber, PartNumber, InspectionName, InspStyle, InspDesc, Nominal, PlusTolerance, MinusTolerance, Measured, Acceptable, Override, ApprovedBy, Documentation, Comments, IncludeInReport )"
    ArchSql = ArchSql & " SELECT CompletedInspections.SerialNumber, CompletedInspections.PartNumber, CompletedInspections.InspectionName, CompletedInspections.InspStyle, CompletedInspections.InspDesc, CompletedInspections.Nominal, CompletedInspections.PlusTolerance, CompletedInspections.MinusTolerance, CompletedInspections.Measured, CompletedInspections.Acceptable, CompletedInspections.Override, CompletedInspections.ApprovedBy, CompletedInspections.Documentation, CompletedInspections.Comments, CompletedInspections.IncludeInReport"
    ArchSql = ArchSql & " FROM CompletedInspections"
    ArchSql = ArchSql & " WHERE (((CompletedInspections.SerialNumber) = '" & SN & "'))"
    
    
    DelSql = "DELETE CompletedInspections.*, CompletedInspections.SerialNumber"
    DelSql = DelSql & " FROM CompletedInspections"
    DelSql = DelSql & " WHERE (((CompletedInspections.SerialNumber) = '" & SN & "'))"
    
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL ArchSql
    DoCmd.RunSQL DelSql
    DoCmd.SetWarnings True
    End Function

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, 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