Populating database from a listbox.

[olushow]

Scenario,

Initially I had a form with approximately 40 combo boxes, based on a suggestion from another discussion group(actually Microsofts support group), I decided to use two listboxes, one text box for the batch number and two command buttons (one to close the form, the other to append the contents of the 2nd listbox to a particular table (tblBatch).

What I need to do is to be able to append this data to a tblBatch.

The reason for so many combo boxes is because of a particlar batch process that may have 15 procedures attached to it or 40. What we are ultimately trying to do is figure out who has been trained on these procedures.



Since I have now created the listboxes, and can move data freely between both listboxes, I now want to be able to append the values in the 2nd listbox to the Batch Table(tblBatch). I still have my form with the 40 combo boxes if this doesn't work out, but I figured that this will be better option for the user.

I'd like to think that my database has been designed properly, the problem is that we have very complex business processes.

 

[MajP]

In these examples I use a multicolumn, multiselect list box where column 1 is a boolean variable, and column 2 is a text field. In the first example I append only the selected rows. In the second, I append all rows in the listbox.

Private Sub cmdUpdate_Click()
  Dim varItem As Variant
  Dim strSql As String
  Dim fldOne As Boolean
  Dim fldTwo As String
  For Each varItem In list2.ItemsSelected
    fldOne = Me.list2.Column(1, varItem)
    fldTwo = Me.list2.Column(2, varItem)
    strSql = "INSERT INTO tblData (blnTF,strFld1)select " & fldOne & ", '" & fldTwo & "'"
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSql)
    DoCmd.SetWarnings (True)
  Next varItem
  End Sub

Private Sub cmdUpdate_Click()
  Dim intCount As Integer
  Dim strSql As String
  Dim fldOne As Boolean
  Dim fldTwo As String
  For intCount = 0 To list2.ListCount - 1
    fldOne = Me.list2.Column(1, intCount)
    fldTwo = Me.list2.Column(2, intCount)
    strSql = "INSERT INTO tblBatch (blnTF,strFld1)select " & fldOne & ", '" & fldTwo & "'"
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSql)
    DoCmd.SetWarnings (True)
  Next intCount
  End Sub

I doubt that your tables are properly designed. If you need a form with 40 comboboxes, it is probably a result of non-normalized tables not a result of complex buisness rules.

 

[SuryaF]

I agree with MajP that there might be a defective normalization.

If your tblBatch table looks like
BatchNo, UserID, ProcID1,ProcID2...ProcID40
then you should change it to:
tblBatch: BatchNo, UserID
tblBatchProcs: BatchNo, ProcID
Then your second list can be directly linked to the second table or to a temporary table (as you probably have right now) and append from the temporary to the tblBatchProcs table on save.

If you prefer not to change the structure, having so many fields I think the easiest is something like

Set rs = CurrentDb.OpenRecordset("tblBatch")

rs.AddNew
For intCount = 1 To list2.ListCount
    rs.Fields("ProcID" & intCount) = Me.list2.ItemData(intCount - 1)
Next intCount
rs.Update

 

[olushow]

I already have a table similar to the tblBatchProcs. Another thing to consider which I didn't explain is that there is more than one batch type.

But if, For Example, There is a particular batch that requires an Employee to be trained in 40 procedures. How do you get around that? Regardless of who the employee is, there is product that requires an employee to be trained in
40 procedures before they are aloud in the manufacturing lab. ?

 

[SuryaF]

I don't think I understand your process there, can you explain in a few words?
For example:
A. Is a Batch a set of procedures they have been trained in at a certain time?
B. Can they be trained in a various number of procedures at once or just 40/15?
C. Are the batches made of the same procedures all the time or any procedures from a certain list?
D. Can a user have only 1 batch?
E. When you're looking to see if the user has been trained in those 40 procedures, are they any 40 procedures of the same type or a specific set of 40 procedures?

 

[olushow]

Yes,

First of all, I will stop saying 40 combo, because the most procedures a batch can have is 36. So let's just say 36 combo boxes. Ok?

The batch is used to describe the manufacturing process of a product. Meaning it is the same product but manufactured in batches. The tricky part is that there are approximately 10 types of batches, and under each batch are the procedures.

There are two sets of batches that have the same procedure all the time, the other batches have their own set of procedures. (I guess I can still apply the concept of Normalization here)

As far as the user goes, I am not quite sure what you mean by user. If you mean the End user, most of the users will just be running the reports. Only two users will be doing data entry.


The current form with multiple combo boxes will be not be used that often, because even after completing a batch process, there are sub processes that fall under the batch process which can take up to 6 months to complete, and will only be used by the QA team.

The Database seems to be giving us the information we need, in terms of reports. But as I stated earlier there is no way of getting around two particular batches, because one of the batches consists of 40 procedures, all the time. While the other also consists of 25 procedures. ALWAYS.

Until the company decides to change their policy. That is why I decided to use a form with to list boxes, a text box, and two command buttons.I know that a form with 40 combo boxes is not good, to say the least.

 

[SuryaF]

The way I understand your system, a normalized structure would have:

tblBatchType: BatchTypeID (Long), BatchTypeName (String)
tblBatchTypeProcs: BatchTypeID (Long), ProcedureID (Long)

tblProcedures: ProcedureID (Long), ProcedureName (String), ProcedureTypeID (Long)
tblProcedureTypes: ProcedureTypeID (Long), ProcedureTypeName (String)

tblEmployees: EmployeeID (Long), FirstName (String), LastName (String) ...

tblTrainings: EmployeeID (Long), ProcedureID (Long), Passed (Yes/No) ...

You can define all 10 Batch Types once with as many procedures as you want.
To see who is trained for a certain Batch Type you can run something like

qryEmplProcsAll (all combinations employees-procedures):
SELECT tblEmployees.EmployeeID, tblProcedures.ProcedureID
FROM tblEmployees, tblProcedures;

qryEmplProcsTrained (training situation for all combinations employees-procedures):

SELECT qryEmplProcsAll.*, nz([Passed],False) AS Trained
FROM qryEmplProcsAll LEFT JOIN tblTrainings ON (qryEmplProcsAll.EmployeeID = tblTrainings.EmployeeID) AND (qryEmplProcsAll.ProcedureID = tblTrainings.ProcedureID);

qryEmplBatchTrained (all combinations batch-employees and their training status):
SELECT qryEmplProcsTrained.EmployeeID, tblBatchProcs.BatchID, Min(qryEmplProcsTrained.Trained) AS TrainedOnBatch
FROM tblBatchProcs INNER JOIN qryEmplProcsTrained ON tblBatchProcs.ProcedureID = qryEmplProcsTrained.ProcedureID
GROUP BY qryEmplProcsTrained.EmployeeID, tblBatchProcs.BatchID;

Check it out.

 

[MajP]

That was about what I was thinking. Also once you do what SuryaF suggests there is no more complicated user interfaces. Simple forms and subforms.

The key is the table tblBatchTypeProcs. Here is where the many to many relationship takes place. Your buisness processes have many-to-many relationships, make guess is that your current structure does not. I usually call these join or link tables.

 

[olushow]

My database is pretty similar to what you described, although I can definately use a lot of your methods to modify my db design. But it still does not change the fact that two particular batch types have approximately 20-30 procedures each. Those wto Still, my question is that do you agree that there is no way of getting around that. Am I a making any sense? Or do you feel there is a way of getting around that.

I forgot to mention that the Batch types never change, the Batch Numbers are never changed, but they may be revised.
For example Batch number P00001Rev00 can be revised to P00001Rev01. This will apply to P00002 through P000010.
Am i making sense to you now.?

 

[olushow]

I still need help in populating my database with the contents of the listbox, plus the contents of a text box. All are on the same form. If you give me your email address, I can send you a screenshot of the form and my table layout.

 

[MajP]

But it still does not change the fact that two particular batch types have approximately 20-30 procedures each

So why is this a problem? It could have 5,000 procedures does not make a difference.

tblBatchTypeProcs
BatchTypeID ProcedureID (Long)
123 A
123 F
123 G
.
.
.
456 A
456 Z
456 R
.
.
456 PP

my question is that do you agree that there is no way of getting around that.

Unless I am not understanding, I disagree. Proper normalization will get around this.

I may not fully understand your problem yet, but I do not think it is as difficult as you are thinking. It may require another join table.

If the batch number ever change, do not make this a primary key.

 

[olushow]

First of all I want you to understand that , I am not saying you are wrong in your assumption. I just want to fully understand the concept of Normalization. Which is to avoid redundant data. But what if the user/my boss makes a request that violates the rules of Normalization. Also I am taking this session as a learning experience for future projects.
I don't want to go outside of the scope of this forum, which VBA Coding.

Let me give you a broader view of our process.

1. You have a product called ProductX.
2. Product X is Grouped in what is called a Lot Number. This means that the product manufacturing cycle is grouped in LotNumbers, once one cycle is complete, another Lot Number is created for the next cycle of the same product.
3. The Batch Record contains a general description of all the procedures that must be followed in manufacturing the product.
4. There are 10 batch record types.
5. Some batch Records have 9 procedures and some have 36 proedures. (maximum is 36)
5. The procedures table that contains detailed information on each specific procedure.

Initially What we were trying to accomplish is to do a proactive search on employees who have been trained on the procedures based on the LOT NUMBER. (We attach the procedures to the Lot Number, by pulling from the apllicable procedures from the Batch record Table.

My boss decided to take tje database project further by allowing the end-user to create batch records for every LOT Number( I didn't agree with this, because since we know the Batch record always stays the same, there is no need to enter this information every time, but my boss and the end users demanded that the user must be able to create a batch record for every new Lot Number.)

 

[Golom]

I'm not really understanding this.

I gather that somewhere in the creation of a new LOT NUMBER, there's a mechanism to associate it with procedures from the Batch Record Table. All well and good.

Now the question
[blue]
Do you associate the LOT NUMBER with individual procedures from the Batch Record table or do you associate it with the Batch Record Type (which is presumably shared among several procedures)?[/blue]

If the answer is "individual procedures" then creating separate sets for procedures for each LOT NUMBER seems to make sense. The caveat of course is that, having saved a procedure somewhere else in association with a LOT NUMBER you now have to deal with what happens if the procedure definition is changed in the Batch Record Table. If you must do this then you need to reference back to the Batch Record Table using keys rather than just making a copy of the procedure definition somewhere else.

If a given LOT NUMBER is restricted to being one of the 10 Batch Record Types then you should be recording only the Batch Record Type with a LOT NUMBER and not the individual procedures within that type.

 

[olushow]

The term "batch record" may throw you off, but in our instance, the definition of the batch record pertains to a document that contains a general list of procedures that have already been set. They never change, although they may be revised. It's actually called Batch Processing Record.

The Lot Number comes close to what a 'batch record' is. It's kind of weird I know. But that's the way the company set it up.

The reason why the batch record is even significant in this database. Is because the end user, will like the option to create a new batch processing record (stored in the tblBPR), when a new Lot Number is created (tblLotNumber).

I am not quite sure why the user wants this, but one of the reasons I was given, was for the user to have the ability to revise the BPR Number if necessary(this will involve revising a procedure within Batch processing record, thus, once a procedure within the Batch record is revised, the Batch Record Number must be revised, as well- THIS IS COMPANY POLICY). GOD I hope this makes sense!

look at the Batch Processing Table has a template, for the Lot Number Table. The only difference is that the data in the Lot Number table will always change.


My boss isn't really concerned about the fact that we have a table with 35 fields, he just wants to make sure that the data is captured, which it is. Right now the users are using an excel spreadsheet to capture all this information.

I still need some help in being able to add the contents of my 2nd listbox to the Batch processing record table.

 

[SuryaF]

Ok, let's give you a hand with the list, and forget about the structure.

I don't really know what the current structure is but there can only be 2 situations so:

A. If your tables are normalized you want to take a list like
Proc1
Proc2
...
ProcX
to a table like: BatchNo, ProcID
To do that you'll have to use something like MajP's code:

Private Sub cmdUpdate_Click()
  Dim intCount As Integer
  Dim strSql As String
  Dim fldOne As Boolean
  Dim fldTwo As String
  For intCount = 0 To list2.ListCount - 1
    BatchNo = Me.BatchNo 
    ProcID = Me.list2.Column(0, intCount)'assuming it's the first column that you want to save
    strSql = "INSERT INTO tblBatch (BatchNo,ProcID) VALUES (" & BatchNo & ", " & ProcID & ")"'or something like that
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSql)
    DoCmd.SetWarnings (True)
  Next intCount
  End Sub

B. If your table is not normalized and you have tblBatch: BatchNo, ProcID1, ProcID2,... ProcID36 then you can use the code from my first posting:

Set rs = CurrentDb.OpenRecordset("tblBatch")

rs.AddNew
rs!BatchNo=Me.BatchNo
For intCount = 1 To list2.ListCount
    rs.Fields("ProcID" & intCount) = Me.list2.ItemData(intCount - 1)
Next intCount
rs.Update

Does this help at all olushow?

 

[olushow]

I am getting the following error message:
Run time error 3265
'item not found in this collection'

Set rs = CurrentDb.OpenRecordset("tblBPRNumber")

rs.AddNew
rs!BPRNumber = Me.txtBprNumber
For intCount = 1 To lstControl.ListCount
Problem line *** rs.Fields("Sop01" & intCount) = Me.lstControl.ItemData(intCount - 1)
Next intCount
rs.Update

 

[SuryaF]

What is the structure of your table, how are you fields named?
If the fields are Sop01...Sop36 you should change your ("Sop01" & intcount) to ("Sop" & Format(intCount,"00")).

 

[olushow]

Worked like a Charm!

Thanks for your help and Patience..

However I will still have to take sometime to fully understand what you just prescribed. One more question -
What was the significance of modifying the following line ("Sop" & Format(intCount,"00")), as oppose to the one I used - ("Sop01" & intcount)?

 

[SuryaF]

It's like saying:

rs!Sop01=Me.list2.ItemData(0)'first line of the list to first field
rs!Sop02=Me.list2.ItemData(1)'Second line of the list to second field
....
rs!Sop36=Me.list2.ItemData(35)'line 36 of the list to field 36

but for a variable number of items in the list.

Does this make sense?

 

[olushow]

What if I have fields named Sop01...Sop36, as well as fields named BPRNumber, Area. How do I write the same line.
I have another form that I am trying to replicate the same function but I get the same error message: -

Run time error 3265
'item not found in this collection'
***Problem Line***rs.Fields("Sop01" & intCount) = Me.lstControl.ItemData(intCount - 1)******, I have tried it with the Format(intcount "00") syntax.