filter data in textbox based on criteria

[neelsfer]

I use a cycling timing program with a RFID receiver and tags.It works 100% with 3rd party software to copy the Racenumber of athletes into a specific txtbox call "Filterbox" .

My problem - when more than one athlete finishes together, then every person's Racenumber is added to this same texbox called "Filterbox" at the sametime.

The Rfid sends 5 digits per person to this txtbox
ie Athlete No 1 = 00001; Athlete No 2 = 00002; Athlete No 3 = 00003.

When 1+2+3 finished together, the filterbox txt box looks like this - 000010000200003 instead of receiving them separately 00001 and then after that 00002 and the lastly 00003.

My plan: when the data arrives in the txtbox called "Filterbox" on my main form called "RacesetupF ", i want the data split to secondary txtboxes called Databox1, Databox2, Databox3 etc etc in groups of 5 characters.

In otherwords, if Filterbox received data = 000010000200003 then it must filter the data and the characters 1-5 (00001) go to Databox1, characters 6-10 (00002)go to Databox2 and characters 11-15 (00003)go to Databox3 etc etc.

From Databoxes 1-3, i will then forward the data to my subforms/tables etc.
Is this possible to split it and forward the data?Please assist

[NeoPa]

That design is limited Neels. It allows for only three values to be handled.

What if, instead of that, you handle the AfterUpdate event of [Filterbox] and ensure commas (,) are entered between every fifth character and the next one in the string :

Private Sub Filterbox_AfterUpdate()
    Dim intPos As Integer
    Dim strTmp As String

    With Me
        If Len(.Filterbox) < 6 Then Exit Sub
        If Mid(.FilterBox, 6, 1) = "," Then Exit Sub
        For intPos = 1 To Len(.Filterbox) Step 5
            strTmp = strTmp & "," & Mid(.FilterBox, intPos, 5)
        Next intPos
        .FilterBox = Mid(strTmp, 2)
    End With
End Sub

That way, your code processing the data can handle multiple values easily using Split([Filterbox], ",").

[neelsfer]

Thx NeoPa it splits the data with a comma correctly. I am still a bit lost.
I use the following code to copy this 5 digit Racenumber to a field in a subform and then i also add a "finish time" as follow:

Private Sub filterbox_AfterUpdate()
[Forms]![RaceSetupF]![RaceTimingSFBunch].SetFocus
DoCmd.GoToControl ("RaceNumber")
DoCmd.GoToRecord , "", acNewRec
[Forms]![RaceSetupF]![RaceTimingSFBunch]![RaceNumber] = Me.filterbox
[Forms]![RaceSetupF]![RaceTimingSFBunch]![RaceFinishTime] = Format(Now(), "General Date")

Somehow i need to copy these groups of 5 characters individually to the subform field.
My thinking is still to first copy each group of 5 characters individually to each of the "DataBox1-3..." txtboxes

[Forms]![RaceSetupF]![RaceTimingSFBunch]![RaceNumber] = Me.Databox1or 2 or 3

and then pass it on to the fields in the subform. How would i split each group of 5 characters to enable me to populate it to the Databox txtboxes?

[NeoPa]

You've got yourself into a bit of a pickle there Neels, as your design doesn't really allow for multiple records being created at once. The design of a form where you had any number of TextBox controls to handle multiple records doesn't help with that at all and also leaves you with the problem of how many TextBoxes to include.

If you're intending to create possibly multiple records a better solution (or approach) might be to knock up some quick SQL to insert the new record(s) then call a .Requery of the subform followed by a GoTo for maybe the first record (You obviously can't go to more than one of these new records at the same time), or alternatively (may or may not suit your needs) filter to match all three.

The code to handle the basics of that would be something like :

Private Sub Filterbox_AfterUpdate()
    Dim intPos As Integer
    Dim strTemplate As String, strSQL As String
    Dim db As DAO.Database

    Set db = CurrentDb
    strTemplate = "INSERT INTO [YourTable]([RaceNumber],[RaceFinishTime]) " & _
                  "Values('%N', #%T#)"
    strTemplate = Replace(strTemplate, "%T", Format(Now(), "m/d/yyyy H:m:s")
    With Me
        For intPos = 1 To Len(.Filterbox) Step 5
            strSQL = Replace(strTemplate, "%N", Mid(.FilterBox, intPos, 5))
            Call db.Execute(strSQL)
        Next intPos
        Call .SubformControl.Form.Requery
    End With
End Sub

[neelsfer]

Thx Neopa for the trouble. I will give it a try tomorrow.

[NeoPa]

No worries Neels.

It's only a starting point. I don't know all the details you do so there will probably still be some work for you to do, but it should be a good basis.

NB. I hope you noticed the difference in format string. "General Date" will work over in the USA, but not in most other places properly. SQL date literals are not locality dependent.

[neelsfer]

Neopa;I am still sweating with this.
This part stays red

# strTemplate = "INSERT INTO [racetimingT]([RaceNumber],[RaceFinishTime]) " & _
#                   "Values('%N', #%T#)"

Have a look at this small application i got on the web. It takes the comma delimited string and strip it out individually into a txtbox. I am also looking at a modified version of it as perhaps another option, as i will not need more than 5-10 hidden txtboxes to receive data. my date format in South Africa is dd/mm/yyyy.
thx for your trouble so far

[NeoPa]

Neels:
NeoPa;I am still sweating with this.
This part stays red :

# strTemplate = "INSERT INTO [racetimingT]([RaceNumber],[RaceFinishTime]) " & _
#                   "Values('%N', #%T#)"

OK. I'm somewhat confused though :

1. Where do the hashes come from. They are not part of the code.
2. Red, I assume (as you tell me no more) is simply an indication that you have selected the line as a breakpoint in the code. No idea why that might be a problem.

Neels:
Have a look at this small application I got on the web.

Why? I'm not looking for anything. I'm here to help you.

You have every right to ignore my advice that such an approach is non-viable, but I'm not sure why you think I may change my advice at this stage.

Neels:
My date format in South Africa is dd/mm/yyyy

Same as over here in Blighty. Irrelevant as far as SQL is concerned, but you will create unreliable SQL unless you format the date values in SQL Date format (m/d/yyyy or variations thereof).