List Box to a Table

[mlr0911]

How can I get my list box values to a table?
I am using the list box "Row Source Type" as a "Value List" that is being passed information from code.

I need to take the information that is selected from this list box and transfer it to my table1.

Any thoughts?

 

[rainman89]

Is your forms recordsource based on your table?

 

[mlr0911]

No, my form and listbox is unbound.

 

[rainman89]

in the afterupdate event of the listbox you can do

table.fieldname= me.listboxname

* i think

 

[mlr0911]

That makes sense, however, since I am passing the information through vba, when I implement that particular piece of code (table.field=mylistbox; I even tried writing a sql statment) it is telling me "Invalid use of Null--Object Required" where mylistbox is null. It's like it isn't reading the information that is being displayed there. I have no clue on how to get this to work. I have been round and round with it. I am sure it's something simple that I am missing.

Thanks

 

[modest]

Make sure you do a Me.Refresh before you call the code. This forces the listboxes values to be stored.

IE.

Me.Refresh
Table.Field = MyListBox

 

[mlr0911]

Thanks for your help;

However, I am getting an object variable not set error when adding this code. Here is what I have:

Private Sub Combo1_AfterUpdate()
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strTable As String


Set db = CurrentDb()

strTable = Me!Combo1

For Each fld In db.TableDefs(strTable).Fields
'Debug.Print fld.Name

Me!List9.AddItem fld.Name


'I have even placed the example code here as well but doesn't work correctly.

Next fld

Me.Refresh
Table1.Field = fld.Name

End Sub

 

[modest]

You never set your Table1 variable. But I think you need a little help in coding what you need accomplished.

When do you want to add data to the table? When you click on the listbox, or when you double click it. Or do you just want all values in the listbox to go to the table?

You should also be using the dot operator as opposed to the bang operator (period instead of exclamation point)

Your code should probably look something like this:

    [color=Blue]Dim [/color]db              [color=Blue]As [/color]DAO.Database
    [color=Blue]Dim [/color]rs              [color=Blue]As [/color]DAO.Recordset
    
    [color=Blue]Dim [/color]fld             [color=Blue]As [/color]DAO.Field
    [color=Blue]Dim [/color]strTable        [color=Blue]As [/color][color=Blue]String[/color]
    
    strTable = Me.Combo1
    [color=Blue]Set [/color]db = CurrentDb
    [color=Blue]Set [/color]rs = db.OpenRecordset(strTable, dbOpenDynaset)
    
    
  '[color=green]If showing heads change the 0 to a 1[/color]
    [color=Blue]For [/color]i = 0 [color=Blue]To [/color]Me.List9.ListCount - 1
        [color=Blue]With [/color]rs
            .Addnew
                .Fields("ID") = Me.List9.ItemData(i)
            .Update
        [color=Blue]End [/color][color=Blue]With[/color]
    [color=blue]Next[/color]

 

[mlr0911]

Have all variables goto the table after the list refreshes.

Thanks for your help.

 

[mlr0911]

I used the code, but it didn't place the field names into the table.

Thanks

 

[modest]

Manually create the table. This puts the values in the table, it does not create the table.