Pulling data from a Listbox to a table.

[Guest]

I have two listboxes.
One listbox populates the 2nd listbox.
What I am trying to do is to populate the values in the 2nd listbox to
append the values in the listbox, and also another value in a text box as a
new record in a particular table. I have made several posts, but have not
received any replies. Hope I haven't been blacklisted for asking too many
questions. I am starting to wrap up my project, but I need to make some
modifications to my forms. Initially I had a form with 40 combo boxes, but
based on suggestion from this group, I decided to go with the two listbox ,
command buttons route, which was a smarter/ neater approach..

Here is my code from my Form_Load


Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String, strItem As String

strSQL = "SELECT ControlNumber FROM tblSopNumbers"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Do Until rs.EOF
strItem = rs.Fields("ControlNumber").Value
Me.lstControlNumbers.AddItem strItem 'Row Source type must be Value List
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

The problem I am having is that some times the user may have to populate 15
columns in a table, and sometimes he/she may have to populate more.
Example - A particular batch may require that an employee be trained on 15
modules, while another batch may require the employee be trained on 25.


Any additional information will be provided, if needed.
FYI - I used the "How to use AddItem and RemoveItem to move selections from
one list box to another" from http://support.microsoft.com/kb/278378/;
to create my listboxes.

 

[Larry Linson]

No one is blacklisted for "asking too many questions." Sometimes questions
remain unanswered because people have trouble understanding what the person
has, what the person is trying to acomplish, and what is going wrong. That
is certainly the case with me, but then it is a little late, and maybe I am
just missing something.

For example, I am not certain what "_the_ two listbox, command buttons
route" is that you are talking about -- there is a "two listbox" approach in
which information is moved from one to the other, but that may or may not be
what you mean, and, if it is, I don't know how you are using it, or for what
purpose, nor do I understand "The problem I am having is that some times the
user may have to populate 15 columns in a table, and sometimes he/she may
have to populate more." and the example you cite doesn't clear it up for me.

While I have far too little information and understanding of what you're
doing to be certain, I have some concern that populating a varying number of
columns in a table may indicate an un-normalized table design that might
complicate what you are trying to accomplish.

Re-read your question, but do so from the point of view of someone who does
_not_ have your database in front of them, does not know what you are trying
to accomplish, and needs a clear, simple, straightforward explanation of
what you have, what you expect, what is happening, and why that didn't meet
your expectations.

Too, not many of us have time to visit the Knowledge Base to read details of
a particular article (but even fewer are likely to remember the details of
an article).

Larry Linson
Microsoft Access MVP

 

[Guest]

Scenario,

As far as the issue of Normalization is concerned, the Database is
Normalized up to the you know what.... We just have complex business
processes that cannot be completely address with the database.. Read the
Explanation below:

The manufacturing process of the product is grouped in batches.

The form with the two listboxes will help the end user process a new batch.

The tricky part is that, there are multiple batch types. Some batch types
consist of 12 procedures, while another batch type consists of 30 training
procedures.

What we are trying to do is figure out who is trained on the procedures,
based on the batch type. The QA department wants this database due to FDA
regulations.

I have no control over the business processes, that's the way its setup. I
already have a form with 40 combo boxes to address this process. This works
perfectly.
I was just looking for an alternative way of completing this process.
Having a form with 40 combo boxes seems a little bit untidy.


Form in question

I have a form with two listboxes.

One listbox is the pulling the data from a table called Control Table. The
Control table contains all the procedures that need to be followed by our
manufacturing department..

The 2nd listbox is where I move my selections to. Meaning Whatever I select
in listbox one is move to the 2nd lisbox, what I then want to do is populate
my database (specifically the batch table) with the contents in the 2nd
listbox. That's about it.

I have no problem with my listboxes. I can move data back and forth between
them.

My problem is populating a particular table with the contents of the 2nd
listbox. Let's forget about the rest of the database, let's just focus on
this one aspect of the database, which is to populate a table using the
contents of a listbox. Period.

 

[Larry Linson]

OK, now I understand... this is similar to the use of two listboxes in the
Report Wizard. You are copying items from one unbound List Box to a second
unbound List Box (lstBox2 in my example) with buttons to move both ways.

Once you have the items in the second List Box, you use the following code
to copy the values from lstBox2 to the first Field/Column of a Table,
tblFromList, creating a new record for each value. In my example, I put the
code in the Click event of a CommandButton and did nothing if the Table was
not empty, but you could append records instead, if that is what you want.
Be sure to change the name of the CommandButton, Table, and List Box to the
corresponding names in your application.

Private Sub cmdCopyToTable_Click()
On Error GoTo Err_cmdCopyToTable_Click

Dim rs As DAO.Recordset
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("tblFromList")
If rs.BOF = True And rs.EOF = True Then 'Recordset is Empty, Fill It
For i = 0 To Me.lstBox2.ListCount - 1
rs.AddNew
rs(0) = Me.lstBox2.ItemData(i)
rs.Update
Next i
Else
MsgBox "Table is not empty"
End If

Exit_cmdCopyToTable_Click:
Exit Sub

Err_cmdCopyToTable_Click:
MsgBox Err.Description
Resume Exit_cmdCopyToTable_Click

End Sub

Now, on re-reading your original question, it appears you may want to copy
the values to multiple Fields/Columns within a Record in a Table. As I don't
know how you want to identify the Record in which those columns are to be
filled, and what columns they are, I can't give you code but you should be
able to modify the code above... instead of filling one column in each
record, you will fill multiple columns in one record.

Larry Linson
Microsoft Access MVP