How can I populate table from listbox

[Rino]

Greetings,

This is my first post and I hope it'll be the lucky one since I've been

hurting my head with the following situation for a while now.


I need to be able to populate a table from all of the items located in
the listbox.
To be more precise this is the procces user needs to perform.
1. from listbox1 user selects the item(row) that populates 3 textboxes.

2. on the command button click, data from those three textboxes goes to

another listbox2 and than it populates it
However, user can go back and repeat the step 1 and then listbox2 will
be populated with another item, so in other words there are now two
rows of data in the listbox2.


What I need here is a function that will allow the user to be able to
hit the command button which will than grab all of those values from
the listbox2 (x number of rows) and populate the table.


Example:


say ID=123


ListBox2
row1: 1, cd234 , this is blalala, $111
row2: 3, fds12, blalbdglfsfgkd, $222


Table after being populated
row1: 123, cd234 , this is blalala, $111
row2: 123, fds12, blalbdglfsfgkd, $222


With big please with whip cream and cherry on the top how can I achieve

this proccess.


I have the code here that only inserts the first record in the table
and can not figure it out how to enter the second, third, etc.


Dim Quan As String
Dim PartNum As String
Dim Description As String
Dim ListEach As String


Dim frm As ListBox, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.lstResults2
'Set ctl = frm!QuoteDetail
Set ctl = frm
DoCmd.SetWarnings False
For Each varItem In ctl.ItemsSelected


strSQL = "INSERT INTO dbo_QuoteItem (QuoteItem_ID, Quote_ID,
Per_ID, Org_ID, Quan, PartNum, Description, ListEach) VALUES ('" &
Me![txtQuoteItemID] & "' , '" & Me![txtQuoteID] & "' , '" & Me![Org_ID]

& "' , '" & Me![Per_ID] & "' , '" & Me.lstResults2.Column(0) & "' , '"
& Me.lstResults2.Column(1) & "' , '" & Me.lstResults2.Column(2) & "' ,
'" & Me.lstResults2.Column(3) & "')"
Debug.Print strSQL
DoCmd.RunSQL strSQL
Next varItem


DoCmd.SetWarnings True


Please help!


Thanks in advance


-Rino

 

[Dirk Goldgar]

Greetings,

This is my first post and I hope it'll be the lucky one since I've
been

hurting my head with the following situation for a while now.


I need to be able to populate a table from all of the items located in
the listbox.
To be more precise this is the procces user needs to perform.
1. from listbox1 user selects the item(row) that populates 3
textboxes.

2. on the command button click, data from those three textboxes goes
to

another listbox2 and than it populates it
However, user can go back and repeat the step 1 and then listbox2 will
be populated with another item, so in other words there are now two
rows of data in the listbox2.


What I need here is a function that will allow the user to be able to
hit the command button which will than grab all of those values from
the listbox2 (x number of rows) and populate the table.


Example:


say ID=123


ListBox2
row1: 1, cd234 , this is blalala, $111
row2: 3, fds12, blalbdglfsfgkd, $222


Table after being populated
row1: 123, cd234 , this is blalala, $111
row2: 123, fds12, blalbdglfsfgkd, $222


With big please with whip cream and cherry on the top how can I
achieve

this proccess.


I have the code here that only inserts the first record in the table
and can not figure it out how to enter the second, third, etc.


Dim Quan As String
Dim PartNum As String
Dim Description As String
Dim ListEach As String


Dim frm As ListBox, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.lstResults2
'Set ctl = frm!QuoteDetail
Set ctl = frm
DoCmd.SetWarnings False
For Each varItem In ctl.ItemsSelected


strSQL = "INSERT INTO dbo_QuoteItem (QuoteItem_ID, Quote_ID,
Per_ID, Org_ID, Quan, PartNum, Description, ListEach) VALUES ('" &
Me![txtQuoteItemID] & "' , '" & Me![txtQuoteID] & "' , '" &
Me![Org_ID]

& "' , '" & Me![Per_ID] & "' , '" & Me.lstResults2.Column(0) & "' , '"
& Me.lstResults2.Column(1) & "' , '" & Me.lstResults2.Column(2) & "' ,
'" & Me.lstResults2.Column(3) & "')"
Debug.Print strSQL
DoCmd.RunSQL strSQL
Next varItem


DoCmd.SetWarnings True


Please help!

You're using the dread word "populate", which means different things to
different people, but I think I understand. The trouble with the code
you posted is that it only writes the *selected* items from lstResults2
out to the table, whereas you want all the items in the list, selected
or not, to be written. Try this:

'----- start of revised (but untested) code -----
Dim db As DAO.Database
Dim strSQLBase As String
Dim I As Long

strSQLBase = _
"INSERT INTO dbo_QuoteItem (QuoteItem_ID, Quote_ID, " & _
"Per_ID, Org_ID, Quan, PartNum, Description, ListEach) " & _
" VALUES ('" & _
Me![txtQuoteItemID] & "' , '" & _
Me![txtQuoteID] & "' , '" & Me![Org_ID] & "' ," & _
"'" & Me![Per_ID] & "' ,"

Set db = CurrentDb

With Me!lstResults2

For I = Abs(.ColumnHeads) To (.ListCount - 1)

strSQL = strbasesql & _
"'" & .Column(0, I) & "', '" & _
"'" & .Column(1, I) & "', '" & _
"'" & .Column(2, I) & "', '" & _
"'" & .Column(3, I) & "')"


db.Execute strSQL, dbFailOnError

Next I

End With
'----- end of code -----

 

[Rino]

Dirk,


You are responsible for putting a big smile on my face!!! ))))
I do not know how to thank you. It works GREAT! - Exactly what I
needed.
Thanks for the quick response too, since I'm close to the deadline this
comes very handy. I've been looking everywere and talked to a lot of
people about how to resolve this problem earlier, but no success till
now.

I love the technology and people like you!

tell, me how can I grade you here since I've seen somewhere people
asking to be graded.

Once again you have a HUGE thanks from me

-Rino

))))

 

[Dirk Goldgar]

You are responsible for putting a big smile on my face!!! ))))
I do not know how to thank you. It works GREAT! - Exactly what I
needed.

Great! You're welcome.

tell, me how can I grade you here since I've seen somewhere people
asking to be graded.

There's no need for that, as far as I'm concerned.

If you're using Microsoft's web interface to these newsgroups (which
neither of us is), you can mark a post as having answered your question.
Microsoft's idea is to make it easier for people looking for answers to
separate useful replies from unhelpful ones, and (I think) to enable MS
to keep answers on file longer than non-answers. There are a few other
features of their web-based newsreader that have some value, such as
identifying answers from MVPs or other posters with a high percentage of
helpful posts, but I find the web interface much too slow for the way I
work. I wouldn't ask anyone to use it just so they can mark posts as
answers.

If, however (as it appears from your headers), you're using Google
Groups to access the newsgroups, you *may* want to have a look at the MS
web-based newsreader, which you can get to via

http://support.microsoft.com/newsgroups/default.aspx

or (for Office programs specifically)

http://www.microsoft.com/office/community/en-us/default.mspx

to see if you find that helpful. I think you have to create and sign in
with Windows Live ID (a.k.a. a Microsoft Passport) to post questions and
get the most out of the web forums. I don't bother with that, but just
use Outlook Express as a normal newsreader client instead.