Switchboard error (1 Viewer)

Mining4God

New member
Local time
Today, 12:31
Joined
Feb 16, 2021
Messages
8
Hello all. Complete newb here working on an old database. It's been working fine until today.

When I open the database, it automatically goes to this view:
RU Main.jpg


The problem occurs when in the date field on the left. If I click in it...and the small calendar appears...and hit enter whether I change the date or not, I get this error:
debg.jpg


Clicking on Debug takes me into VBA, to the Switchboard form with the following code. I have bolded the area that the program highlights. I did try researching first, and made sure that Tools>References pointed to the current ADO. No clue how to fix this. Thank you for any and all help.


Option Compare Database

Private Sub Form_Load()
RUDate.Value = Date
End Sub

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

On Error GoTo Form_Open_Err

' Minimize the database window.
DoCmd.SelectObject acForm, "Switchboard", True
DoCmd.Minimize

' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

Form_Open_Exit:
Exit Sub

Form_Open_Err:
MsgBox Err.Description
Resume Form_Open_Exit

End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 10

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me.Controls("Option" & intOption).Visible = False
Me.Controls("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rs.EOF))
Me.Controls("Option" & rs![ItemNumber]).Visible = True
Me.Controls("OptionLabel" & rs![ItemNumber]).Visible = True
Me.Controls("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function

Private Sub SDatecmd_Click()
DoCmd.OpenForm "SelectDate"
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
on the Error Msg, choose Debug.
Press F8 and note on which part of the code does have fault.
post "the part" of the code here.

it would also be easier if you can put your db in .zip and
attached the .zip.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:31
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

You can fix the code, but maybe you could try the switchboard manager first to check if everything is okay.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
i think the problem is in FillOption?
did you add new button? and forget to pass the buttonNumber (intBtn).

when you click on SDate, a form comes up.
maybe that's is where the error originated.
maybe it is calling FillOption (only this part of code
has "that" record source).
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:31
Joined
Sep 12, 2006
Messages
15,613
You have to be careful how you edit the switchboard. The menu options get filled on screen a page at a time from a switchboard items table using "filloptions" The options then get actioned according to the "HandleButtonClick" event.

Your button may work for the first page, but it probably won't work for the next page. Note that you will also likely to get issues if you don't have anything in button 1 on the switchboard.

You can add a static button that always shows does something with a date, but then you need an independent button click handler for the button, as it probably isn't bound to the active page of switchboard items.

The header of the buttonclick event is this, from the code you posted

Private Function HandleButtonClick(intBtn As Integer)

So the click event for the button should be set to, say "HandleButtonClick(9)"

So in this case IntBtn is 9, and it looks to the active page of switchboard items to determine what it should do with switchboard item no 9. (using the numeric action column, and the argument column - so for example action 3 is to open the form named in the argument column). That's the code line that's failing for you, as it doesn't have an action button number. But it will still fail if the active page of buttons doesn't have an entry for item 9.

When the options get filled the code will load in data from the Switchboard items table for however many option buttons you have, and it will therefore do something different with button 9 each time. The switchboard items table works with the form to update the options as you change pages of the switchboard. You can't have a static option.

Well you can, but it needs to be a standalone button, so that it's not part of the switchboard menu, per se.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 16:31
Joined
Sep 21, 2011
Messages
14,044
You have no value for SwitchBoardID ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:31
Joined
Feb 19, 2002
Messages
42,970
I hope you're not trying to run it on a cloud drive. Access databases are NOT multi-user unless they are on a LAN drive.

There may be corruption. C&R. Check for compile errors. Decompile. Repeat. If you still get the error, export everything to a new, empty database.
 

Mining4God

New member
Local time
Today, 12:31
Joined
Feb 16, 2021
Messages
8
It resides on my personal Google Drive. I'm the only user. I have tried to compile it, but will try again.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:31
Joined
Sep 12, 2006
Messages
15,613
Sorry.

I can't reproduce the issue with the date control. There are no event handlers in the date on the left. Nothing is happening.

However, if I press page-down, it tries to load a different menu page, and that does give me the error you mentioned, in both A2003 and later.
I can't work out how it responds to the page-down button. I can't get this effect with other key presses. Your switchboard actually has 10 buttons, but you only show 8. I tried populating buttons and 9 and 10, but it still did the same thing. My switchboards don't respond to page-down. (page-down appears to be a valid response in the popup calendar. I don't know if that is significant. You don;t get a popup calendar in A2003, and it still does the same thing to page-down.

Now, when I check your switchboard code, it's using ADO record sets in the filloptions and handlebuttonclick events. Mine uses DAO record sets, and mine is completely standard, so I imagine your switchboard has been changed by someone. in the past.

The DBS author is shown in the database properties. Does the author and company shown mean anything to you?

Maybe someone can identify what page-down does.

edit. I checked the cycle records property, the binding of the form, and other things against one of my switchboards, but I can't see a difference.
In passing, you don't have option explicit in your modules, and your database won't compile, so maybe some variable gets declared by default as a variant and therefore might be null, which might cause an issue. Maybe page-down makes it try to get to page null, rather than just a non-existent page 0.

edit again. Pressing page-down definitely tries to load a non-existent option page and causes the problems. I can't see how page-down is being allowed through as a key-stroke though.


edit. GOT IT

your form is set to data entry = yes.

You need it to be set to

allow edits = yes 'yes or you won't be able to change the date., I think.
allow additions = no 'doesn't really matter, but it should be No
allow deletions= no 'doesn't really matter, but it should be No
data entry = No 'THIS is the issue. Page-down, and maybe enter is being treated as making a new record, which completely messes up the switchboard navigation. Change it to No.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:31
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure if this helps, but give it a try...

Edit: Attachment deleted per OP request.
 
Last edited:

Mining4God

New member
Local time
Today, 12:31
Joined
Feb 16, 2021
Messages
8
theDBguy

I downloaded that and unzipped it to my desktop. With one little exception, it works perfectly.

I also tried to move it to my Google Drive and operate it from there like I typically do, and the exact same errors listed above came up. What's up with that?!

Anyway, the one thing that isn't working right goes like this. If I click the button for Reports and then Director Reports and then the top button for BAM, a small window asks for the group ID I want the result for. It used to be that clicking the BAM button would produce a report of all groups results compiled nicely on one report. I never had to stipulate which group. (I can live with this, but it will add some time to my reporting.)

By the way, nice fix! What was the deal?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:31
Joined
Oct 29, 2018
Messages
21,358
theDBguy

I downloaded that and unzipped it to my desktop. With one little exception, it works perfectly.

I also tried to move it to my Google Drive and operate it from there like I typically do, and the exact same errors listed above came up. What's up with that?!

Anyway, the one thing that isn't working right goes like this. If I click the button for Reports and then Director Reports and then the top button for BAM, a small window asks for the group ID I want the result for. It used to be that clicking the BAM button would produce a report of all groups results compiled nicely on one report. I never had to stipulate which group. (I can live with this, but it will add some time to my reporting.)

By the way, nice fix! What was the deal?
This one got rid of that error, but the report is empty for me. Are you using a different data file?

Edit: Attachment deleted per OP request.
 
Last edited:

Mining4God

New member
Local time
Today, 12:31
Joined
Feb 16, 2021
Messages
8
No. This tracks the participation and attendance at our local addiction recovery program. I'm the director. We only meet on Fridays, and our data is always entered a week late. If you run it for 2/5/2021, you'll get data in the report.

I just tested it. PERFECT! I don't know what you did, but thank you, thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:31
Joined
Oct 29, 2018
Messages
21,358
No. This tracks the participation and attendance at our local addiction recovery program. I'm the director. We only meet on Fridays, and our data is always entered a week late. If you run it for 2/5/2021, you'll get data in the report.

I just tested it. PERFECT! I don't know what you did, but thank you, thank you!
Ah, okay. I'm not in front of a computer now, so will post details on what I changed later.

Sent from phone...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
as i have said it is in FillOptions sub.
you need to change this code:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & [SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset


To:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Nz(Me![SwitchboardID], 0)
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
 

Mining4God

New member
Local time
Today, 12:31
Joined
Feb 16, 2021
Messages
8
This one got rid of that error, but the report is empty for me. Are you using a different data file?
Do you mind deleting the two zip files that you've added to this thread? They contain personal information. Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:31
Joined
Oct 29, 2018
Messages
21,358
Hi guys.

I'm sorry, but I'm back. We had our meeting tonight, and I noticed that there is a field that is broken. I think it happened when you fixed the report, theDBguy.

Anyway, I'm uploading the zipped file for you. To see the broken field, click the top button "Students" when you open the database. Then, about halfway down you'll see a field for "Group" but it says "#Name?" and I can't change it. Every student is in a group, so this is critical for my records.

Thank you again!
Hi. It's a bit late here now. I'll take a look at it tomorrow.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:31
Joined
May 7, 2009
Messages
19,169
see if it is fixed.
 

Mining4God

New member
Local time
Today, 12:31
Joined
Feb 16, 2021
Messages
8
Hi Arnelgp,

It's 99% fixed. I ran through all the reports, and some now ask for "GroupID" but they never asked for it before, but I can live with that. It appears that I can input any valid group id, and the report will generate for all the groups. Odd, but functional.

I'm happy with it.

Thank you for helping us out. This board has been wonderful to work with.

Nat

PS - Please delete the database as it contains personal info like addresses.
 

Users who are viewing this thread

Top Bottom