VBA Scripting

[M155_Access]

Hello peeps!

I have a simple Access form which tracks samples. These sample are taken out of the freezer and then put in a box to transport to another location. The location of the sample is then recorded using an Access form.

The box has 10 rows and 10 positions in each row.

For examples row 1 has positions 1 through to 10, then moves to row 2, 1-10 until you get to row 10, position 10 and it loops back to row 1, position 1 in a new box.

What's the easiest way that I can auto fill the field + 1 each time until it reaches Row 10, Position 10 then looping back to row 1, position 1?

For example:
Row=1, Position=1


Row=1,Position=2
Row=1,Position=3
Row=1,Position =4
Row=1,Position=5 ..........
Row=10, Position=10
Row=1, Position=1.....

Thanks for your help in advance.

)

[Beetle]

Questions;

1) What is the structure of the relevant tables?

2)Can you describe the process in a bit more detail? Like;

• Are you tracking the boxes themselves with a unique box number or something?
• Do they always fill the box completely each time (100 samples)?
• Do the samples have incrementing values as well? In other words, do you want them to just be able to enter the first sample and then have everything else auto increment?
• Or do they have to enter each sample manually and you just want the Row and Position in the box to increment each time a sample is entered?

[M155_Access]

Hey Beetle,

Thanks for the reply.

This has been the first Access database I have created to please be gentle with me....


To answer you questions...
Are you tracking the boxes themselves with a unique box number or something? It is the sample that we are interested in but the boxes are given a unique box number so that if needed, we can retrieve the sample at a later date from the box.
For example, box 65 has 10 positions and 10 rows. Then the next box number will be 66 etc.
Sometimes the boxes are not completely full before they leave the lab in which case the researcher will change the box number (incrementally), and enter the first sample in that box at row 1 position 1, then row 1 position 2 etc.
The way the form works at the moment is that the researcher will part fill in the form when the sample is received and then return to the form to "check-out" the sample by entering the box number, row & position once it is moved out of the lab in the box.

Many thanks in advance for your expertise!

[Beetle]

OK, here is some sample code for you (error handling not included). This example is based on the following assumptions;

1) Regarding the relevant fields in the table;

• BoxName is Text data type
• Row and Position are Number data type.

2)The relevant Controls (text boxes) on the form are named;

• txtBoxName
• txtRow
• txtPosition

This code is written for the Got Focus event of the txtRow control on the form, but you could use a different event if you prefer. If your naming and data types match up with the above then you should be able to just copy/paste this;

Private Sub txtRow_GotFocus() 
   
    If Len(Nz(Me!BoxName)) = 0 Then
        MsgBox "Please enter a box."
        Me!txtBoxName.SetFocus
    ElseIf Len(Nz(Me!Row)) = 0 Then
        Dim intRow As Integer
        Dim intPos As Integer
        
        intRow = Nz(DMax("Row", "BloodSampleTBL", _
                 "BoxName=""" & Me!BoxName & """"), 1)
                                        
        intPos = Nz(DMax("Position", "BloodSampleTBL", _
                 "BoxName=""" & Me!BoxName & """ And Row=" & intRow), 0)
                 
        If intRow = 10 And intPos = 10 Then
            MsgBox "This Box is full." & vbNewLine _
                 & "Please enter a different Box Name."
            Me!txtBoxName.SetFocus
        Else
            Select Case intPos
                Case 10
                    intRow = intRow + 1
                    intPos = 1
                Case Is < 10
                    intPos = intPos + 1
            End Select
            With Me
                !Row = intRow
                !Position = intPos
            End With
        End If
    End If
    
End Sub

I would also add a couple of additional safeguards in case the users click around the focus event (causing the code never to fire) or if they manually enter an invalid value for either the Row or Position.

In the Got Focus event of the txtPosition control;

Private Sub txtPosition_GotFocus(Cancel As Integer)

    If Len(Nz(Me!BoxName)) = 0 Then
        MsgBox "Please enter a box."
        Me!txtBoxName.SetFocus
    ElseIf Len(Nz(Me!Row)) = 0 Then
        MsgBox "No Row value entered."
        Me!txtRow.SetFocus
    End If
    
End Sub

In the Before Update event of the form;

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Nz(Me!Row) < 1 Or Nz(Me!Row) > 10 Then
        MsgBox "Invalid value for the Box Row."
        Cancel = True
    ElseIf Nz(Me!Position) < 1 Or Nz(Me!Position) > 10 Then
        MsgBox "Invalid value for the Position."
        Cancel = True
    End If
    
End Sub

[M155_Access]

Thank you very much for the reply. I shall give it a go!

BW

[redbull]

lol What lab company is this for?

[M155_Access]

OK, so I have given the code you very kindly wrote a go. But for some reason, row no always =11 and position is always 1. Any ideas? I don't need to change the box number now, just the row & position.

Thanks again

[Beetle]

Can you post the code exactly as it currently is in your app?

[M155_Access]

Here's all the VBA I have so far in my DB...

Option Compare Database

Private Sub btnBuffy_Click()
If IsNull(Barcode.Value) = False Then
SampleType.Value = "Buffy Coat"
TubeType.Value = "EDTA"
TubeVol.Value = "9.0"
End If
End Sub
Private Sub btnPlasma_Click()
If IsNull(Barcode.Value) = False Then
SampleType.Value = "Plasma"
TubeType.Value = "EDTA"
TubeVol.Value = "9.0"
End If
End Sub


Private Sub btnSerum_Click()
If IsNull(Barcode.Value) = False Then
SampleType.Value = "Serum"
TubeType.Value = "Serum"
TubeVol.Value = "7.5"
End If
End Sub


Private Sub btnWhole_Click()
If IsNull(Barcode.Value) = False Then
SampleType.Value = "Whole Blood"
TubeType.Value = "EDTA"
TubeVol.Value = "2.6"
End If
End Sub




Private Sub btnCheckOut_Click()
If IsNull(TimeCollected.Value) = True Then
MsgBox "Please check-in a sample first."
ElseIf IsNull(TimeCollected.Value) = False Then
CollectedBy.Value = "Ania"
BoxName.Value = "Serum and Plasma"
Destination.Value = "CRI"
CheckOutDate.Value = Date
If Len(Nz(Me!BoxNo)) = 0 Then
MsgBox "Please enter a box."
Me!txtBoxNo.SetFocus
ElseIf Len(Nz(Me!Row)) = 0 Then
Dim intRow As Integer
Dim intPos As Integer

intRow = Nz(DMax("Row", "BloodSampleTBL", _
"BoxNo=""" & Me!BoxNo & """"), 1)

intPos = Nz(DMax("Position", "BloodSampleTBL", _
"BoxNo=""" & Me!BoxNo & """ And Row=" & intRow), 0)

If intRow = 10 And intPos = 10 Then
MsgBox "This Box is full." & vbNewLine _
& "Please enter a different Box Number."
Me!txtBoxNo.SetFocus
Else
Select Case intPos
Case 10
intRow = intRow + 1
intPos = 1
Case Is < 10
intPos = intPos + 1
End Select
With Me
!Row = intRow
!Position = intPos
End With
End If
End If
End If
End If
End Sub

Private Sub Comments_GotFocus()
DoCmd.RunCommand acCmdZoomBox
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me!Row) < 1 Or Nz(Me!Row) > 10 Then
MsgBox "Invalid value for the Box Row."
Cancel = True
ElseIf Nz(Me!Position) < 1 Or Nz(Me!Position) > 10 Then
MsgBox "Invalid value for the Position."
Cancel = True
End If
End Sub


Private Sub Form_Current()
If Me.Barcode = 0 Or IsNull(Me.Barcode) Then
Me.Barcode = Nz(DMax("Barcode", "BloodSampleTBL"), 95000) + 1
End If
End Sub


Private Sub Position_GotFocus(Cancel As Integer)
If Len(Nz(Me!BoxNo)) = 0 Then
MsgBox "Please enter a box."
Me!txtBoxNo.SetFocus
ElseIf Len(Nz(Me!Row)) = 0 Then
MsgBox "No Row value entered."
Me!txtRow.SetFocus
End If
End Sub

I'm sure you can work out the bits I've written !

[Beetle]

The way you've nested your If..Then statement may cause the Row/Position code never to run at all under certain circumstances. Also, the code I posted previously was just some rough example code which didn't really account for all possible scenarios. If you have some existing data in your table for the Row or Position number that falls outside the parameters (the parameters being numbers between 1 and 10) then that would throw the code off. If you can post a sample copy of your app with just a little bit of dummy data I can try to correct it for your specific circumstances. Otherwise, you can try to step through the code and determine where it's going wrong.