Results 1 to 11 of 11
  1. #1
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228

    combo box query based on different query result?

    hello all,

    i have a question regarding the structure of some queries i am creating.
    in this database (picture of form attached below) i have a query (call it qry1) that feeds a Work Order to a combo box which populates some fields. that part works great.



    from this work order, i have a list box that should be populated with the Dlot Numbers belonging to the work order. (A Work Order can have anywhere from 1 to 14 Dlot Numbers). i have created a separate query (call it qry2) for the list box, one that only has the fields Work Order and Dicing lot.

    How can i filter qr2 so that it shows only the Dlots belonging to the Work Order in the cbo box referenced above?

    i have tried various things as my criteria but none seemed to work.
    =[forms]![frm name]![cbo name]

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why doesn't it work - error message, wrong results, nothing happens? Review this tutorial about dependent comboboxes/listboxes http://datapigtechnologies.com/flash...combobox2.html

    Image did not attach to your post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    the query populates fine now, but i still can't get my list box to populate.

    this is the code im running:

    Code:
    Private Sub cbodicinglot_afterupdate()
    'This auto-populates the following fields:
    
    Me.TxtPartNumber = Me.CboDicingLot.Column(1)
    'Me.TxtWorkOrder = Me.CboDicingLot.Column(2)
    Me.TxtDiffusionLot = Me.CboDicingLot.Column(3)
    Me.TxtEvaporationLot = Me.CboDicingLot.Column(2)
    Me.TxtCutSize = Me.CboDicingLot.Column(5)
    Me.TxtStackSize = Me.CboDicingLot.Column(4)
    Me.lstdicinglots.Requery
    End Sub

  4. #4
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    cbodicinglot is fed by qry1, that populates up to line 9

    the query for lstdicinglots (line 10) is qry2.

    should it be something like:

    me.lstdicinglots.requery "qry2" ?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The method shown in tutorial did not help you?

    I don't really understand what you mean by 'feeds a Work Order to a combo box'.

    You can set the RowSource property of listbox then requery.
    Me.lstdicinglots.RowSource = "SELECT ..."
    Me.lstdicinglots.Requery

    Do you want to provide project for analysis?
    Last edited by June7; 02-15-2012 at 02:23 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    June7,
    I got it to work thanks to the tutorial, and thanks to your help of course!
    The next (and last step) is to populate some text fiends based on the DicingLots I click from my List box.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What fields do you need to populate with what data? Is this duplicating data?

    Why do you have similar named fields (Dicing Lot 1, Dicing Lot 2, etc)? This is indication data is not normalized.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    i need to populate the dicing lot 1-4 fields with the dicing lots from the list box.
    this is, in a sense, duplicating data; but our production process is a bit complicated.

    We have wafer stacks that we cut, each stack has its own dicinglot number.
    a work order may have up to 14 stacks (rarely do we see more than 14 per work order). When building the parts for the work order, a single part may use one or more of these dicing lots, (in no particular order). it is necessary for us to know which stacks were used in building this part, which is why i must have the operators select the dicing lots used.

    perhaps it would be better to create a single field (call it, DicingLotsUsed) instead of having the fields Dicing Lot 1-4. And when the operator selects the lots used, they will be saved to just that one field, instead of 4 different fields.

    (we never use more than 4 lots per part, that is why i only have dicing lots 1-4 instead of 1-14)

    i hope this makes sense.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not quite. I still don't understand how the fields are populated based on the listbox Dicing Lot selected. If each Dicing Lot has standard set of Dicing Lots (up to 4) just save the value from the listbox and then retrieve the Dicing Lots info by joining tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    Each work order has a set of dicing lots. To complete a work order may take up to several months. given this, we need to keep track of which dicing lots are being used and when; which is why i need to have the operators select from the list box the dicing lots they used.

  11. #11
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    hi june7,

    im very thankful for your help. i was able to manage my task using the code provided by http://support.microsoft.com/kb/827423

    i understand your point about data duplication, database normalization; due to our complicated production process, it is necessary that i save this information.

    although these dicing lots are stored individually in the database which creates them, in this database they will be stored in one field as a partial set belonging to the larger set from the work order. my goal here wasn't to discuss theory, but i really appreciate your concern for data duplication.

    thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query based on combo box
    By hawkins in forum Queries
    Replies: 10
    Last Post: 08-19-2011, 05:27 PM
  2. Replies: 10
    Last Post: 07-02-2011, 11:51 AM
  3. combo box based subform and query help required!!
    By crustycrab101 in forum Forms
    Replies: 3
    Last Post: 05-03-2011, 01:39 PM
  4. Running a query based on 2 combo boxes.
    By blessoni in forum Queries
    Replies: 4
    Last Post: 12-12-2010, 02:09 PM
  5. Query result based upon two field conditions
    By diane802 in forum Access
    Replies: 35
    Last Post: 01-08-2010, 06:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums