Solved

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

Posted on 2003-12-11
16
1,146 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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