Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 46
  1. #16
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    BINLOC-davegri-v01.zip
    This db uses SQL to create tblBins records with any number of bins, rows, columns and compartments that you choose.
    It uses 4 levels of nested for-next looping to create the SQL for each record in the table. Records are inserted via currentdb.execute
    Last edited by davegri; 06-05-2018 at 11:47 AM. Reason: edit

  2. #17
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    davegi, This is perfect! Works great and I'm going to look at it more to see you did it. Thank you so much!!!

  3. #18
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Glad to help. Nice little exercise. Good luck with the rest of your project.

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by MadTom View Post
    Yes I should add BinNum, no problem!
    As I did in post 7 but the BinCompartment field isn't necessary regardless of where the illustration is showing that it belongs.
    You are only dealing with 50 rows if there are 50 bins, and you can only populate the bin data since it's the only thing that's relative to the record before it. So unless someone already has what you need in their arsenal, it would take far longer to write such a recursive query than it would take you to enter the 50 rows manually. If you must, drag through Excel to auto populate 50 numbers and Paste/Append into your bin table.

    EDIT: should have refreshed after trying in Access and before finishing the post. I shouldn't be surprised that someone had it at the ready.
    Or am I just getting too old to post answers here?

  5. #20
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    I see you used VB, I'm going over it and will teach me a lot! so much to learn! THANKS

  6. #21
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    davegri, Why does the BinIDPK field keep growing larger? Every time it runs keeps adding on from last run. Thanks!

  7. #22
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    MadTom,

    Can you tell us in simple English what goes into a Bin? How is a BIN divided/subdivided?
    And how do the BINs reflect/relate to your tblCapacitors, tblResistors.tblDiodes....?
    I'm still confused with the set up given that you have these tables.

  8. #23
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    davegri, Why does the BinIDPK field keep growing larger? Every time it runs keeps adding on from last run. Thanks!
    After emptying the table, do a compact/repair before building the table again.

  9. #24
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    I store my parts, resistors, Capacitors, Diodes, small parts in polypropylene, dividable drawers offer easy content identification.
    Each drawer can have a divider across the middle of its width, which you can easily make from cardboard or cereal boxes instead of buying. Drawers are in rows and columns. So when I lists my parts in my tables I can add a location where I can find it. Thanks!

    https://www.amazon.com/s/ref=nb_sb_s...d=SANL1UCMI2SE

    https://www.amazon.com/Akro-Mils-101...arts+organizer

  10. #25
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    MadTom,
    I am familiar with the plastic cases and the compartments ---fishing tackle, small nuts and bolts etc. Thanks.

    As for
    Why does the BinIDPK field keep growing larger? Every time it runs keeps adding on from last run
    .

    If you have 40 cases/Bins that seems to be a fixed number. So it should not (necessarily) change. It could change if you add (or remove) cases. But your description sounds similar to X rooms on a floor in a Building. Just because you move something into a Room, the room number shouldn't increase. Maybe I'm missing something basic.

    As I understand your set up (and please change as necessary to give us the context):

    - you have X Bins
    - each BIN is configured into horizontal(rows) and vertical(columns)
    - the number of rows and columns is specific to 1 or more Bins
    - a storage location exists in a Bin at position row, column (much like a matrix)
    - a Bin has Row x Column storage locations
    - the total storage locations for the "current Bin set up" is Sum(of each Bin's Rows x Columns)
    - no 2 Bins necessarily have the same configuration of Rows and Columns
    - a Bin's configuration can be changed
    - new Bin(s) can be added to the "current Bin set up"
    - an existing Bin could be removed from the "current Bin set up"

    It seems a lot like a bunch of "pigeon holes" where you store "things". The number of "pigeon holes" doesn't change unless you physically add or delete them.

    Good luck with your project.


    Off topic for your set up, but an interesting thread with a similar concept from a couple of years ago. It is more complex (putting multiple samples and replicates in various storage locations in different freezers) than your project, but could be good to read through the dialog. www.accessforums.net/showthread.php?t=60035

  11. #26
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Orange, I could name each drawer and compartment, but this way with 10 rows, 5 cols, 4 comps (each drawer) = 200 locations. I can use this table to lookup and add location. So my 2n3906 transistors location maybe BinQ 59 ( row 3, col 5, comp 3). 5K resistor maybe in BinR 192 (row 10, col 3, Comp 4). Now with this Form I can add a table for each set of bins (Drawers) some may have different number of rows, cols and comp. I have 6 or 7 sets of bins for different parts. I hope this makes sense. Thanks for your help!
    Last edited by MadTom; 06-05-2018 at 06:05 PM. Reason: change

  12. #27
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    davegri, I've been going over your code and learning how it works, works great! I though I would add letters to nRow . I can get the letter inserted into sSQL = "INSERT INTO tblBins (BinNum, RowNum, ColNum, CompNum) VALUES (" & nBin & ", " & Lin & ", " & nCol & ", " & nCom & ")" , but will not run CurrentDb.Execute sSQL, dbFailOnError with out an error. I changed the RowNum field to short text in the table, is there something else in the table I have to change? Lin is set to Dim Lin As String. I know the correct letter is there I can see it in MsgBox Values (1, A, 1, 1). Thanks for your Help!

    Quote Originally Posted by davegri View Post
    BINLOC-davegri-v01.zip
    This db uses SQL to create tblBins records with any number of bins, rows, columns and compartments that you choose.
    It uses 4 levels of nested for-next looping to create the SQL for each record in the table. Records are inserted via currentdb.execute

  13. #28
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    In the table, change BinNum to short text and use revised function:
    Code:
    Public Function fcnBuildTable()
        Dim sSQL As String
        Dim nKey As Long
        Dim sBin As String
        Dim nBin As Long
        Dim nCol As Long
        Dim nCom As Long
        Dim nRow As Long
        For nBin = 1 To fBins
            sBin = "R" & nBin
            For nRow = 1 To fRows
                For nCol = 1 To fCols
                    For nCom = 1 To fComs
                        sSQL = "INSERT INTO tblBins (BinNum, RowNum, ColNum, CompNum) VALUES ('" & sBin & "', " & nRow & ", " & nCol & ", " & nCom & ")"
                        'Debug.Print sSQL
                        CurrentDb.Execute sSQL, dbFailOnError
                    Next nCom
                Next nCol
            Next nRow
        Next nBin
        DoCmd.OpenTable "tblBins"
        DoCmd.SelectObject acTable, "tblBins"
        DoCmd.Requery
        DoCmd.SelectObject acForm, "frmBuild"
    End Function
    EDIT: Sorry, just noticed that your wanted a ROW to be a string. Same modification applies, just adjust for row instead of bin.
    Last edited by davegri; 06-07-2018 at 07:52 AM. Reason: BooBoo

  14. #29
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    BINLOC-davegri-v02.zip

    Better yet, here's a revised DB. It allows you to specify the row prefix on the Build Form.
    Also included is a data entry form for adding parts to the bins.
    A parts type table (resistor, diode, IC, etc.) is included for the dropdown.
    Using this method, you won't need an individual table for each type of parts like resistors, caps, etc.

    The setup is a main/subform, with Bins as the main and parts as the subform.
    The code restricts the subform to only one record per bin location, as you probably don't want to mix parts.

  15. #30
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    I see what I needed! I didn't try your code yet, but this is how I did it. You are the BEST! Thanks for your time.

    Public Function fcnBuildTable()
    Dim sSQL As String
    Dim nKey As Long
    Dim nBin As Long
    Dim nCol As Long
    Dim nCom As Long
    Dim nRow As Long
    Dim Letter As String
    Dim Lin As String
    Letter = "A"
    For nBin = 1 To fBins
    For nRow = 1 To fRows
    Lin = Letter
    Letter = Chr(Asc(Letter) + 1)
    For nCol = 1 To fCols
    For nCom = 1 To fComs
    sSQL = "INSERT INTO tblBins (BinNum, RowNum, ColNum, CompNum) VALUES (" & nBin & ", '" & Lin & "', " & nCol & ", " & nCom & ")"
    'Debug.Print sSQL
    CurrentDb.Execute sSQL, dbFailOnError
    Next nCom
    Next nCol
    Next nRow
    Next nBin
    DoCmd.SelectObject acTable, "tblBins"
    DoCmd.Requery
    DoCmd.SelectObject acForm, "frmBuild"
    End Function

    Quote Originally Posted by davegri View Post
    In the table, change BinNum to short text and use revised function:
    Code:
    Public Function fcnBuildTable()
        Dim sSQL As String
        Dim nKey As Long
        Dim sBin As String
        Dim nBin As Long
        Dim nCol As Long
        Dim nCom As Long
        Dim nRow As Long
        For nBin = 1 To fBins
            sBin = "R" & nBin
            For nRow = 1 To fRows
                For nCol = 1 To fCols
                    For nCom = 1 To fComs
                        sSQL = "INSERT INTO tblBins (BinNum, RowNum, ColNum, CompNum) VALUES ('" & sBin & "', " & nRow & ", " & nCol & ", " & nCom & ")"
                        'Debug.Print sSQL
                        CurrentDb.Execute sSQL, dbFailOnError
                    Next nCom
                Next nCol
            Next nRow
        Next nBin
        DoCmd.OpenTable "tblBins"
        DoCmd.SelectObject acTable, "tblBins"
        DoCmd.Requery
        DoCmd.SelectObject acForm, "frmBuild"
    End Function
    EDIT: Sorry, just noticed that your wanted a ROW to be a string. Same modification applies, just adjust for row instead of bin.

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

Similar Threads

  1. Replies: 4
    Last Post: 04-07-2018, 11:55 PM
  2. Replies: 3
    Last Post: 06-01-2017, 06:57 AM
  3. Replies: 2
    Last Post: 01-20-2014, 12:50 PM
  4. Replies: 2
    Last Post: 10-09-2012, 03:20 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Tags for this Thread

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