Why won't my combo box allow me to select a value on one form but it will on another?

I have a combo box that I have placed on a form.  The values come from a pass-through query.  The combo box populates with the correct values but it will not allow me to select one.  As an experiment, I created a new, blank form.  I created the same combo box on it.  It works correctly - it populates with the correct values and allows me to select one.  If I copy that same combo box to the form I want it on, it stops working.

The form that it does NOT work on is a "switchboard" form for the front end to the data.  I cannot find a "Do Not Allow Combo Boxes To Work" property on the form :).

If more backstory is required...

I'm working with data described here.  Each Access file in question has got many excellent forms and reports that are beyond my skill level to create.  When the data from all of these individual Access files is finally put in a SQL database, I am simply going to modify the underlying queries to query against the database for a particular shift.  I am putting a combo box on the switchboard form to allow the user to select the shift for which they wish to see data.  That is the combo box that populates but does not allow selection.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I created the same combo box on it.  It works correctly - it populates with the correct values and allows me to select one.  If I copy that same combo box to the form I want it on, it stops working.

So then recreating this combobox would seem to be the answer here...

Or perhaps I am missing something...

So to clarify, ...what *exactly* does or does not happen when you try to select a value?
No values appear?

If you do  this on the after update event of the combobox what happens:
msgbox me.YourComboboxName


You can click a value, ...but it does not do anything after that
Paul MacDonaldDirector, Information SystemsCommented:
Does the page refresh when you change the selection (or on any other action you might be triggering)?

I expect there's some code behind the page that's interfering with your intended operation.
StudmillGuyAuthor Commented:

When the combo box does NOT work, I click on a value from the list but when I click off of the combo box, no value is left in the combo box.

When the combo box DOES work, I select a value from the list and when I click off of the combo box the selected value remains in the combo box.

When I place your code in the After Update event of the combo box that does NOT work and I select a value from the list, nothing happens - it is as if the After Update event did not fire.

When I place your code in the After Update event of the combo box that does work (on my test form) and I select a value from the list, a Messagebox pops up with the value of the combo box.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

StudmillGuyAuthor Commented:

The page does not seem to refresh.  I immediately suspected some devious code was at work but I cannot find it.  There are a couple of bits of code that I do not understand though.

Here is the entire code for the form...

Option Compare Database

Private Sub Combo0_AfterUpdate()
    MsgBox (Me.Combo0)
End Sub

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

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

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

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

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

    ' The number of buttons on the form.
    Const conNumButtons = 8
    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.
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("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"
        While (Not (rs.EOF))
            Me("Option" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
    End If

    ' Close the recordset and the database.
    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."
        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], "")

        ' Exit the application.
        Case conCmdExitApplication

        ' 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.
On Error Resume Next
    Set rs = Nothing
    Set con = Nothing
    Exit Function

    ' 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
        MsgBox "There was an error executing the command.", vbCritical
        Resume HandleButtonClick_Exit
    End If
End Function

Open in new window

If the form does not allow updates, the combo won't either.  Open the properties sheet and check the data tab.  If AllowUpdates is set to No, that is the answer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StudmillGuyAuthor Commented:
Pat my friend!  So good of you to drop by.  I do believe you have found the problem.  There is no "AllowUpdates" property on the data tab (I'm using Access 2010), but there are "AllowAdditions", "AllowDeletions", "AllowEdits", and "AllowFilters" properties and all of those are set to "No"  .

There is also a "Filter" and "Filter On Load" is set to yes.
StudmillGuyAuthor Commented:
In this case it was the "AllowEdits" property.  As soon as I set it to "Yes", the combo box works as expected.  Thanks Pat.
Updates/Edits - you worked it out.  You are very welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.