By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Using if and then with multiple lines of code

100+
P: 547
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 "frmrtMainchip"
subform = RaceTimingSF3chip

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","data3" on the mainform.
My problem however, is to copy the Data 1-3 fields to "Racenumber" field in the subform "RaceTimingSF3chip" after a new line has been added everytime. Only Data1 seems to work.
here is current code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub strInput_AfterUpdate()
  2. Me.Data1 = tbExtractStr(Me.strInput, "1", ",", "")
  3. Me.Data2 = tbExtractStr(Me.strInput, "2", ",", "")
  4. Me.Data3 = tbExtractStr(Me.strInput, "3", ",", "")
  5. If Data1 > 0 Then
  6. [Forms]![frmrtmainchip]![RaceTimingSF3chip].SetFocus
  7. DoCmd.GoToControl ("RaceNumber")
  8. DoCmd.GoToRecord , "", acNewRec
  9. [Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceNumber] = Me.Data1
  10. [Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceFinishTime] = Format(Now(), "General Date")
  11. End If
  12. If Data2 > 0 Then
  13. [Forms]![frmrtmainchip]![RaceTimingSF3chip].SetFocus
  14. DoCmd.GoToControl ("RaceNumber")
  15. DoCmd.GoToRecord , "", acNewRec
  16. [Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceNumber] = Me.Data2
  17. [Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceFinishTime] = Format(Now(), "General Date")
  18. End If
  19.  
  20. If Data3 > 0 Then
  21. [Forms]![frmrtmainchip]![RaceTimingSF3chip].SetFocus
  22. DoCmd.GoToControl ("RaceNumber")
  23. DoCmd.GoToRecord , "", acNewRec
  24. [Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceNumber] = Me.Data3
  25. [Forms]![frmrtmainchip]![RaceTimingSF3chip]![RaceFinishTime] = Format(Now(), "General Date")
  26. End If
  27. 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,data2,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:
Expand|Select|Wrap|Line Numbers
  1. Function tbExtractStr(strIn, intNeedSegment, strDelimiter, Optional strNotFound As String) As String
  2.  
  3.     ' Function to chop a input string into segments and return the requested segment
  4.     ' Written and developed by Thomas M. Brittell
  5.     ' Copyright 1998; All rights reserved.
  6.     '
  7.     ' strIn          - Input string to be segmented
  8.     ' intNeedSegment - Indicates the segment to be returned
  9.     ' strDelimiter   - The delimiter used to seperate each segment
  10.     ' strNotFound    - When no segment is found return the specified string if provided
  11.     '
  12.  
  13.     Dim intCurrentPosition As Integer
  14.     Dim intFoundPosition   As Integer
  15.     Dim intLastPosition    As Integer
  16.     Dim intGetSegment      As Integer
  17.     Dim wrkNotFound        As String
  18.  
  19.     If IsEmpty(strNotFound) Or strNotFound = "" Then
  20.         wrkNotFound = ""
  21.     Else
  22.         wrkNotFound = strNotFound
  23.     End If
  24.  
  25.     intCurrentPosition = 0
  26.     intFoundPosition = 0
  27.     intLastPosition = 0
  28.     intGetSegment = intNeedSegment
  29.  
  30.     Do While intGetSegment > 0
  31.         intLastPosition = intCurrentPosition
  32.         'Find a occurance of the delimiter
  33.         intFoundPosition = InStr(intCurrentPosition + 1, strIn, Left$(strDelimiter, 1))
  34.         If intFoundPosition > 0 Then
  35.             intCurrentPosition = intFoundPosition
  36.             intGetSegment = intGetSegment - 1
  37.         Else
  38.             'End of input string so exit
  39.             intCurrentPosition = Len(strIn) + 1
  40.             Exit Do
  41.         End If
  42.     Loop
  43.     'If nothing was found and you had at least one delimiter return ""
  44.     If (intFoundPosition = 0) And ((intGetSegment <> intNeedSegment) And (intGetSegment > 1)) Then
  45.         tbExtractStr = wrkNotFound
  46.     Else
  47.         'Return the segment between the last position and the current one
  48.         tbExtractStr = Mid$(strIn, intLastPosition + 1, intCurrentPosition - intLastPosition - 1)
  49.     End If
  50.  
  51. End Function

Attached Images
File Type: jpg copy1.jpg (38.6 KB, 733 views)
Dec 3 '11 #1

✓ answered by ADezii

Just a simple adjustment should do it, see Code lines: 11, 13, 15-20
Expand|Select|Wrap|Line Numbers
  1. Private Sub strInput_AfterUpdate()
  2. Dim varRet As Variant
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5.  
  6. If IsNull(Me![strInput]) Then Exit Sub
  7.  
  8. Set MyDB = CurrentDb
  9. Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  10.  
  11. Dim strInputString As String        'Move with other Declarations
  12.  
  13. strInputString = Me![strInput]
  14.  
  15. 'See if Trailing Comma (,) is present, if so Extract it!
  16. If Right$(strInputString, 1) = "," Then
  17.   strInputString = Left$(strInputString, Len(strInputString) - 1)
  18. End If
  19.  
  20. varRet = Split(strInputString, ",")
  21. '*********************** Code Intentionally Omitted ***********************

Share this Question
Share on Google+
13 Replies


100+
P: 547
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
Dec 3 '11 #2

ADezii
Expert 5K+
P: 8,634
Is this what you are looking for?
Expand|Select|Wrap|Line Numbers
  1. Dim varRet As Variant
  2. Dim strBaseString As String
  3.  
  4. strBaseString = "0001000,0002000,0003000"
  5.  
  6. varRet = Split(strBaseString, ",")
  7.  
  8. Debug.Print varRet(0)
  9. Debug.Print varRet(1)
  10. Debug.Print varRet(2)
  11.  
Expand|Select|Wrap|Line Numbers
  1. 0001000
  2. 0002000
  3. 0003000
Dec 4 '11 #3

100+
P: 547
Hi Adezi; yes the code arrives from the rfid receiver in any number sequence "0005000,0002120,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
Expand|Select|Wrap|Line Numbers
  1. Racenumber field----Racefinishtime field in RacetimingT table
  2. 0005000 --------   08/10/2011 02:47:02 PM
  3. 0002120 --------   08/10/2011 02:47:00 PM
  4. 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.
Dec 4 '11 #4

ADezii
Expert 5K+
P: 8,634
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub strInput_AfterUpdate()
  2. Dim varRet As Variant
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5.  
  6. If IsNull(Me![strInput]) Then Exit Sub
  7.  
  8. Set MyDB = CurrentDb
  9. Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  10.  
  11. varRet = Split(Me![strInput], ",")
  12.  
  13. Select Case UBound(varRet)      'How many Race Numbers?
  14.   Case 0        '1 Race#
  15.     With rst
  16.       .AddNew
  17.         'Must ADD the Child Linking Field
  18.         ![RaceNumber] = varRet(0)
  19.         ![RaceFinishTime] = Format(Now(), "General Date")
  20.       .Update
  21.     End With
  22.   Case 1        '2 Race#'s
  23.     With rst
  24.       .AddNew       'ADD Race 1
  25.         'Must ADD the Child Linking Field
  26.         ![RaceNumber] = varRet(0)
  27.         ![RaceFinishTime] = Format(Now(), "General Date")
  28.       .Update
  29.       .AddNew       'ADD Race 2
  30.         'Must ADD the Child Linking Field
  31.         ![RaceNumber] = varRet(1)
  32.         ![RaceFinishTime] = Format(Now(), "General Date")
  33.       .Update
  34.     End With
  35.   Case 2        '3 Race#'s
  36.     With rst
  37.       .AddNew       'ADD Race 1
  38.         'Must ADD the Child Linking Field
  39.         ![RaceNumber] = varRet(0)
  40.         ![RaceFinishTime] = Format(Now(), "General Date")
  41.       .Update
  42.       .AddNew       'ADD Race 2
  43.         'Must ADD the Child Linking Field
  44.         ![RaceNumber] = varRet(1)
  45.         ![RaceFinishTime] = Format(Now(), "General Date")
  46.       .Update
  47.       .AddNew       'ADD Race 3
  48.         'Must ADD the Child Linking Field
  49.         ![RaceNumber] = varRet(2)
  50.         ![RaceFinishTime] = Format(Now(), "General Date")
  51.       .Update
  52.     End With
  53.   Case Else     'Who knows
  54.     'Do Nothing
  55. End Select
  56.  
  57. rst.Close
  58. Set rst = Nothing
  59.  
  60. 'Requery the Record Source of the Sub-Form
  61. End Sub
Dec 4 '11 #5

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. varRet = Split(Me![strInput], ",")
Dec 4 '11 #6

ADezii
Expert 5K+
P: 8,634
Just a simple adjustment should do it, see Code lines: 11, 13, 15-20
Expand|Select|Wrap|Line Numbers
  1. Private Sub strInput_AfterUpdate()
  2. Dim varRet As Variant
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5.  
  6. If IsNull(Me![strInput]) Then Exit Sub
  7.  
  8. Set MyDB = CurrentDb
  9. Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  10.  
  11. Dim strInputString As String        'Move with other Declarations
  12.  
  13. strInputString = Me![strInput]
  14.  
  15. 'See if Trailing Comma (,) is present, if so Extract it!
  16. If Right$(strInputString, 1) = "," Then
  17.   strInputString = Left$(strInputString, Len(strInputString) - 1)
  18. End If
  19.  
  20. varRet = Split(strInputString, ",")
  21. '*********************** Code Intentionally Omitted ***********************
Dec 4 '11 #7

100+
P: 547
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"
Dec 4 '11 #8

100+
P: 547
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 " RaceTimingSF3chip" subform, i would have used
Expand|Select|Wrap|Line Numbers
  1. [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 "RaceTimingSF3chip" subform after requery
Any suggestions?
Dec 5 '11 #9

ADezii
Expert 5K+
P: 8,634
@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:
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.   ![Text1].SetFocus        'Can use any Control
  3.   ![strInput].SetFocus
  4. End With
  5.  
Dec 5 '11 #10

100+
P: 547
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!
Dec 6 '11 #11

ADezii
Expert 5K+
P: 8,634
@neelsfer:
I learn a new thing every day!
So do I! (LOL).
Dec 6 '11 #12

100+
P: 547
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.
Expand|Select|Wrap|Line Numbers
  1. With rst
  2.             .AddNew
  3.          'Must ADD the Child Linking Field
  4.          ![RaceNumber] = varRet(0)
  5.          ![RaceFinishTime] = Format(Now(), "General Date")
  6.          ![Racedate] = [Forms]![frmrtmain]![RacingDate]
  7.          ![RaceName] = [Forms]![frmrtmain]![RaceName]
  8.        .update
  9.         End With
this is from my subform

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. On Error GoTo Err_Form_BeforeUpdate
  3. Dim lngLastLapNo As Long
  4.  
  5.    Me.RaceFinishTime = Format(Now(), "General Date")
  6.  
  7.     lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = " & Me![RaceNumber] & _
  8. " AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
  9.    If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
  10.   Me![LapNo] = 1
  11.   Else
  12.    Me![LapNo] = lngLastLapNo + 1
  13.      End If
  14.  
  15. Exit_Form_BeforeUpdate:
  16.   Exit Sub
  17.  
  18. Err_Form_BeforeUpdate:
  19. MsgBox Error$
  20.     Resume Exit_Form_BeforeUpdate
  21. End Sub
  22.  
Dec 7 '11 #13

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. With rst
  2.             .AddNew
  3.          'Must ADD the Child Linking Field
  4.          ![RaceNumber] = varRet(0)
  5.          ![RaceFinishTime] = Format(Now(), "General Date")
  6.          ![Racedate] = [Forms]![frmrtmainchip]![RacingDate]
  7.          ![RaceName] = [Forms]![frmrtmainchip]![RaceName]
  8.        .update
  9.         End With
this is from my subform

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. On Error GoTo Err_Form_BeforeUpdate
  3. Dim lngLastLapNo As Long
  4.  
  5.     lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = " & Me![RaceNumber] & _
  6. " AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
  7.    If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
  8.   Me![LapNo] = 1
  9.   Else
  10.    Me![LapNo] = lngLastLapNo + 1
  11.      End If
  12.  
  13. Exit_Form_BeforeUpdate:
  14.   Exit Sub
  15.  
  16. Err_Form_BeforeUpdate:
  17. MsgBox Error$
  18.     Resume Exit_Form_BeforeUpdate
  19. End Sub
  20.  
Dec 7 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.