Overflow Error

[jtm013]

Hello,



I have a module which inconsistently is generating an error message that pops up and states "overflow".
Any assistance is appreciated.

Public Function EmBypass()
Dim strAdminPWord As String
Dim strAdminOride As String
strAdminPWord = InputBoxDK("Enter Authorization Code", "Emergency Bypass")


If strAdminPWord = DLookup("Pwd", "Tbl_Log_In_Data", "EmpNumber = 0001") Then
    DoCmd.OpenForm "frmDemo"
        [Forms]![frmDemo]![chkLogOutAllUsers] = False
        [Forms]![frmDemo]![chkShowMe] = False
            DoCmd.Close acForm, "frmDemo"
                MsgBox "Shutdown Aborted!", vbOKOnly, "Bypass Successful"
                strAdminOride = InputBoxDK("If you would like be taken to the administrative controller type 'Admin'." & vbCrLf & _
                "If you would like to return to the Hub type 'Hub'." & vbCrLf & _
                "If you would like to remain on your current screen, click Cancel.", "Input Required")
    
 
Else
    MsgBox ("You entered an invalid password")
   ' Exit Sub
End If


If strAdminOride = "" Then
    MsgBox "Invalid Password!"
    ElseIf strAdminOride = "admin" Then
        DoCmd.OpenForm "frmDemo"
        ElseIf strAdminOride = "hub" Then
            DoCmd.OpenForm "frmSwitchboard"
End If
        
        
    
End Function

[June7]

Have you step debugged? Which line throws the error?

strAdminPWord is declared as string. If the DLookup does not find a match it returns Null. Cannot compare to Null http://allenbrowne.com/casu-12.html. Handle possible Null. Try:

If strAdminPWord = Nz(DLookup("Pwd", "Tbl_Log_In_Data", "EmpNumber = 0001"), "") Then

[Missinglinq]

There's also a problem with this:

"EmpNumber = 0001"

That syntax is only valid if EmpNumber is defined as a Number, but the leading Zeros would seem to indicate that it's actually defined as Text. If it is, in fact, defined as Text (which data that consists of only digits, but is not used for math, frequently is) then the syntax, for the literal value 0001, needs to be

"EmpNumber = '0001'"

Linq ;0)>

[June7]

Ooops, good catch Linq. I meant to mention that and show in my suggestion.

[jtm013]

Have you step debugged? Which line throws the error?

strAdminPWord is declared as string. If the DLookup does not find a match it returns Null. Cannot compare to Null http://allenbrowne.com/casu-12.html. Handle possible Null. Try:

If strAdminPWord = Nz(DLookup("Pwd", "Tbl_Log_In_Data", "EmpNumber = 0001"), "") Then

June7 - Right, I added Nz( as you recommended, I am still getting the overflow error.
I have step debugged but the overflow error is thrown inconsistently.
The overflow error is only occurring 2 times in 10, so it is not consistent.
What I don't understand is how that can be, as the bypass (this is my bypass command to override my maintenance shutdown)
is only accessible to one user (myself) and is only tied to a single employee number (mine) and will only ever have one code
entered into it (also mine) so I don't understand what the variation is that causes the inconsistency.

There's also a problem with this:

"EmpNumber = 0001"

That syntax is only valid if EmpNumber is defined as a Number, but the leading Zeros would seem to indicate that it's actually defined as Text. If it is, in fact, defined as Text (which data that consists of only digits, but is not used for math, frequently is) then the syntax, for the literal value 0001, needs to be

"EmpNumber = '0001'"

Linq ;0)>

This is my bad, the actual code does not say "= 0001" it says my employee number so I just swapped it out with 0001 before posting.
The actual number is a number, in number format, and does not have leading zeros. Sorry for the misleading information.

---

I have attached a copy of the DB for in context review. I set the admin EmpNumber to 1001 and password to password for simplicity's sake.
The code above is in the module 'modEmBypass' The code is activated by clicking the hidden button on the maintenance logout notification popup, form name 'frmLogoutStatus'. 'frmDemo' is the admin control panel from which the maintenance shutdown can be triggered.

Thanks for the continued assistance I always appreciate the new knowledge I gain from this forum and the members on it!

[June7]

The Employee table is a link so if any code involves that table, cannot test.

Why duplicate employee name in Tbl_Log_In_Data?

I had to modify the API function declarations to run on my computer. Still get error for:
hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

I clicked the Shut it down! button on frmDemo and get error: "Continuous Improvement Database can't find the field 'Check96' referred to in your expression."

[jtm013]

The Employee table is a link so if any code involves that table, cannot test.

This should not be the problem. The only thing that the employee table does is populate a dropdown box on the Suggestion Form.

Why duplicate employee name in Tbl_Log_In_Data?

Tbl_Log_In_Data has a first and last name field, usually the Admin slot is populated with my first and last name, I just switched it to Admin,Admin for simplicity.

I had to modify the API function declarations to run on my computer. Still get error for:
hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

I don't know what would cause this, sorry. I use Access 2013, I'm not sure if that would make a difference.

I clicked the Shut it down! button on frmDemo and get error: "Continuous Improvement
Database can't find the field 'Check96' referred to in your expression."

Apologies, forgot to change the control name in the onclick event before posting. (When I made my OP, I realized I had not given that check box a proper name and apparently when I did so, I missed one reference before uploading.) I have attached a copy of the DB to this post with this problem fixed.

Thank you for the continued support and assistance!

[June7]

That's not what I meant by duplicating names. Saving names in Tbl_Log_In_Data duplicates data already in Employee table. The EmpNumber is in Tbl_Log_In_Data and should not need names as well.

Now I click the Shut it down! button on frmDemo and the form closes. Doesn't run the procedure shown in OP, which, by the way, is declared as Function TEST() in the db - Function EmBypass() does not exist.

Need to give specific guidance on how to replicate the error.

[jtm013]

That's not what I meant by duplicating names. Saving names in Tbl_Log_In_Data duplicates data already in Employee table. The EmpNumber is in Tbl_Log_In_Data and should not need names as well.

Oh, that. In short I agree.
However the linked employee table (which is kept up to date by HR) is stored in a DB which I am not authorized to edit/touch/etc.
It took enough convincing to get HR to let me link it to my DB so that users would have a full and complete drop down list of
employee names on the suggestion form.
Subsequently, I needed a table in which I could store the authorized user list for this db.
(The function of this db is to serve as an electronic suggestion box, any employee can leave a suggestion,
but only authorized users can review the suggestions and take further action hence the reason for having a log in,
and thus needing a place to store the log in information.)

Now I click the Shut it down! button on frmDemo and the form closes. Doesn't run the procedure shown in OP, which, by the way, is declared as Function TEST() in the db - Function EmBypass() does not exist.

Need to give specific guidance on how to replicate the error.

In order to replicate the error as it would occur 'naturally'

1. On frmDemo- check box next to "I am prepared" must be checked, then click "Shut it down!".
2. frmDemo will close,
3. then between 5-20 seconds later (depending on where the timer was at time of clicking) an irritating stripped popup will display alerting you that the database will shutdown for maintenance in 1 minute.
4. There is a picture on the left hand side of this irritating popup, beneath it there is a hidden button- click it.
5. An input box asking for the bypass code will appear- when this input box is displayed type "password"
6. After this it should say "shutdown aborted!"
7. A msgbox shows up asking if you want to go back to frmdemo, the switchboard, or remain on the current screen.

I have had the error occur during both step 6 and 7.

Shortcut version to replicate error-

1. Open frmLogoutStatus
2. Click the aforementioned hidden button
3. enter "password" when prompted
4. It should say "shutdown aborted!"
5. A msgbox will show up.

Same as above the error could occur during either of the last two steps.

[June7]

This process involves the hHook code that triggers error for me. I had to comment out that line so the code would compile. I do still get the Overflow error. I OK through that popup and do get the "shutdown aborted" popup. I also eventually get "invalid password" popup.

What is purpose of the API functions?

[jtm013]

This process involves the hHook code that triggers error for me. I had to comment out that line so the code would compile. I do still get the Overflow error. What is purpose of the API functions?

The API functions are part of a code I picked up, the sole purpose of which is to provide a password input mask on the input box that pops up asking for the bypass code. I know I had to adjust the References in order to get it to work right.
I show that VBA is checked, Microsoft Access 15.0 Object Library is checked, OLE Automation, Microsoft DAO 3.6 Object Library, and Microsoft ActiveX Data Object 2.1 Library are all also checked as references.

I OK through that popup and do get the "shutdown aborted" popup. I also eventually get "invalid password" popup.

That is a new combination for me. If the shutdown is aborted, then you should not be able to get an "invalid password" notification.
I wrote it as an If Then Else statement so I don't see how both could occur.

[June7]

Possibly because I had to comment out the hHook = line to get the code to compile. My Access doesn't like the AddressOf NewProc. Seems I've run into this before for another poster.

[jtm013]

Possibly because I had to comment out the hHook = line to get the code to compile. My Access doesn't like the AddressOf NewProc. Seems I've run into this before for another poster.

Okay so I checked to see what would happen if I completely disregarded the entirety of the code which allows for an input masked inputbox.
I noticed two things:

1. My problem with the overflow error, is gone. So whatever the cause is, is related not to Public Function Test(), but to the mass of code that allows InputBoxDK to be input masked.
2. Without fail now that I have removed the input mask code, I get the "shutdown aborted" and "invalid password" combo. However, I think this is due to a shortcoming of my code.

strAdminOride = InputBox("If you would like be taken to the administrative controller type 'Admin'." & vbCrLf & _
                "If you would like to return to the Hub type 'Hub'." & vbCrLf & _
                "If you would like to remain on your current screen, click Cancel.", "Input Required")

First This section shows up allowing user to direct themselves to listed locations, but...

If strAdminOride = "" Then
    MsgBox "Invalid Password!"
    ElseIf strAdminOride = "admin" Then
        DoCmd.OpenForm "frmDemo"
        ElseIf strAdminOride = "hub" Then
            DoCmd.OpenForm "frmSwitchboard"

I believe that If strAdminOride = "" Then section of code is what is causing the "invalid password" to be displayed upon cancel click.
I'm not sure why it did not do that with the InputBoxDK code sections in place. Hmmm puzzling.

As always thanks for the continued assistance. I know I am sort of stumbling along in my knowledge of access, but I think I am making good progress.

[June7]

API functions can be tricky.

An alternative is a form instead of InputBox. Passing value between forms has its challenges but eliminates the troublesome API code.

[jtm013]

API functions can be tricky.

An alternative is a form instead of InputBox. Passing value between forms has its challenges but eliminates the troublesome API code.

Right, the only reason I really cared about input masking the inputbox was because I am currently using my password as the override code and when inevitably some manager calls me to their desk to over ride the logout I didnt want it displayed on the screen.

I think since the APIs are causing problems I will just make up an independent password/code thing that will serve as the bypass and then just do away with the input mask. It will be visible to the user if I have to type it in at their terminal, but as long as I make a habit of rotating it out I don't foresee it causing too much of a problem.


Thanks for assistance. I will go ahead and mark this solved.