Need help binding multiselect listbox to table

[JNariss@gmail.com]

Hello,

I have created a form called frmS2P with the following:

1 listbox called List11 which holds the contents of a query created off
my table called tblRequestActio ns. The fields which the listbox holds
are Request_ID and MoveNumber.

1 text box called Date which is populated with the default value of
=Date( ).

1 combo box called Combo2 which is holds the contents of a query
created off my users table to display the names of managers.

1 button called Submit

I would like this form to allow managers to select multiple items from
the listbox and then use the combo box to pick there name and then
click the submit button.

Once the submit button is clicked I would like it to go find the table
tblRequestActio ns and populate the records that have been choosen in
the listbox by finding the Request ID in that table and updating the
DateSubmitted and SubmitAuthorize dBy fields with the contents of the
Date text box and Submitted By Combo2 box.

I have started on my own to write this code with help from searching
around and I can't figure out how or where to add the Date field in and
why it is not reading the field SubmitAuthorize dBy in the
tblRequestActio ns. I keep getting an error of method or data member not
found.

The code I have so far is:

Private Sub cmdSubmit_Click ()
Dim db As DAO.Database
Dim rst As DAO.Recordset
If Me.List11.Items Selected.Count = 0 Then
MsgBox "No items selected. Update operation aborted."
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordse t("tblRequestAc tions")
rst.Index = "Request_ID "
For Each itm In Me.List11.Items Selected
rst.Seek "=", Me.List11.ItemD ata(itm)
rst.Edit
rst.SubmitAutho rizedBy = Me.Combo2
rst.Update
Next
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub


Could someone please point in the right direction here??

Thanks,
Justine

[Tom van Stiphout]

Re: Need help binding multiselect listbox to table

On 9 Mar 2006 18:06:28 -0800, JNariss@gmail.c om wrote:

Looks like you're on the right track. A few points:
* After EVERY .Seek, the next line should be .NoMatch:
rst.Seek "=", Me.List11.ItemD ata(itm)
if rst.NoMatch then
MsgBox "Aaarrrrccchhhh !!!"
else
'normal processing
end if

* Rather than:
rst.SubmitAutho rizedBy = Me.Combo2
I would write:
rst!SubmitAutho rizedBy = Me.Combo2
Reason: a recordset doesn't have that property, like it has rst.EOF,
but it has such a field, and the ! notation can be used for that, or
you can write:
rst.Fields("Sub mitAuthorizedBy ").Value = ...
(the .Value part is not necessary because that's the default property,
but in .Net there are no default properties - might as well get used
to it.)

* Set a breakpoint at the top of this function and step through it.
It's easy to bind a dropdown list incorrectly, and where you think
Me.Combo2 returns an EmployeeID (long int), it may return text
unexpectedly. Inspect all values carefully in the debugger.

-Tom.


[color=blue]
>Hello,
>
>I have created a form called frmS2P with the following:
>
>1 listbox called List11 which holds the contents of a query created off
>my table called tblRequestActio ns. The fields which the listbox holds
>are Request_ID and MoveNumber.
>
>1 text box called Date which is populated with the default value of
>=Date( ).
>
>1 combo box called Combo2 which is holds the contents of a query
>created off my users table to display the names of managers.
>
>1 button called Submit
>
>I would like this form to allow managers to select multiple items from
>the listbox and then use the combo box to pick there name and then
>click the submit button.
>
>Once the submit button is clicked I would like it to go find the table
>tblRequestActi ons and populate the records that have been choosen in
>the listbox by finding the Request ID in that table and updating the
>DateSubmitte d and SubmitAuthorize dBy fields with the contents of the
>Date text box and Submitted By Combo2 box.
>
>I have started on my own to write this code with help from searching
>around and I can't figure out how or where to add the Date field in and
>why it is not reading the field SubmitAuthorize dBy in the
>tblRequestActi ons. I keep getting an error of method or data member not
>found.
>
>The code I have so far is:
>
>Private Sub cmdSubmit_Click ()
>Dim db As DAO.Database
>Dim rst As DAO.Recordset
>If Me.List11.Items Selected.Count = 0 Then
> MsgBox "No items selected. Update operation aborted."
> Exit Sub
>End If
>Set db = CurrentDb
>Set rst = db.OpenRecordse t("tblRequestAc tions")
>rst.Index = "Request_ID "
>For Each itm In Me.List11.Items Selected
> rst.Seek "=", Me.List11.ItemD ata(itm)
> rst.Edit
> rst.SubmitAutho rizedBy = Me.Combo2
> rst.Update
>Next
>rst.Close
>Set rst = Nothing
>Set db = Nothing
>
>End Sub
>
>
>Could someone please point in the right direction here??
>
>Thanks,
>Justine[/color]