Results 1 to 4 of 4
  1. #1
    aexsar is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2017
    Posts
    2

    ComboBox not updating values

    I've run into a snag trying to load values from a table into a combo box so the user can select from it. My first try attempted to do this at Form_Open, which consistently threw a 2474 error at
    Code:
    Screen.ActiveControl.RowSource = Screen.ActiveControl.RowSource & """ & Employee_line & "";"
    Further reading led me to believe that the control was not available for accessing at Form_Open, so my next try was a BeforeUpdate event for the combo box itself. I receive no error, but I get no values loaded into the combo box when I click on it (I've checked to ensure I have entries in the Employee table). Code is below, this is my first foray into MSACCESS and if someone has any insight I'd greatly appreciate it.

    Code:
    Private Sub Employee_List_BeforeUpdate(Cancel As Integer)
    Dim CAT_record As ADODB.Recordset 'Declares a recordset handle to hold data from Employee table
    Set CAT_record = New ADODB.Recordset 'Initializes CAT_record recordset
    CAT_record.ActiveConnection = CurrentProject.Connection 'Binds the recordset handle to the existing connection
    CAT_record.CursorType = adOpenStatic 'Only pull records as they are now (not dynamic)
    CAT_record.Open "Select * from Employee ORDER BY 'Lname'" 'Pull records from Employee Table, sort by last name
    
    DoCmd.GoToControl "Employee_List" 'Set focus to combo box for names
    Do Until CAT_record.EOF 'Loop through all Employee table record
    Employee_line = CAT_record("Lname") & ", " & CAT_record("Fname") & " " & CAT_record("Grade") 'Create new entry to choose from
    Screen.ActiveControl.RowSource = Screen.ActiveControl.RowSource & """ & Employee_line & "";" 'Append to list in combo box
    CAT_record.MoveNext 'Access next record
    Loop 'Repeat until End Of File (EOF)
    
    CAT_record.Close 'Close the recordset
    Set CAT_record = Nothing 'Unbind the handle
    End Sub


  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Normally you bind the combo box row source to a table, query or sql statement as part of the form design.
    If for any reason this isn't possible, use the Form Load event

  3. #3
    aexsar is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2017
    Posts
    2
    Quote Originally Posted by ridders52 View Post
    Normally you bind the combo box row source to a table, query or sql statement as part of the form design.
    If for any reason this isn't possible, use the Form Load event
    Form_Load gives a 2474 error as well, at the same spot.
    Code:
    Screen.ActiveControl.RowSource = Screen.ActiveControl.RowSource & """ & Employee_line & "";" 'Append to list in combo box
    My preference was to have one column for the box containing an aggregation of three separate entries from the table for easier readability for the user and not to have three separate columns for the combo box. I feel I'm missing something fundamental regarding control focus since this is the error I am receiving, but
    Code:
    DoCmd.GoToControl "Employee_List" 'Set focus to combo box for names
    and
    Code:
    Screen.ActiveControl.RowSource = Screen.ActiveControl.RowSource & """ & Employee_line & "";" 'Append to list in combo box
    -Should- set focus correctly without throwing an error from what I've learned so far. I'm really stumped at what the problem could be.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are making this unnecessarily difficult. Can't imagine why one combo column with concatenated values would be less confusing than one column for each. You couldn't search on the concatenation anyway, so you'd still need a column for the record id, although it doesn't have to be visible too. Also, why worry about the ActiveControl when the code applies to the control that's being interacted with? That is, if I'm clicking on a combo, the event runs for that control. The ActiveControl property of the form is pointless since the clicked on control is not only known, any code applicable to it can run.

    Setting a row source for a combo in BeforeUpdate seems like a strange approach. You have to be able to pick a value in order to update the control. How do you do that if there's no rows to begin with? If you are trying to append values to a value list, I would use the AddItem method of the combo box - not sure you can do it your way. Then again, I'd base the combo rowsource on a query, which is much easier to modify in the future if need be. It certainly would be the easier way to concatenate fields if that's just a necessity. Either way, you'd have to worry about the appearance if a field can contain nulls, whereas with multiple columns, you don't.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-09-2016, 01:23 PM
  2. Replies: 3
    Last Post: 03-02-2016, 06:05 PM
  3. Replies: 8
    Last Post: 06-12-2014, 05:25 AM
  4. Combobox setting other combobox values problem
    By maxmaggot in forum Programming
    Replies: 8
    Last Post: 06-30-2013, 07:18 AM
  5. Combobox not updating
    By lostfan789 in forum Forms
    Replies: 15
    Last Post: 06-16-2010, 09:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums