Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-12-11
16
Medium Priority
?
1,150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

704 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