Selecting Items From Listbox and storing into table

[wheels1284]

Hello,

I am currently trying to set up a form that manages distribution lists. I am
using a list box to pull back the names and user ids of each person (from a
user table), but now I need to take the ones I select and store them into a
table which contains the user id and the respective distribution list. I
tried using the listbox wizard and it seems like I got it to work, but I
still don't know how to take that data and place it into a table. I was
thinking a command button that did some sort of insert command, but I am weak
on the code end of things. Any suggestions would be appreciated. Thank you.

 

[B. Edwards]

' The following assumes: a list box called lboUser
' The first column in the list box contains the information to be stored in
the table called tblUser

Dim strName As String
Dim varItem As Variant
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM tblUser", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

With Me.lboUser
If .MultiSelect = 0 Then
strName = .Value
rst.AddNew
rst.Fields("Username") = strName
rst.Update
Else
For Each varItem In .ItemsSelected
strName = .Column(0, varItem)
rst.AddNew
rst.Fields("UserName") = strName
rst.Update
Next varItem
End If
End With

 

[B. Edwards]

Or if you prefer to use a SQL statement instead of creating a recordset

Dim strSQL As String
Dim strName As String
Dim varItem As Variant
With Me.lboUser
If .MultiSelect = 0 Then
strName = .Value
strSQL = "INSERT INTO tblUser (UserName) VALUES ('" & strName & "')"
CurrentProject.Connection.Execute strSQL

Else
For Each varItem In .ItemsSelected
strName = .Column(0, varItem)
strSQL = "INSERT INTO tblUser (Username) VALUES ('" & strName &
"')"
CurrentProject.Connection.Execute strSQL
Next varItem
End If
End With

 

[wheels1284 via AccessMonster.com]

Thanks for taking the time to help. However I am still having some problems.
So I used the code containing the recordset. But when I try to add the users,
I am getting the certain values can't be null because they are required in
the user's table where I am pulling the values from, but I don't need those
values for the new table. So I tried to add the required fields and establish
relationships but it still saying the value is null. I checked the debugger
and it seems to get stuck at the rst.update line. Is there anyway to tell it
that I just want the userid's and not all those other fields? Or how should I
proceed? Any help would be appreciated.

More info on the tables:
I am pulling the data from tbl_Users
Only really care about a field called UserID
But fields FirstName and LastName are required

Putting the data into tbl_l_UserDistributionList
Only contains a UserID and the Distribution List (The distribution list is
created when a new entry is made into the form)

Hope this helps make things easier to understand. Any questions feel free to
ask.

Thanks

' The following assumes: a list box called lboUser
' The first column in the list box contains the information to be stored in
the table called tblUser

Dim strName As String
Dim varItem As Variant
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM tblUser", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

With Me.lboUser
If .MultiSelect = 0 Then
strName = .Value
rst.AddNew
rst.Fields("Username") = strName
rst.Update
Else
For Each varItem In .ItemsSelected
strName = .Column(0, varItem)
rst.AddNew
rst.Fields("UserName") = strName
rst.Update
Next varItem
End If
End With

Hello,

[quoted text clipped - 11 lines]

on the code end of things. Any suggestions would be appreciated. Thank
you.

 

[B. Edwards]

What is the rowsource of the listbox?
What is the bound column of the listbox?
In tbl_1_UserDistributionList is the field Distributiion List a required
field?

" Putting the data into tbl_l_UserDistributionList
Only contains a UserID and the Distribution List (The distribution list is
created when a new entry is made into the form)"
How is the distribution list created when a new entry is made into the form?

Thanks for taking the time to help. However I am still having some
problems.
So I used the code containing the recordset. But when I try to add the
users,
I am getting the certain values can't be null because they are required in
the user's table where I am pulling the values from, but I don't need
those
values for the new table. So I tried to add the required fields and
establish
relationships but it still saying the value is null. I checked the
debugger
and it seems to get stuck at the rst.update line. Is there anyway to tell
it
that I just want the userid's and not all those other fields? Or how
should I
proceed? Any help would be appreciated.

More info on the tables:
I am pulling the data from tbl_Users
Only really care about a field called UserID
But fields FirstName and LastName are required

Putting the data into tbl_l_UserDistributionList
Only contains a UserID and the Distribution List (The distribution list is
created when a new entry is made into the form)

Hope this helps make things easier to understand. Any questions feel free
to
ask.

Thanks

' The following assumes: a list box called lboUser
' The first column in the list box contains the information to be stored
in
the table called tblUser

Dim strName As String
Dim varItem As Variant
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM tblUser", CurrentProject.Connection,
adOpenDynamic,
adLockOptimistic

With Me.lboUser
If .MultiSelect = 0 Then
strName = .Value
rst.AddNew
rst.Fields("Username") = strName
rst.Update
Else
For Each varItem In .ItemsSelected
strName = .Column(0, varItem)
rst.AddNew
rst.Fields("UserName") = strName
rst.Update
Next varItem
End If
End With

Hello,

[quoted text clipped - 11 lines]

on the code end of things. Any suggestions would be appreciated. Thank
you.

 

[wheels1284 via AccessMonster.com]

Rowsource = SELECT tbl_Users.UserID, tbl_Users.LastName FROM tbl_Users ORDER
BY tbl_Users.LastName;

Bound Column = 1

The field is actually called distribution name, and yes it is required

As far as how a new list is created: I have a switchboard running. When I
click on the respective form it opens in Edit mode. Then if I enter data into
the last record (which is blank), it adds a new record.

Hope that helps.

What is the rowsource of the listbox?
What is the bound column of the listbox?
In tbl_1_UserDistributionList is the field Distributiion List a required
field?

" Putting the data into tbl_l_UserDistributionList
Only contains a UserID and the Distribution List (The distribution list is
created when a new entry is made into the form)"
How is the distribution list created when a new entry is made into the form?

Thanks for taking the time to help. However I am still having some
problems.

[quoted text clipped - 61 lines]

 

[B. Edwards]

If Distribution name is a required field, then is has to be provided
whenever you add a new record:

Dim intUserId As Integer
Dim varItem As Variant
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM tblUser", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

With Me.lboUser
If .MultiSelect = 0 Then
intUserId = .Value
rst.AddNew
rst.Fields("UserId") = intUserId
rst.fields("DistributionName") = whatever the distribution name is
rst.Update
Else
For Each varItem In .ItemsSelected
intUserId = .Column(0, varItem)
rst.AddNew
rst.Fields("UserId") = intUserId
rst.fields("DistributionName") = whatever the distribution name
is
rst.Update
Next varItem
End If
End With

Rowsource = SELECT tbl_Users.UserID, tbl_Users.LastName FROM tbl_Users
ORDER
BY tbl_Users.LastName;

Bound Column = 1

The field is actually called distribution name, and yes it is required

As far as how a new list is created: I have a switchboard running. When I
click on the respective form it opens in Edit mode. Then if I enter data
into
the last record (which is blank), it adds a new record.

Hope that helps.

What is the rowsource of the listbox?
What is the bound column of the listbox?
In tbl_1_UserDistributionList is the field Distributiion List a required
field?

" Putting the data into tbl_l_UserDistributionList
Only contains a UserID and the Distribution List (The distribution list
is
created when a new entry is made into the form)"
How is the distribution list created when a new entry is made into the
form?

Thanks for taking the time to help. However I am still having some
problems.

[quoted text clipped - 61 lines]

on the code end of things. Any suggestions would be appreciated. Thank
you.

 

[wheels1284 via AccessMonster.com]

I was able to get it to run without errors, but I have a problem. It is not
pulling back all of my data, specifically my distribution name and id which
are foreign keys from another table (tbl_distributionlist), although it is
pulling the data from the users table(first name, last name user id). I tried
to add a relationship and added a distribution name and id fields in my users
table. But the data didn't move over with it. So I tried to create a query,
pulling data from the two tables. But then all my text in my list box
disappeared. I know this is kind of vague sorry. How do I get access to pull
the data from the distribution list table? If it will help I can send you
screen shots.

User's table (first name last name userID)
Distribution List table (Distribution ID, Distribution Name) -->
UserDistributionList Table

(shows Distribution ID and User ID)

If Distribution name is a required field, then is has to be provided
whenever you add a new record:

Dim intUserId As Integer
Dim varItem As Variant
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM tblUser", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

With Me.lboUser
If .MultiSelect = 0 Then
intUserId = .Value
rst.AddNew
rst.Fields("UserId") = intUserId
rst.fields("DistributionName") = whatever the distribution name is
rst.Update
Else
For Each varItem In .ItemsSelected
intUserId = .Column(0, varItem)
rst.AddNew
rst.Fields("UserId") = intUserId
rst.fields("DistributionName") = whatever the distribution name
is
rst.Update
Next varItem
End If
End With

Rowsource = SELECT tbl_Users.UserID, tbl_Users.LastName FROM tbl_Users
ORDER

[quoted text clipped - 28 lines]