Solved

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
8
2,187 Views
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.
0
Comment
Question by:StudmillGuy
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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

JeffCoachman


You can click a value, ...but it does not do anything after that
...etc
0
 
LVL 33

Expert Comment

by:paulmacd
Comment Utility
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.
0
 

Author Comment

by:StudmillGuy
Comment Utility
@Jeff

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.
0
 

Author Comment

by:StudmillGuy
Comment Utility
@Paul

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], "")
    FillOptions
    
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.
    Me![Option1].SetFocus
    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"
    Else
        While (Not (rs.EOF))
            Me("Option" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Visible = True
            Me("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

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:StudmillGuy
Comment Utility
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.
0
 

Author Comment

by:StudmillGuy
Comment Utility
In this case it was the "AllowEdits" property.  As soon as I set it to "Yes", the combo box works as expected.  Thanks Pat.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Updates/Edits - you worked it out.  You are very welcome.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Outlook Free & Paid Tools
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now