Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Update listbox via combobox?

    Hello,

    On my Access 2003 form I have a combobox (rowsource tableA) and a listbox (set to the row source of the form - tableB). I also have a command button.

    From the combobox I want to select a record, then click the command button, and the selected ID field of the record should be displayed in the listbox. I can keep selecting different records from the combobox to fill up the listbox. The listbox bound column is the primary key field of the form, so the listbox shows the correct related record of the form.

    Im having trouble finding or working out the correct code to do this. I've tried -
    Code:
    Me.Combo115.Value = Me!List117.Column(1)
    DoCmd****nCommand acCmdRefresh
    (DBForums places **** in the code quote) But nothing is placed into the listbox. I've not worked much with combo boxes so Im guessing I need different code. Can anyone give me some pointers?

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here are two solutions (among others):

    1. Create a table that will be the RowSource of the list, then add the value you want to see in the list from the AfterUpdate event of the combo.

    2. Set the Row Source Type property of the ListBox to Value List and create a function that will returm such a value list. This function being called by the AfterUpdate event of the combo.
    Have a nice day!

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Hello, the row source of the listbox is the table in which I want the record to go. But Im not sure if I worded my question correctly, I think I need to insert the selected item from the combo box into the table used by the listbox.

    I want the command button to execute the command if all this makes sense!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A listbox is a one-way control (read-only if you prefer). It displays data from a source (table, query, value list, etc...) but cannot be used to insert (add) or update (modify) its underlying data source.

    In your case, you can build a SQL statement to insert data selected from the combo into the table:
    Code:
        Dim strSQL as String
        strSQL = "INSERT INTO <TableName> ( <ColumnName>, ... ) VALUES ( <Value to insert>, ... )
        CurrentDb.Execute strSQl, dbFailOnError
        Me.<ListBoxName>.Requery
    You can also use a Recordset:
    Code:
        Dim rst As DAO.Recordset
        Set rst = CurrentDb.OpenRecordset(<TableName>, dbOpenDynaset, dbSeeChanges)
        With rst
            .AddNew
            !<ColumnName> = <Value to insert>
            .Update
            .Close
        End With
        Set rst = Nothing
        Me.<ListBoxName>.Requery
    If the form has the same datasource as the list, you can use:
    Code:
        Me.Recordset.AddNew
        Me!<ColumnName> = <Value to insert>
        Me.Recordset.Update
        Me.<ListBoxName>.Requery
    or ("macro style" VBA, with the advantage of positioning the form on the new record):
    Code:
        DoCmd.GoToRecord acActiveDataObject, Me.Name, acNewRec
        Me!<ColumnName> = <Value to insert>
        Me.Dirty = False
        Me.<ListBoxName>.Requery
    Have a nice day!

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Thankyou so much for the very informative and useful reply. I have used the first example and my code is this -
    Code:
    DoCmd****nCommand acCmdSaveRecord 
    DoCmd****nSQL "INSERT INTO tblActivities (type_of_activity, visitID )SELECT [Forms]![visits_frm]![combo115] AS Expr1, [Forms]![visits_frm]![visitID] AS Expr2;"
    Me.List124.Requery

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.
    Have a nice day!

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    I have also managed to delete from the listbox as well! -
    Code:
    DoCmd****nSQL "DELETE tblActivities.visitID, tblActivities.type_of_activity, tblActivities.* FROM tblActivities WHERE (((tblActivities.activityID)=[Forms]![visits_frm]![list124]));"
    Me.List124.Requery
    I think there is something in the water this evening!

    Thanks again

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's great!

    Just a little piece of advice: when you issue a DELETE SQL statement for a table, you can skip the enumeration of the columns:
    Code:
    DoCmd****nSQL "DELETE FROM tblActivities WHERE (((tblActivities.activityID)=[Forms]![visits_frm]![list124]));"
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •