Multiple selection list box

P

pkihou

I am trying to populate the different columns, with different vales;
following script is not doing what I need to do. Can some one help me? Thanks


Dim frm As Form, ctl As ListBox
Dim varItem As Variant
Dim strSQL1 As String
Dim db As DAO.Database


Set db = CurrentDb
Set frm = Forms!CreateComplianceReport
Set ctl = frm!ASQARep

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
If ctl.MultiSelect > 0 Then
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(ASQARep1, varItem) & "','" & ctl.Column(ASQARep2, varItem) & "');"

db.Execute strSQL

Next varItem

End If
End If

Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
 
S

SteveS

According to Help (A2K):

"You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Use 0 to refer to
the first column, 1 to refer to the second column, and so on. Use 0 to refer to
the first row, 1 to refer to the second row, and so on."


In "control.Column(column, row)", the column and the row must be integers.

If ASQARep1 is the second column in the list box and ASQARep2 is the third
column in the list box, strSQL would look like this:

(remember zero based columns)

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"



HTH
Steve S.
 
P

pkihou

Steve S.
Here what I am trying to do. It just want to populate the the different
table with the selected values. The first selected value should go to the
first column and the second selected value should go to the second column in
the different table....
Thanks

SteveS said:
According to Help (A2K):

"You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Use 0 to refer to
the first column, 1 to refer to the second column, and so on. Use 0 to refer to
the first row, 1 to refer to the second row, and so on."


In "control.Column(column, row)", the column and the row must be integers.

If ASQARep1 is the second column in the list box and ASQARep2 is the third
column in the list box, strSQL would look like this:

(remember zero based columns)

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"



HTH
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
I am trying to populate the different columns, with different vales;
following script is not doing what I need to do. Can some one help me? Thanks


Dim frm As Form, ctl As ListBox
Dim varItem As Variant
Dim strSQL1 As String
Dim db As DAO.Database


Set db = CurrentDb
Set frm = Forms!CreateComplianceReport
Set ctl = frm!ASQARep

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
If ctl.MultiSelect > 0 Then
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(ASQARep1, varItem) & "','" & ctl.Column(ASQARep2, varItem) & "');"

db.Execute strSQL

Next varItem

End If
End If

Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
Click to expand...
Click to expand...
 
S

SteveS

OK.

You can't use the SQL statement:

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(ASQARep1, varItem) & "','" & ctl.Column(ASQARep2, varItem) & "');

because in "ctl.Column(Column,row)" column & row have to be INTEGERS.
You need to use:

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"
^^^ ^^^

provided that "ComplianceReport" is the table name and "ASQARep1" & "ASQARep2"
are field names in the table.

HTH
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Steve S.
Here what I am trying to do. It just want to populate the the different
table with the selected values. The first selected value should go to the
first column and the second selected value should go to the second column in
the different table....
Thanks

:

According to Help (A2K):

"You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Use 0 to refer to
the first column, 1 to refer to the second column, and so on. Use 0 to refer to
the first row, 1 to refer to the second row, and so on."


In "control.Column(column, row)", the column and the row must be integers.

If ASQARep1 is the second column in the list box and ASQARep2 is the third
column in the list box, strSQL would look like this:

(remember zero based columns)

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"



HTH
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
I am trying to populate the different columns, with different vales;
following script is not doing what I need to do. Can some one help me? Thanks


Dim frm As Form, ctl As ListBox
Dim varItem As Variant
Dim strSQL1 As String
Dim db As DAO.Database


Set db = CurrentDb
Set frm = Forms!CreateComplianceReport
Set ctl = frm!ASQARep

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
If ctl.MultiSelect > 0 Then
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(ASQARep1, varItem) & "','" & ctl.Column(ASQARep2, varItem) & "');"

db.Execute strSQL

Next varItem

End If
End If

Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
Click to expand...
Click to expand...
Click to expand...
 
P

pkihou

is there any way i can resolve this problem?

pkihou said:
Steve S.
Here what I am trying to do. It just want to populate the the different
table with the selected values. The first selected value should go to the
first column and the second selected value should go to the second column in
the different table....
Thanks

SteveS said:
According to Help (A2K):

"You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Use 0 to refer to
the first column, 1 to refer to the second column, and so on. Use 0 to refer to
the first row, 1 to refer to the second row, and so on."


In "control.Column(column, row)", the column and the row must be integers.

If ASQARep1 is the second column in the list box and ASQARep2 is the third
column in the list box, strSQL would look like this:

(remember zero based columns)

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"



HTH
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
I am trying to populate the different columns, with different vales;
following script is not doing what I need to do. Can some one help me? Thanks


Dim frm As Form, ctl As ListBox
Dim varItem As Variant
Dim strSQL1 As String
Dim db As DAO.Database


Set db = CurrentDb
Set frm = Forms!CreateComplianceReport
Set ctl = frm!ASQARep

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
If ctl.MultiSelect > 0 Then
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(ASQARep1, varItem) & "','" & ctl.Column(ASQARep2, varItem) & "');"

db.Execute strSQL

Next varItem

End If
End If

Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Did you try the modified SQL I suggested?




strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"


Each time you execute the above statement, there will be a new record inserted
into table "ComplianceReport". The field named "ASQARep1" will have the value
(string) from the selected row, column 1 of the listbox and "ASQARep2" will
have the value (string) from the selected row, column 2 of the listbox.

What happened when you executed the SQL?

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
is there any way i can resolve this problem?

:

Steve S.
Here what I am trying to do. It just want to populate the the different
table with the selected values. The first selected value should go to the
first column and the second selected value should go to the second column in
the different table....
Thanks

:

According to Help (A2K):

"You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Use 0 to refer to
the first column, 1 to refer to the second column, and so on. Use 0 to refer to
the first row, 1 to refer to the second row, and so on."


In "control.Column(column, row)", the column and the row must be integers.

If ASQARep1 is the second column in the list box and ASQARep2 is the third
column in the list box, strSQL would look like this:

(remember zero based columns)

strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(1, varItem) & "','" & ctl.Column(2, varItem) & "');"



HTH
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

pkihou wrote:

I am trying to populate the different columns, with different vales;
following script is not doing what I need to do. Can some one help me? Thanks


Dim frm As Form, ctl As ListBox
Dim varItem As Variant
Dim strSQL1 As String
Dim db As DAO.Database


Set db = CurrentDb
Set frm = Forms!CreateComplianceReport
Set ctl = frm!ASQARep

'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
If ctl.MultiSelect > 0 Then
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO ComplianceReport (ASQARep1,ASQARep2) VALUES ('" &
ctl.Column(ASQARep1, varItem) & "','" & ctl.Column(ASQARep2, varItem) & "');"

db.Execute strSQL

Next varItem

End If
End If

Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
Click to expand...
Click to expand...
Click to expand...
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error in multiple select in a list box code 4
Multi-Select List Box as Query Parameter 1
Multi-Select List Box 2
Multi-Select List Box Code Problem 5
Help with multiple Select List box 2
Multi-select list box as query parameter 15
More problems with Multiselect List Boxes 10
Access 2007, Passing parameters over to a query 4

Top