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

Posted on 2014-10-07
Last Modified: 2014-10-07
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.
Question by:StudmillGuy
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40366876
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
LVL 34

Expert Comment

by:Paul MacDonald
ID: 40366898
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.

Author Comment

ID: 40366913

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 40366926

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

LVL 35

Accepted Solution

PatHartman earned 500 total points
ID: 40366962
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.

Author Comment

ID: 40366975
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.

Author Comment

ID: 40366983
In this case it was the "AllowEdits" property.  As soon as I set it to "Yes", the combo box works as expected.  Thanks Pat.
LVL 35

Expert Comment

ID: 40367006
Updates/Edits - you worked it out.  You are very welcome.

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question