help with DAO. adding selected listbox items from two seperate listboxes


'''Right here I have all items in "ListBoxWaferID" selected and I am creating a new record for every item
 For Each varItm In Me.ListBoxWaferID.ItemsSelected
   Me.tmpWaferID.value = Me.ListBoxWaferID.ItemData(varItm)
   varWaferID = Me.tmpWaferID
     
     Set rstWaferTable = db.OpenRecordset("tblWaferID", dbOpenDynaset, dbAppendOnly)
     With rstWaferTable
      .AddNew
            rstWaferTable![LotID] = varLotID
            rstWaferTable![WaferID] = varWaferID
      rstWaferTable.Update
      rstWaferTable.Close
      End With
 
'Right here I am putting the same "varWaferID"  in a seperate table
    Set rstLayerTable = db.OpenRecordset("tblWaferIDToLayer", dbOpenDynaset, dbAppendOnly)
    With rstLayerTable
      .AddNew
            rstLayerTable![WaferID] = varWaferID
      rstLayerTable.Update
      rstLayerTable.Close
      End With

'Right here I would like to append data from "listBoxLayerInfo" to the "varWaferID" created in "tblWaferIDToLayer".  
'There are about 6 columns in the listbox
'[Layer], [Field], [Address Size], [Mask Grade], and [Percent Clear]
''''''
''''''
''''''
''''''

   Next
LVL 2
lightcrossAsked:
Who is Participating?
 
rthomsenCommented:
For Each varItm In Me.ListBoxWaferID.ItemsSelected
   Me.tmpWaferID.value = Me.ListBoxWaferID.ItemData(varItm)
   varWaferID = Me.tmpWaferID
     
     Set rstWaferTable = db.OpenRecordset("tblWaferID", dbOpenDynaset, dbAppendOnly)
     With rstWaferTable
      .AddNew
            rstWaferTable![LotID] = varLotID
            rstWaferTable![WaferID] = varWaferID
      rstWaferTable.Update
      rstWaferTable.Close
      End With
   
    Set rstLayerTable = db.OpenRecordset("tblWaferIDToLayer", dbOpenDynaset, dbAppendOnly)
    With rstLayerTable
         For LayerIndex = 0 To ListBox.ListCount - 1
      If ListBox.Selected(Index) = True Then
        .AddNew
         rstLayerTable![WaferID] = varWaferID
         rstLayerTable![LayerNameRev] = listbox.Column(1, index))
         rstLayerTable![Field] = listbox.Column(2, index))
         rstLayerTable![AddressSize] = listbox.Column(3, index))
         rstLayerTable![MaskGrade] = listbox.Column(4, index))
         rstLayerTable![PercentClear] = listbox.Column(5, index))
         rstLayerTable.Update
      End If
   Next LayerIndex
            rstLayerTable.Close
      End With
Next
0
 
rthomsenCommented:
I'm not sure if I understand your question but I think you are trying to figure out how to access the 6 columns in your other listbox to add it to the database.

Try using this
for index = 0 to lstbox.listcount -1
   If listbox.Selected(index) = True Then
       col1value = listbox.Column(0, index))
       col2value = listbox.Column(1, index))
       col3value = listbox.Column(2, index))
   End If
next index

I hope this helps
0
 
lightcrossAuthor Commented:
yes thanks.  i know how to access the columns however i dont know how to include it into my current code.  the column data needs to be included as you have it for each waferid added in table "tblWaferIDToLayer".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rthomsenCommented:
Do you  need to get the ID generated from the database when you added the Record to the tblWaferIDToLayer table?

With rstLayerTable
      .AddNew
            rstLayerTable![WaferID] = varWaferID
            IDvariable = rstLayerTable.fields("GeneratedIDFieldName")
      rstLayerTable.Update
      rstLayerTable.Close
      End With

Otherwise why can it not be added using the same method as above with varwaferID variable?
0
 
lightcrossAuthor Commented:
ok, think of something like this
For Each varItm In Me.ListBoxWaferID.ItemsSelected
   Me.tmpWaferID.value = Me.ListBoxWaferID.ItemData(varItm)
   varWaferID = Me.tmpWaferID
     
     Set rstWaferTable = db.OpenRecordset("tblWaferID", dbOpenDynaset, dbAppendOnly)
     With rstWaferTable
      .AddNew
            rstWaferTable![LotID] = varLotID
            rstWaferTable![WaferID] = varWaferID
      rstWaferTable.Update
      rstWaferTable.Close
      End With
   
    Set rstLayerTable = db.OpenRecordset("tblWaferIDToLayer", dbOpenDynaset, dbAppendOnly)
    With rstLayerTable
      .AddNew
            rstLayerTable![WaferID] = varWaferID
For LayerIndex = 0 To ListBox.ListCount - 1
   If ListBox.Selected(Index) = True Then
       rstLayerTable![LayerNameRev] = listbox.Column(1, index))
       rstLayerTable![Field] = listbox.Column(2, index))
       rstLayerTable![AddressSize] = listbox.Column(3, index))
       rstLayerTable![MaskGrade] = listbox.Column(4, index))
       rstLayerTable![PercentClear] = listbox.Column(5, index))
   End If
Next LayerIndex
           
      rstLayerTable.Update
      rstLayerTable.Close
      End With
   Next
0
 
lightcrossAuthor Commented:
i understand what i just coded will not work.  but how can i make it work?
0
 
lightcrossAuthor Commented:
or maybe something more like this....

For Each varItm In Me.ListBoxWaferID.ItemsSelected
   Me.tmpWaferID.value = Me.ListBoxWaferID.ItemData(varItm)
   varWaferID = Me.tmpWaferID
     
     Set rstWaferTable = db.OpenRecordset("tblWaferID", dbOpenDynaset, dbAppendOnly)
     With rstWaferTable
      .AddNew
            rstWaferTable![LotID] = varLotID
            rstWaferTable![WaferID] = varWaferID
      rstWaferTable.Update
      rstWaferTable.Close
      End With
   
    Set rstLayerTable = db.OpenRecordset("tblWaferIDToLayer", dbOpenDynaset, dbAppendOnly)
    With rstLayerTable
      .AddNew
            rstLayerTable![WaferID] = varWaferID
For Each LayerIndex In Me.ListBox.ItemSelected
       rstLayerTable![LayerNameRev] = listbox.Column(1, index))
       rstLayerTable![Field] = listbox.Column(2, index))
       rstLayerTable![AddressSize] = listbox.Column(3, index))
       rstLayerTable![MaskGrade] = listbox.Column(4, index))
       rstLayerTable![PercentClear] = listbox.Column(5, index))
Next LayerIndex
           
      rstLayerTable.Update
      rstLayerTable.Close
      End With
   Next
0
 
lightcrossAuthor Commented:
ok, now we are on to something.  

current problem is that it is only adding the first item in listbox rather then only the items that are selected.  
0
 
rthomsenCommented:
I can't see why it wouldn't be adding all of the selected items in listbox.  You should set a break in the code and step through it using F8 to see why only 1 record is being added.
0
 
lightcrossAuthor Commented:
it is not adding only 1 record.  it is adding only the first record 10 times.  10 times is the number of total items in the listbox.
0
 
lightcrossAuthor Commented:
it appears to be having a problem with this:
rstLayerTable![LayerNameRev] = listbox.Column(1, index))


if i change it to this then it is okay.  but its the reason why its adding only the first item in listbox.  
rstLayerTable![LayerNameRev] = listbox.Column(1)

possibly there is something wrong with this:
 For LayerIndex = 0 To ListBox.ListCount - 1  
      If ListBox.Selected(Index) = True Then
0
 
rthomsenCommented:
Here is the problem:

For LayerIndex = 0 To ListBox.ListCount - 1  
      If ListBox.Selected(Index) = True Then

You are incrementing a variable called Layerindex but using a variable called index to access the list.
0
 
lightcrossAuthor Commented:
nope... i found the problems.  

(1)we somehow started interchanging "LayerIndex" and "Index"!    

(2)we had one too many parenthesis in our column code.

so here is the final which works perfectly!  thanks for all your help!  you did exactly what I wanted I just made things more difficult by trying to adjust your "index" to something more understandable to myself and neglected to implement it elsewhere.

For Each varItm In Me.ListBoxWaferID.ItemsSelected
   Me.tmpWaferID.value = Me.ListBoxWaferID.ItemData(varItm)
   varWaferID = Me.tmpWaferID
     
     Set rstWaferTable = db.OpenRecordset("tblWaferID", dbOpenDynaset, dbAppendOnly)
     With rstWaferTable
      .AddNew
            rstWaferTable![LotID] = varLotID
            rstWaferTable![WaferID] = varWaferID
      rstWaferTable.Update
      rstWaferTable.Close
      End With
   
    Set rstLayerTable = db.OpenRecordset("tblWaferIDToLayer", dbOpenDynaset, dbAppendOnly)
    With rstLayerTable
         For Index = 0 To ListBox.ListCount - 1
     
      If ListBox.Selected(Index) = True Then
        .AddNew
         rstLayerTable![WaferID] = varWaferID
         rstLayerTable![LayerNameRev] = ListBox.Column(1, Index)
         rstLayerTable![Field] = ListBox.Column(2, Index)
         rstLayerTable![AddressSize] = ListBox.Column(3, Index)
         rstLayerTable![MaskGrade] = ListBox.Column(4, Index)
         rstLayerTable![PercentClear] = ListBox.Column(5, Index)
         rstLayerTable.Update
      End If
   Next Index
            rstLayerTable.Close
      End With
Next
0
 
rthomsenCommented:
Great!.  I'm happy it worked out for you.

Thanks

rthomsen
0
 
lightcrossAuthor Commented:
i have one more question for you though...

how would i make it error proof if any one of the column values were null

         rstLayerTable![WaferID] = varWaferID
         rstLayerTable![LayerNameRev] = ListBox.Column(1, Index)
         rstLayerTable![Field] = ListBox.Column(2, Index)
         rstLayerTable![AddressSize] = ListBox.Column(3, Index)
         rstLayerTable![MaskGrade] = ListBox.Column(4, Index)  ''' <-------for instance, maybe this value is empty.  currently it errors out.  i would like it to continue to next
         rstLayerTable![PercentClear] = ListBox.Column(5, Index)
0
 
rthomsenCommented:
The column property won't return null.  If it is blank it will return "", the empty string.  Make sure in your table you have the allowzerolength property set to true for all text fields.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.