How do I recreate the error in your db?
How do I recreate the error in your db?
It can depend on the data type. Play with the following test code:If the argument is optional, and is not included in the Call, wouldn't that be a null value?
If I call this asCode: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
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.
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.
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.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.
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.It can depend on the data type. Play with the following test code:
If I call this asCode: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
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.
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)Let me know if you find any other issues with the program.
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
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.
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?
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.
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.
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 theIsMissing 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
and replace it withCode:If (variable) <>"" and not IsNull(variable) Then
This, along with the option explicit are the only things I changed, and it worked.Code:if IsMissing(variable) = false then
glad we could help
Good luck going forward.
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.
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)
Example (Query - Same Function as above)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
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