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.

 

[SuryaF]

To be more clear, what the Format(intCount,"00") does is forcing two digits on the number (puts a 0 in front of the number when the number is less than 10).
So:
when you have intCount=1 your "Sop" & intCount = "Sop1" but if you use "Sop" & Format(intCount,"00") = "Sop01".

For your questions.

What if I have fields named Sop01...Sop36, as well as fields named BPRNumber, Area. How do I write the same line.

To edit some other fields as well you just enter the rs!FieldName=<value> somewhere between the AddNew and Update but outside the For...Next
So you'll have:

rs.AddNew
rs!BPRNumber = Me.txtBprNumber
rs!Area=Me.txtArea
For intCount = 1 To lstControl.ListCount
rs.Fields("Sop01" & Format(intCount,"00")) = Me.lstControl.ItemData(intCount - 1)
Next intCount
rs.Update

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.

You're using the "Sop01" again. So your rs.Fields("Sop01" & intCount) is equivalent to rs!Sop011 (for first field) rs!Sop012 for the second and so on. Take the "01" out, add the Format function and try it again. Use the same code as above.

 

[olushow]

Here is my dilemna.

With the previous box you assisted me with, the listbox was populated with just the "Sop01 through Sop30 data".

With this new form I am designing, I am using a combo box to populate a listbox. This works, but it also populates the listbox with other values that pertain to other fields, like BPRNumber and Area. Unlike the other form which just has the values for the "SOP##" field name.


Here is the code that populates the listbox.

Private Sub cboBpr_AfterUpdate()

On Error Resume Next

cboBpr.SetFocus

Dim db As DAO.Database
Dim strSQL As String
Dim strWhere As String
Dim rs As DAO.Recordset

strWhere = "WHERE BPRNumber = '" & cboBpr & "'"
strSQL = "SELECT * FROM tblBPRNumber " & strWhere
strSQL = strSQL & " ORDER BY BPRNumber;"

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then
'No data
Exit Sub
Else
rs.MoveLast
If rs.RecordCount <> 1 Then
'Too much data
Exit Sub
Else
For i = 0 To rs.Fields.Count - 1
strValues = strValues & rs.Fields(i) & ";"
Next
strValues = Left(strValues, Len(strValues) - 1)
End If
End If

Me.lstSops.RowSource = strValues



Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


If Not rs.BOF Then
Me.txtArea = rs("Area")
Me.lstSops.Requery
End If

rs.Close
Set rs = Nothing
db.Close
End Sub

My options are to limit the strSQL query, where by only the values for the "SOP##" fields show in my listbox, or to alter the VB line (***Problem Line***rs.Fields("Sop01" & intCount) = Me.lstControl.ItemData(intCount - 1)******,

I want to account for fields that are not named "Sop##". I am having a problem trying to do either of them.

 

[SuryaF]

Yes, all you have to do is change the

For i = 0 To rs.Fields.Count - 1
strValues = strValues & rs.Fields(i) & ";"
Next
strValues = Left(strValues, Len(strValues) - 1)

to
For i = 0 To rs.Fields.Count - 1
if left(rs.fields(i).name,3)="Sop" Then _
strValues = strValues & rs.Fields(i) & ";"
Next
strValues = Left(strValues, Len(strValues) - 1)

HTH