The automated tape library/storage robot was just an attempt to show storage locations within cases, racks,modules.... see this for a better sample.
I have looked at the data a little more but have focused on the storage aspect.
How do you relate/associate barcodes with your "samples".
I have set up a table called tblCryoStorage that encompasses freezers,canes, boxes,boxColumns and boxRows. This is representing all of your Cryostorage locations.
When you assign a barcode to a location the meaning is "putting a sample at position X in the freezer".
Barcode becomes the key to your operation. By means of the barcode you can uniquely identify the sample/ingredients to the related entities and to the storage location within your "cryosphere".
Here's a jpg of the table with some sample data (again I'm focused on the storage)
Here is some code to add random barcodes(samples) to random locations in the CryoTable. Some of the code are based on Steve's materials.
Code:
'---------------------------------------------------------------------------------------
' Procedure : addRandomSamples
' Author : mellon
' Date : 03/06/2016
' Purpose : This is a mock up of storing a number of "vials/samples" in to cryostorage.
' For illustration of the concept I chose to place 28 samples with Random Barcodes into
' randomLocations in the current cryostorage made up of 2 freezers, having similar geography
' 9 Canes containing 8 Boxes each box having 12 columns and 8 rows. Canes, boxes and columns are numbered from 1.
' Rows are identified A thru H.
'
'There are 17280 unique locations in 2 freezers with the above configuration.
'---------------------------------------------------------------------------------------
'
Sub addRandomSamples()
'to store 28 barcodes in cryostorage
Const maxstor As Long = 17280 'max locations in 2 freezers with geography stated above
Dim samp As Long: samp = maxstor
Dim sSQL As String
Dim iRecsToAdd As Integer: iRecsToAdd = 0
Dim randStorage As Long
Dim randBarcode As Long
On Error GoTo addRandomSamples_Error
Do Until iRecsToAdd = 28
randStorage = randomNumber(1, maxstor)
'make sure we are adding to locations that are NOT currently occupied
If DLookup("isOccupiedYN", "tblCryostorage", "CryoRecordId = " & randStorage) = 0 Then
randBarcode = GetRandomBarCode 'get a random barcode
'make sure random barcode is not in use
If DCount("barcode", "tblCryostorage", "barcode=" & randBarcode) = 0 Then
Debug.Print DLookup("isoccupiedYN", "tblCryoStorage", "CryoRecordId=" & randStorage)
Debug.Print randStorage & " " & randBarcode
sSQL = "UPDATE tblCryoStorage SET "
sSQL = sSQL & " Barcode=" & randBarcode
sSQL = sSQL & ", isOccupiedYN = True WHERE CryoRecordId =" & randStorage
Debug.Print sSQL
CurrentDb.Execute sSQL, dbFailOnError
iRecsToAdd = iRecsToAdd + 1
Debug.Print "iRecsToAdd " & iRecsToAdd
End If
End If
Loop
MsgBox "Cryostorage update completed " & Now
On Error GoTo 0
Exit Sub
addRandomSamples_Error:
MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure addRandomSamples of Module Module1"
End Sub
Code:
'---------------------------------------------------------------------------------------
' Procedure : randomNumber
' Author : mellon
' Date : 03/06/2016
' Purpose :To create a random number
'---------------------------------------------------------------------------------------
'
Function randomNumber(Lo As Long, Hi As Long) As Long
10 On Error GoTo randomNumber_Error
20 Randomize
30 randomNumber = Int((Hi - Lo + 1) * Rnd + Lo)
60 On Error GoTo 0
70 Exit Function
randomNumber_Error:
80 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure randomNumber of Module Module1"
End Function
Code:
'---------------------------------------------------------------------------------------
' Procedure : GetRandomBarCode
' Author : mellon
' Date : 03/06/2016
' Purpose : Routine to provide a barcode to a sample in this demo/mock up.
'There will need to be some barcode authority in the operational system.
'---------------------------------------------------------------------------------------
'
Function GetRandomBarCode()
'8 digit barcodes in this mockup
GetRandomBarCode = randomNumber(10000000, 99999999)
End Function
Query to show what storage is currently occupied.
Code:
SELECT TblCryoStorage.*
FROM TblCryoStorage
WHERE (((TblCryoStorage.IsOccupiedYN)=True));
I think this storage approach simplifies some coding/logic. After set up, transactions will be updates.
If history including who did what to what when etc then a transaction log record should be written when the sample is placed into storage. Again, barcode will be a major key to linking materials required in the log.
Queries can be set up for things like: (again I focused on storage)
What storage is occupied/unoccupied?
Where are the specimens related to X?
What is in Cane Y of Freezer 2?
What interaction with storage did "person3" have since Date XX?
How long has specimen WW been in storage?
Who added materials to Freezer 2 in June 2016?
Hope this is helpful.
Comments???