SQL Query to add RowNum and ColNum in PartsLocation Table

[MadTom]

Wow! This is Great, will save me lots of time. I'm learning as I go, examples can help a lot.

[MadTom]

Update and problem! I have a form that creates a table tblbins with fields Part_FK (autoNum), BinNum (Text) ( indexed and No Duplicates), RowNum (Text), ColNum (Text), ComNum (text). I can enter info. in the form and works great, but I want unique data entered into the BinNum field and sometime it give me a error message of duplicates and some times it adds duplicate data when I do a build. I've tried many things and can't seem to over come this problem. The error message is OK, but why does it add duplicate data sometime?

some of my code:

Public Function fcnBuildTable()
Dim sSQL As String
Dim nKey As Long
Dim sRow As Long
Dim sBin As String
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 nRow = 1 To fRows
Lin = Letter
Letter = Chr(Asc(Letter) + 1)
For nCol = 1 To fCols
For nCom = 1 To fComs
nBin = nBin + 1
sBin = Nz(sPfx + " ", "") & nBin
sSQL = "INSERT INTO tblBins (BinNum, RowNum, ColNum, CompNum) VALUES ('" & sBin & "', '" & Lin & "' , " & nCol & ", " & nCom & ")"
' Debug.Print sSQL

CurrentDb.Execute sSQL, dbFailOnError (I get error here if problem with build)

Next nCom
Next nCol
Next nRow
MsgBox "Insert successful"
DoCmd.OpenTable "tblBins"
DoCmd.SelectObject acTable, "tblBins"
DoCmd.Requery
DoCmd.SelectObject acForm, "frmBuild"
DoCmd.Close acTable, "tblBins"

Call resetAll
End Function

This is how I create the table:
Private Sub cmdCreate_Click()

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "CREATE TABLE tblBins( " _
& "Parts_FK counter primary key," _
& "BinNum text(50), " _
& "RowNum text(50), " _
& "ColNum text(50)," _
& "CompNum text(50))"

' Dim dbs As Database

db.Execute strSQL

' Create a unique index, BinID, on the
' BinNum field.
db.Execute "CREATE UNIQUE INDEX BinID " _
& "ON tblBins (BinNum) " _
& "WITH DISALLOW NULL;"
' Set db = Nothing
On Error Resume Next
DoCmd.Requery
End Sub

[davegri]

Why are you running this code more than once?
After the table is created, why would you run that code again? Why would you run the bin initialization code more than once?
Tell us more about what you are trying to accomplish in simple terms.

[MadTom]

If I run the function more then once I can add more location names, but I don't want duplicates. This is still work in progress and more of a learning thing! This is what my bins table could look like, and at a later date I may add another Tray or Bin Cab. Why does it allow the build to use the same data more than once? Thanks for your help!

tblBIns
Parts_FK BinNum RowNum ColNum CompNum
1 Top 1 A 1 1
2 Top 2 A 1 2
3 Top 3 A 2 1
4 Top 4 A 2 2
5 Bot 1 A 1 1
6 Bot 2 A 2 1
7 Bot 3 B 1 1
8 Bot 4 B 2 1
9 Tray #1 1 A 1 1
10 Tray #1 2 A 2 1
11 Tray #1 3 A 3 1
12 Tray #1 4 B 1 1
13 Tray #1 5 B 2 1
14 Tray #1 6 B 3 1
Cols don't line up as in table!

[orange]

Tom,
If this is not your basic BIN, then please adjust the attached so that your terminology is clear.

This seems to be a multi-row, multi-column "bin" with adjustable "paper or plastic inserts" to allow
multiple compartments. You may have many BINs where each BIN can have different configurations.

Any BIN configuration has a configuration (row, col, compartments) that you design/build/create. It only
changes when you make a physical change. You can have as many BINs as you need.

Revised graphic:

[MadTom]

This is some of what I have for parts many sizes and shapes, and I have bin cabinets also. BinNum has the Bin sPfx * Number (Tray #1 + number). Second run maybe (Top Bin + number) = BinNum. Function works great, just want to prevent duplicates in the BinNum field. Some times I get an error and other builds I don't, should get error when ever I use same data in BinNum.

[davegri]

Here is an updated DB from the one I posted at #29.
This one includes a computed key in each record that will prevent duplicates in tblBins.

BINLOC-davegri-v03.zip

[orange]

What exactly is a Tray?
What is a BinCabinet?
Where do these fit in the picture?

Perhaps others understand your setup, but I think there is still confusion.
In addition to the graphic presented in my previous post showing my understanding of Bin, Row, Column and Compartment,
I am posting another graphic for you to confirm what you are really dealing with. My view is there is confusion between your concept of a solution and some interim code. Like most database/application projects a clear understanding of requirements is the best starting point.



Perhaps you could adjust or confirm the piece as per your requirement.

A few points to consider:
( base assumption is that original configuration has "dividers" that provide R*C compartments)

-when you move a "divider" and create a "compartment" that spans more than 1 row and/or more than 1 column that is a Customization

-Cabinet, Bin, Row and Column only identify those storage locations where no customization has occurred
-once you make 1 or more customization(s) you could identify a Compartment table to identify current Compartments in a given Bin
-For example, BIN6 --with 6 Rows and 4 columns has 24 base compartments initially. If BIN6 is modified/customized such that (as per my original graphic) in Row 1 dividers between col1 and col2, col3 and col4 and col5 and col6 are removed, then you are left with 3 compartments in Row1. In the tblCompartment, you could number BIN compartments from upper left to lower right
showing Compartment1 now includes row1, col1 and row1, col2; Compartment2 includes row1, col3 and row1, col4....
-the implication of all of this is that ONLY the current configuration exists. If you make any Customization, you must update your tblCompartment accordingly and ensure you have moved all "items" from the old compartment(s) to this new compartment(s) or some existing Compartment.
-with any customization, row and col alone do not identify a compartment

Good luck with your project.

[orange]

@davegri,
Please describe the rationale for determining a compartment. Perhaps a graphic showing the set up would clarify things.

From your latest db (v03)
2 Bins, 5 rows, 4 columns and 13 compartments =====>; gives me 520 records???

It seems your interpretation would divide each row/col intersection into NumCompartment compartments and that every Bin and row and column would be exactly the same? Does it allow for various configurations in each Bin?

[MadTom]

I don't see how sDupBuild = nBin & sRow & nCol & nCom ? Where does it compare sDupBuild? I'm still looking and learning!

[davegri]

Orange:

Does it allow for various configurations in each Bin?

Obviously not. Simple looping code requires a rigid structure.
I shared your concerns when I designed the code, and had reservations about the variability, but it was what the OP asked for and approved.
I figured that any variation from the rigid structure would have to be handled manually.

[orange]

Davegri,

I still haven't found a confirmation or clear requirement from MadTom. He talked about plastic or paper dividers so my reaction was variation in "compartment geometry".I've offered some ideas, but have had no feedback.

[davegri]

I don't see how sDupBuild = nBin & sRow & nCol & nCom ? Where does it compare sDupBuild? I'm still looking and learning!

sDupBuild is written into each record in tblBins field DupCheck. DupCheck is indexed with no duplicates.
If the build code is run again with the same parameters that would cause duplicates in the table, the record is not added and the code continues.
The pertinent code to avoid the dups is in the error handler:

fcnBuildTable_Error:
    Select Case Err
        Case 3022       'duplicate, ignore the insert
            Resume Next
        Case Else
            MsgBox Err.Number & ":" & Err.Description
    End Select
    Resume fcnBuildTable_EXIT

[MadTom]

davegri, I see it now! I have the same set up on BinNum, but doesn't always work. I will take your code and use it for what I need.
Thanks agains for all your help, will post my project when done.

[MadTom]

Been working and learning on my first project! This is my Bins Form and Table DB, still work in progress and has a few bugs. This will make and add data for Bins, Trays or any type small storage units., Uses Letters for Rows, Numbers for Columns and any number of compartments. If any compartment isn't used add info. in Notes. Any suggestions would be great and thanks to this Forum and the members! Now I will add more Forms and Parts Tables to this project!