Solved

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

Posted on 2003-12-11
16
1,144 Views
Last Modified: 2007-12-19

'''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
0
Comment
Question by:lightcross
  • 9
  • 7
16 Comments
 
LVL 2

Expert Comment

by:rthomsen
ID: 9924566
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924598
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
 
LVL 2

Expert Comment

by:rthomsen
ID: 9924656
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924844
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924846
i understand what i just coded will not work.  but how can i make it work?
0
 
LVL 2

Author Comment

by:lightcross
ID: 9924865
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
 
LVL 2

Accepted Solution

by:
rthomsen earned 250 total points
ID: 9924867
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924895
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Expert Comment

by:rthomsen
ID: 9924923
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924930
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924944
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
 
LVL 2

Expert Comment

by:rthomsen
ID: 9924952
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
 
LVL 2

Author Comment

by:lightcross
ID: 9924963
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
 
LVL 2

Expert Comment

by:rthomsen
ID: 9924977
Great!.  I'm happy it worked out for you.

Thanks

rthomsen
0
 
LVL 2

Author Comment

by:lightcross
ID: 9925001
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
 
LVL 2

Expert Comment

by:rthomsen
ID: 9928688
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now