Using if and then with multiple lines of code

[neelsfer]

I first tried another method but it was not working.
Now I get this code only to work with the first part of the "If then" code. The 2nd and 3rd "if then" does not want to fire, and populate data to subform.
See screen pic.
The main form is called "frmrtMainc hip"
subform = RaceTimingSF3ch ip

Racenumbers are sometimes received in the strInput txtfield from RFID chips in this format - 0001000,0002000 ,0003000, and i then have to split it before populating it to the subform "Racenumber " field.

I do it successfully into "data1", "data2","da ta3" on the mainform.
My problem however, is to copy the Data 1-3 fields to "Racenumber " field in the subform "RaceTimingSF3c hip" after a new line has been added everytime. Only Data1 seems to work.
here is current code:

Private Sub strInput_AfterUpdate()
Me.Data1 = tbExtractStr(Me.strInput, "1", ",", "")
Me.Data2 = tbExtractStr(Me.strInput, "2", ",", "")
Me.Data3 = tbExtractStr(Me.strInput, "3", ",", "")
If Data1 > 0 Then
[Forms]![frmrtmainchip]![RaceTimingSF3chip].SetFocus
DoCmd.GoToControl ("RaceNumber")
DoCmd.GoToRecord , "", acNewRec
[Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceNumber] = Me.Data1
[Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceFinishTime] = Format(Now(), "General Date")
End If
If Data2 > 0 Then
[Forms]![frmrtmainchip]![RaceTimingSF3chip].SetFocus
DoCmd.GoToControl ("RaceNumber")
DoCmd.GoToRecord , "", acNewRec
[Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceNumber] = Me.Data2
[Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceFinishTime] = Format(Now(), "General Date")
End If

If Data3 > 0 Then
[Forms]![frmrtmainchip]![RaceTimingSF3chip].SetFocus
DoCmd.GoToControl ("RaceNumber")
DoCmd.GoToRecord , "", acNewRec
[Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceNumber] = Me.Data3
[Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceFinishTime] = Format(Now(), "General Date")
End If
End Sub

If i knew how, i would preferred to copy the the first 5 digits received (0001000) of a string (ie 0001000,0002000 ,0003000,) in the strInput field on mainform directly to the subform "Racenumber " field,followed by the next 5 then then next 5 etc
Now i have to split it first to secondary fields(data1,da ta2,data3) and then try and copy it to the subform.

Any suggestions please?

The module code i used for the above splitting if anybody is interested is:

Function tbExtractStr(strIn, intNeedSegment, strDelimiter, Optional strNotFound As String) As String

    ' Function to chop a input string into segments and return the requested segment
    ' Written and developed by Thomas M. Brittell
    ' Copyright 1998; All rights reserved.
    '
    ' strIn          - Input string to be segmented
    ' intNeedSegment - Indicates the segment to be returned
    ' strDelimiter   - The delimiter used to seperate each segment
    ' strNotFound    - When no segment is found return the specified string if provided
    '
   
    Dim intCurrentPosition As Integer
    Dim intFoundPosition   As Integer
    Dim intLastPosition    As Integer
    Dim intGetSegment      As Integer
    Dim wrkNotFound        As String
    
    If IsEmpty(strNotFound) Or strNotFound = "" Then
        wrkNotFound = ""
    Else
        wrkNotFound = strNotFound
    End If
    
    intCurrentPosition = 0
    intFoundPosition = 0
    intLastPosition = 0
    intGetSegment = intNeedSegment
    
    Do While intGetSegment > 0
        intLastPosition = intCurrentPosition
        'Find a occurance of the delimiter
        intFoundPosition = InStr(intCurrentPosition + 1, strIn, Left$(strDelimiter, 1))
        If intFoundPosition > 0 Then
            intCurrentPosition = intFoundPosition
            intGetSegment = intGetSegment - 1
        Else
            'End of input string so exit
            intCurrentPosition = Len(strIn) + 1
            Exit Do
        End If
    Loop
    'If nothing was found and you had at least one delimiter return ""
    If (intFoundPosition = 0) And ((intGetSegment <> intNeedSegment) And (intGetSegment > 1)) Then
        tbExtractStr = wrkNotFound
    Else
        'Return the segment between the last position and the current one
        tbExtractStr = Mid$(strIn, intLastPosition + 1, intCurrentPosition - intLastPosition - 1)
    End If

End Function

[imgnothumb]http://bytes.com/attachments/attachment/5745d1322939281/copy1.jpg[/imgnothumb]

[neelsfer]

If i can get this to work, then every single form and report works in my application. The rfid input works 100% if the cyclist finished 1 by 1 with gaps in between, but as soon as 2 or 3 crosses the finishline at the same time, then it can't deal with the multiple racenumbers in the strInput txtbox

[ADezii]

Is this what you are looking for?

Dim varRet As Variant
Dim strBaseString As String

strBaseString = "0001000,0002000,0003000"

varRet = Split(strBaseString, ",")

Debug.Print varRet(0)
Debug.Print varRet(1)
Debug.Print varRet(2)

0001000
0002000
0003000

[neelsfer]

Hi Adezi; yes the code arrives from the rfid receiver in any number sequence "0005000,000212 0,0002120" in the strInput txtbox, and then i need to get it into the subform with a racefinishtime attached. Sometimes it may even be a single number also ie 0008190, received in the strInput txtbox.
Subform

Racenumber field----Racefinishtime field in RacetimingT table
0005000 --------   08/10/2011 02:47:02 PM
0002120 --------   08/10/2011 02:47:00 PM
0002120 --------   08/10/2011 02:34:58 PM

in your example strBaseString = strInput txtbox on my mainform
the attached screenpic has arrows to explain it better.

[ADezii]

I see nothing but trouble using your current approach. Assuming the Record Source of the Sub-Form is the Table RaceTimingT, I would Add the Data directly to the Table itself, depending on the Number of Elements present in strInput, then Requery the Sub-Form, as in:

Private Sub strInput_AfterUpdate()
Dim varRet As Variant
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

If IsNull(Me![strInput]) Then Exit Sub

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)

varRet = Split(Me![strInput], ",")

Select Case UBound(varRet)      'How many Race Numbers?
  Case 0        '1 Race#
    With rst
      .AddNew
        'Must ADD the Child Linking Field
        ![RaceNumber] = varRet(0)
        ![RaceFinishTime] = Format(Now(), "General Date")
      .Update
    End With
  Case 1        '2 Race#'s
    With rst
      .AddNew       'ADD Race 1
        'Must ADD the Child Linking Field
        ![RaceNumber] = varRet(0)
        ![RaceFinishTime] = Format(Now(), "General Date")
      .Update
      .AddNew       'ADD Race 2
        'Must ADD the Child Linking Field
        ![RaceNumber] = varRet(1)
        ![RaceFinishTime] = Format(Now(), "General Date")
      .Update
    End With
  Case 2        '3 Race#'s
    With rst
      .AddNew       'ADD Race 1
        'Must ADD the Child Linking Field
        ![RaceNumber] = varRet(0)
        ![RaceFinishTime] = Format(Now(), "General Date")
      .Update
      .AddNew       'ADD Race 2
        'Must ADD the Child Linking Field
        ![RaceNumber] = varRet(1)
        ![RaceFinishTime] = Format(Now(), "General Date")
      .Update
      .AddNew       'ADD Race 3
        'Must ADD the Child Linking Field
        ![RaceNumber] = varRet(2)
        ![RaceFinishTime] = Format(Now(), "General Date")
      .Update
    End With
  Case Else     'Who knows
    'Do Nothing
End Select

rst.Close
Set rst = Nothing

'Requery the Record Source of the Sub-Form
End Sub

[neelsfer]

Thx Adezi you made my day and week and year.
Just one last question about it.

If the strInput code is with a comma after the last number ie "1100,1002, " and not "1100,1002" - how would i change the

varRet = Split(Me![strInput], ",")

[ADezii]

Just a simple adjustment should do it, see Code lines: 11, 13, 15-20

Private Sub strInput_AfterUpdate()
Dim varRet As Variant
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

If IsNull(Me![strInput]) Then Exit Sub

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)

Dim strInputString As String        'Move with other Declarations

strInputString = Me![strInput]

'See if Trailing Comma (,) is present, if so Extract it!
If Right$(strInputString, 1) = "," Then
  strInputString = Left$(strInputString, Len(strInputString) - 1)
End If

varRet = Split(strInputString, ",")
'*********************** Code Intentionally Omitted ***********************

[neelsfer]

Thx Adezi. Without you and Neopa and other experts and lots of patience, it would not have been possible for me to do this. I owe you bigtime.

I use logger software that copies the RFID data straight into my original application into this strInput txtbox - if you ever have somebody with a similar query - it works great "http://www.aggsoft.com/tcpip-data-logger.htm"

[neelsfer]

Sorry i am back - one small bug
I need to setfocus back to the StrInput textbox on the mainform after the above racenumber was copied.
If the data was copied to the " RaceTimingSF3ch ip" subform, i would have used

[Forms]![frmrtmainchip]![strInput].SetFocus

to return.

But with the recordset and RacetimingT table directly involved, it does not work. I do display the results from the RacetimeT table in the "RaceTimingSF3c hip" subform after requery
Any suggestions?

[ADezii]

@Neelsfer:
The problem is that, from the AfterUpdate() Event of a Control, you cannot directly return Focus to that Control.

The workaround is to set Focus to any other Control on the Main Form that can receive the Focus, then set Focus to your Control. Assuming you have a Text Box named Text1, the last 4 Lines of Code in the AfterUpdate() Event of strInput should be:

With Me
  ![Text1].SetFocus        'Can use any Control
  ![strInput].SetFocus
End With

[neelsfer]

thx Adezi. Works great now. I had to get the focus back to the strInput where the RFID offload the racenumber.
I learn a new thing every day!

[ADezii]

@neelsfer:

I learn a new thing every day!

So do I! (LOL).

[neelsfer]

Mr Adezi, Why sometimes if you change one thing, then another one does not work. It Drives me mad!!
With the previous (wrong) way of doing it, i had the following code in the subform that also adds a sequential number to the "Lapno" field in the same RacetimeT table depending on the current "Racenumber " and "RaceName"
I modified your code slightly to add the Racedate and Racename also to the RacetimeT.

With rst
            .AddNew
         'Must ADD the Child Linking Field
         ![RaceNumber] = varRet(0)
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![Racedate] = [Forms]![frmrtmain]![RacingDate]
         ![RaceName] = [Forms]![frmrtmain]![RaceName]
       .update
        End With

this is from my subform

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim lngLastLapNo As Long

   Me.RaceFinishTime = Format(Now(), "General Date")
   
    lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = " & Me![RaceNumber] & _
" AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
   If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
  Me![LapNo] = 1
  Else
   Me![LapNo] = lngLastLapNo + 1
     End If
   
Exit_Form_BeforeUpdate:
  Exit Sub

Err_Form_BeforeUpdate:
MsgBox Error$
    Resume Exit_Form_BeforeUpdate
End Sub

[neelsfer]

Mr Adezi, Why sometimes if you change one thing, then another one does not work. It Drives me mad!!
With the previous (wrong) way of doing it, i had the following code in the subform that also adds a sequential number to the "Lapno" field in the same RacetimeT table depending on the current "Racenumber " and "RaceName" when the athlete does numerous laps around a course
I modified your code slightly to add the Racedate and Racename also to the RacetimeT.
Part of your current code that works well without this "lapno" field

With rst
            .AddNew
         'Must ADD the Child Linking Field
         ![RaceNumber] = varRet(0)
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![Racedate] = [Forms]![frmrtmainchip]![RacingDate]
         ![RaceName] = [Forms]![frmrtmainchip]![RaceName]
       .update
        End With

this is from my subform

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim lngLastLapNo As Long
 
    lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = " & Me![RaceNumber] & _
" AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
   If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
  Me![LapNo] = 1
  Else
   Me![LapNo] = lngLastLapNo + 1
     End If
   
Exit_Form_BeforeUpdate:
  Exit Sub

Err_Form_BeforeUpdate:
MsgBox Error$
    Resume Exit_Form_BeforeUpdate
End Sub