Results 1 to 5 of 5
  1. #1
    kpo's Avatar
    kpo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    38

    Which statement to use


    Back story: We specialize in servicing one type of product that comes from 4 different manufacturers. Those 4 manufacturers make collectively 16 different types and each type comes in completely different sizes even from the same manufacturer.

    Similar to a jean manufacturer. Levi jeans come in different styles (505, 515, 550) and different sizes (1, 3, 5, 7, etc.) for each style. But Wrangler makes different styles (Cowboy cut, Aura, Rock47) with completely different sizes (2, 4, 6, 8, etc.)

    I have created a
    -Manufacturers table where each record is a different manufacturer
    -table for each for each of the different manufacturers where each record is a different type/style
    -and a table for each of the types/styles where each record is a different size

    My Inventory table has fields for the Manufacturer, Type, Size among other things relevant to our industry.

    I am trying to set up a macro for my Inventory table to say that when [Manufacturer] = "Levi" to lookup a record from the "Levi Style" table and then depending on which style I chose I want it to lookup the record from the appropriate sizes table.

    I was trying to use the If Else statement but it doesn't seem to be working.

    I am new to access and just about ready to scream... any help would be greatly appreciated!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you should be using Forms as the means of interacting with your tables.
    Also, I think you should investigate Cascading comboboxes as the means of associating
    Styles with Manufacturers in a Search/Lookup a record.

    Getting your tables and relationships set up correctly is key to successful database.

    see post #2 at https://www.accessforums.net/showthr...lp-with-design

    for free video tutorials on database design and concepts

  3. #3
    kpo's Avatar
    kpo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    38
    Thank you orange.

    In my form I pasted the following code. I found it online and inserted the appropriate names of my tables and fields but it doesn't seem to be working.

    Private Sub cmboManu_AfterUpdate()
    Select Case cmboType.Value
    Case "Limitorque"
    cmboType.RowSource = "tblLimitorqueTypes"
    Case "Rotork"
    cmboType.RowSource = "tblRotorkTypes"
    Case "EIM"
    cmboType.RowSource = "tblEIMTypes"
    Case "Auma"
    cmboType.RowSource = "tblAumaTypes"
    End Select
    End Sub

    Am I at least heading in the correct direction?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you watch the free tutorials I recommended?

    Please describe exactly WHAT you are trying to do. We only know what you tell us, so please tell all that is relevant.
    Getting code online and adapting it to your needs is not an alternative to good design.

  5. #5
    kpo's Avatar
    kpo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    38
    I did watch the videos. Thank you.

    I was able to create cascading combo boxes with a Select Case statement I did myself.

    Thanks again for all your help!!

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

Similar Threads

  1. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  2. if-then statement in vba
    By rivereridanus in forum Programming
    Replies: 3
    Last Post: 07-07-2011, 02:11 PM
  3. If statement help please
    By bgeorge12 in forum Programming
    Replies: 12
    Last Post: 06-28-2011, 08:41 PM
  4. Help with IF statement
    By mkallover in forum Programming
    Replies: 7
    Last Post: 01-04-2011, 04:47 PM
  5. Help with Iif statement
    By tmcrouse in forum Queries
    Replies: 2
    Last Post: 09-02-2010, 09:00 AM

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