add record to table from a form using unbound list and text boxes

[kabilliu]

Hi all,
I have a form with 1 unbound listbox as drop down list (entypolst), an unbound text box(entypotxt) and a command button. The list box reads items from a table. I want to change a value (text) on listbox, input a text on textbox so pressing the button add a new record in a table (Table1 fields Category,Product) showing in my form as subform (SFTable1) in datasheet view. For that reason a made the following code:

Private Sub AddBtn_Click()
Dim ans As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
If Me.entypolst.ItemsSelected.Count = 0 Then
MsgBox "0 items selected !"
Exit Sub
End If
Set rs = db.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)
Me.entypolst.SetFocus
Me.entypolst.Dropdown
For Each varItem In Me.entypolst.ItemsSelected
rs.AddNew
rs![Category] = Me.entypolst.Column(0, varItem)
rs![Product] = Me.entypotxt
rs.Update
Next varItem
ans = Me.entypolst.ItemsSelected.Count
MsgBox Str(ans)
[SFTable1].Requery
MsgBox Str(ans)
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

I take the error msg for 0 items selected in listbox and exit the sub. I can’t understand the problem.
I appreciate any help
Thanks in advance

 

[billmeye]

I don't understand why you have this code inserted after setting your recordset:

Me.entypolst.SetFocus
Me.entypolst.Dropdown

I would think that would reset whatever had previously been selected and when you attempt to append your recordset there would no longer be any ItemsSelected.

Also, is entypolst a multiselect field your using as a ComboBox or is it a ListBox?

 

[kabilliu]

Thanks for your interest
you' re right, I forgot these τεστ lines of code by mistake. I quote the correct code but the problem remains:

Private Sub AddBtn_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
If Me.entypolst.ItemsSelected.Count = 0 Then
MsgBox "0 items selected !"
Exit Sub
End If
Set rs = db.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)
For Each varItem In Me.entypolst.ItemsSelected
rs.AddNew
rs![Category] = Me.entypolst.Column(0, varItem)
rs![Product] = Me.entypotxt
rs.Update
Next varItem
[SFTable1].Requery
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

The entypolst is a multiselect ComBobox field.
Thanks again for your reply.

 

[billmeye]

I'm unfamiliar with your syntax:

Me.entypolst.Column(0, varItem)

I would have chosen: Me.entypolst.ItemData(varItem)
The column(0) should not be necessary unless your defaulting to other than the first column of your row source, in that case, Me.entypolst.ItemData(varItem).column(0)

Maybe that is your problem.

 

[kabilliu]

Thanks for your reply
I try your suggestions but the problem is still the same. Nothing happens on table.

 

[kabilliu]

I am searching a way to import new records to a table by a form which contains a combobox and a textbox. If there is another way please help me!

Thank you in advance

 

[billmeye]

Does your code run without any errors? I'm not familiar with using multi-select fields. I would just see if each step worked, i.e, add Debug.Print Me.entypolst.ItemsSelected.Count to see that it actually has data. Add a Debug.Print Me.entypolst.ItemData(varItem) after rs.addnew etc. Also, I'd get rid of the dbAppendOnly, it's not needed.

One other thing, if there are any Foreign Keys needed then you won't be able to add a record unless you also add those.

 

[kabilliu]

Ok, with the following simple code I solved the problem:

Private Sub AddBtn_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1")
rs.AddNew
rs("Category") = Me![entypolst]
rs("Product") = Me![entypotxt]
rs.Update
rs.Close
Me.[SFTable1].Requery
End Sub

Thank you all for interest