By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,184 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,184 IT Pros & Developers. It's quick & easy.

Need help binding multiselect listbox to table

P: n/a
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 tblRequestActions. 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
tblRequestActions and populate the records that have been choosen in
the listbox by finding the Request ID in that table and updating the
DateSubmitted and SubmitAuthorizedBy 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 SubmitAuthorizedBy in the
tblRequestActions. 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.ItemsSelected.Count = 0 Then
MsgBox "No items selected. Update operation aborted."
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("tblRequestActions")
rst.Index = "Request_ID"
For Each itm In Me.List11.ItemsSelected
rst.Seek "=", Me.List11.ItemData(itm)
rst.Edit
rst.SubmitAuthorizedBy = 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

Mar 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 9 Mar 2006 18:06:28 -0800, JN*****@gmail.com 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.ItemData(itm)
if rst.NoMatch then
MsgBox "Aaarrrrccchhhh!!!"
else
'normal processing
end if

* Rather than:
rst.SubmitAuthorizedBy = Me.Combo2
I would write:
rst!SubmitAuthorizedBy = 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("SubmitAuthorizedBy").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.
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 tblRequestActions. 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
tblRequestActions and populate the records that have been choosen in
the listbox by finding the Request ID in that table and updating the
DateSubmitted and SubmitAuthorizedBy 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 SubmitAuthorizedBy in the
tblRequestActions. 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.ItemsSelected.Count = 0 Then
MsgBox "No items selected. Update operation aborted."
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("tblRequestActions")
rst.Index = "Request_ID"
For Each itm In Me.List11.ItemsSelected
rst.Seek "=", Me.List11.ItemData(itm)
rst.Edit
rst.SubmitAuthorizedBy = 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


Mar 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.