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 -
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!
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:
Dim strSQL as String
strSQL = "INSERT INTO <TableName> ( <ColumnName>, ... ) VALUES ( <Value to insert>, ... )
CurrentDb.Execute strSQl, dbFailOnError
You can also use a Recordset:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(<TableName>, dbOpenDynaset, dbSeeChanges)
!<ColumnName> = <Value to insert>
Set rst = Nothing
If the form has the same datasource as the list, you can use:
Me!<ColumnName> = <Value to insert>
or ("macro style" VBA, with the advantage of positioning the form on the new record):