Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    Dave, this is precisely what I am looking to do, except the second drop down needs to be able to select the part type (Capacitor or Blower, from there it will show the part number in the part number field you created. Hope that make sense. Looks like you are a master of your craft. Thank you.

  2. #17
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    BakerMan,

    I am far from the master, most on this forum can run circle around me, they deserve far more credit than I do, I learn from them everyday.

    I don't think your table structure will get you where you want to be. I will try to modify it to what I understand your looking for and see if that is correct.

    Dave

  3. #18
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    Much appreciated. This is a project that would greatly reduce the amount of time we spend day to day matching up xz axis from an excel spreadsheet to find a part number. Thank you again!

  4. #19
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    MasterPartList.zip

    You can do what you want with your current structure, but you have to be a lot more capable in your programming.
    What you want is something like

    Code:
    tblPartType
    PT_ID  PT_Name
    1      Blower
    2      Capacitor
    
    tblModel
    M_ID  M_Name
    1     Model1
    2     Model 2
    
    tblModelPartSerial
    MPS_ID  M_ID  PT_ID  MPS_Number
    1       1     2      SG400Blower#
    2       1     1      SG400Cap#
    3       2     2      SG600Blower#
    4       2     1      SG600Cap#
    Which is a 'normalized' data structure mentioned in several prior posts on this page.

    Then you can simply use cascading combo boxes and apply a criteria to get what you're looking for.

    All that being said, I'm enclosing a copy of your database showing you how to do it with your current structure. It's pretty dirty but it works. You'll have to do some cleanup if you want to exclude the model number from the list selecting the part.

  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bakerman,

    As discussed your table structure does not reflect your requirement. You have not "abstracted sufficiently" to deal with Parts. You should step back and see that you have Part, and the name of a specific part could be a Blower, Stainless,150cfm; another specific part could be a Capacitor, ceramic,10µF.

    If you have Items that are subdivided into Models (eg 40 in TV;46 in TV;80 in TV) and again by LED, LCD.... and each Model can be composed/uses different parts then your data model may look more like the one below.
    Here the Computer :
    -is a certain Type (desktop, laptop...)
    -has a specific ProcessorType
    -has a specific Manufacturer
    -- and each of these attributes are derived from their separate tables.


    You could have a table with a field PartType that would have values like:
    Blower
    Capacitor
    Gizmo

    If you have Models -whatever they are, and each model could have different parts and PartTypes could be considered some Categorization of Parts.

    It still isn't clear how Part and Model relate in your set up.

    Good luck with your project.

    Update: I agree with rpeare and his structure --models and parts related


    Click image for larger version. 

Name:	SellingComputersByPalletAtAuction.PNG 
Views:	22 
Size:	38.5 KB 
ID:	38375
    Last edited by orange; 05-10-2019 at 12:23 PM. Reason: updated rpeare has offered a normalized structure

  6. #21
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    BakerMan,

    Here is what I came up with real quick. See if this is what you want.



    MasterPartList-DB-1.zip


    Dave

  7. #22
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    MasterPartListEdit.zip

    @rpeare

    OK So this is exactly what I need! your gonna kill me but i realize i need the row and the column switched around. I will upload what I switched, and im missing updating some property somewhere, because when i rename the fields it broke.

  8. #23
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This line of code needs to be modified to reflect your new field name
    where before it was

    [part number]

    now it just needs to be

    [part]

    to reflect the change in your field name

    ssql = "SELECT [Part],[" & PartField & "] FROM Parts WHERE [Part] = '" & Model & "'"

    Let me stress again, orange and others have mentioned you should look at a more normalized structure, I agree with them, but this may give you a temporary solution until you figure that out.

  9. #24
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    I cannot modify my table because the company makes updates to an excel spreadsheet which would need to occasionally be imported & updated. So that would be the reason for me to need to keep this database structure. Honestly the template you made for me @rpeare is exactly the functionality I need. Thank you.

    PS. I have been struggling to figure out where to make the needed changes. I am receiving a prompt to enter parameter value. I know im asking a lot, but would you kindly be able to fix this? Thank you.

  10. #25
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    Where would I find this code? The change I made was the field name [Unit Model] was changed to [PART]

    Quote Originally Posted by rpeare View Post
    This line of code needs to be modified to reflect your new field name
    where before it was

    [part number]

    now it just needs to be

    [part]

    to reflect the change in your field name

    ssql = "SELECT [Part],[" & PartField & "] FROM Parts WHERE [Part] = '" & Model & "'"

    Let me stress again, orange and others have mentioned you should look at a more normalized structure, I agree with them, but this may give you a temporary solution until you figure that out.

  11. #26
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    in the ON CLICK event of the search button

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As suggested back in post 5, don't need button and VBA code.

    Instead of listbox on form, have a textbox with expression in ControlSource:

    =DLookUp("[" & [PartField] & "]","Parts","Part='" & [Model] & "'")
    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.

  13. #28
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    Solved. @rpeare for the win. This took care of what I needed. @orange, all you really did was compare apples to oranges. You really didn't help, you complicated matters. Thanks for trying. Again, big thanks to @rpeare for just simply reading my request and making the magic happen. I greatly appreciate your efforts!.

  14. #29
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Bakerman I really thought about your last post overnight and I think it's over the top. Orange spends time trying to get users to think about their business structure because that's the best way to start thinking about your data and how it fits into a relational database, on top of which there are *tons* of people who come here with bad database design and it helps us (the more seasoned folks) to understand what your problem really is, not just what is posted. I read his post and I agree with him on almost everything he wrote. Maybe we're just old hacks and don't phrase things in a way that may be useful to a newer programmer but he gave you useful information. Even if the structure of your data can't change, it doesn't mean you're stuck with it. You can build an import process to translate the spreadsheet into a normalized structure fairly painlessly which would lend itself to much more than just a lookup, which is where I think orange may have been going. Maybe even build a database with data entry so your users can abandon the spreadsheet altogether.

    If you didn't find it helpful you could have asked for clarification instead of cutting him down. I think you owe him an apology.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Replies: 2
    Last Post: 01-28-2016, 08:23 AM
  3. Replies: 5
    Last Post: 08-05-2013, 09:47 AM
  4. Search Form - Cascading Combo Boxes
    By WeeTerrier in forum Forms
    Replies: 7
    Last Post: 12-05-2011, 08:26 PM
  5. Creating a Form with 2 combo boxes and a text box
    By smorris4804 in forum Access
    Replies: 1
    Last Post: 06-17-2011, 02:06 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