What am I missing?!

[miss031]

I don't know why this won't work. I am getting a type mismatch error.


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant

Set db = CurrentDb

Set rst = db.OpenRecordset("tbl_firearms_licence")

For Each varItem In Me.txt_gun_lic_no.Column(0)

With rst
.AddNew
rst![contact_ID] = [Forms]![add_new_all]![contact_ID]
rst![licence_type] = varItem
'fill in here all the necessary field values
.Update

.Close
End With
Next varItem

 

[mscertified]

ensure you are moving correct data type to each column (based on defined data
type in table design_). Also make sure no value has a NULL in it.

Also it looks like you are closing the recordset in the wrong place!

-Dorian

 

[miss031]

Thanks... sometimes a person just needs a fresh set of eyes. I changed the
code to below, but now, if there is 2 items selected in the listbox, it will
insert 2 records in the table, but they will both be the same, and the same
if there are 3 selected, it inserts 3 identical records. What did I screw up
now?


With rst
For Each varItem In Me.txt_gun_lic_no.ItemsSelected
.AddNew
rst![contact_ID] = [Forms]![add_new_all]![contact_ID]
rst![licence_type] = Me.txt_gun_lic_no.Column(0)
'fill in here all the necessary field values
.Update
Next varItem
.Close
End With

ensure you are moving correct data type to each column (based on defined data
type in table design_). Also make sure no value has a NULL in it.

Also it looks like you are closing the recordset in the wrong place!

-Dorian

I don't know why this won't work. I am getting a type mismatch error.


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant

Set db = CurrentDb

Set rst = db.OpenRecordset("tbl_firearms_licence")

For Each varItem In Me.txt_gun_lic_no.Column(0)

With rst
.AddNew
rst![contact_ID] = [Forms]![add_new_all]![contact_ID]
rst![licence_type] = varItem
'fill in here all the necessary field values
.Update

.Close
End With
Next varItem