Breaking down the text in a multi-line text box... 2

[rickyoswaldiow]

Good evening all. Today I am trying to store the text typed into a text box into a database. Very simple you may say, but here's the catch; The text box is multi-lined and there is a seperate record for each line in the database. For example, the user will enter his address as follows
House Number
Street Name
City
State

But in the database there are seperate fields for each line; Address1, Address2...

Would it be easier to simply go back and change the form design to accomodate 5 seperate text boxes? There are a *lot* of references to the way I have it set up already, currently it reads from the database using this method:

with rsAddress
   Me.txtAddress = !Address1
   Me.txtAddress = me.txtAddress & vbCrLf & !Address2
   Me.txtAddress = me.txtAddress & vbCrLf & !Address3

end with

I'm guessing there is a way to read through a string and check for the vbCrLf or similar? Any help would be greatly appreciated!

 

[PatrickIRL]

Use the split() function to do what you need.

To write to the db:

dim sInfo()    as string

sinfo=split(text1.text,vbcrlf)

now you have an array of name and address elements

sInfo(0) is house number
sInfo(1) is street name

etc ...

Patrick

 

[rickyoswaldiow]

That's cool, thanks!

 

[rickyoswaldiow]

This is fairly limiting because there has to be a fixed number in the array. If I try to read in 4 entries and the user only enters 3 lines, it will crash. I think I need to construct a loop to detect how many lines the user has entered, is there an easy way to detect this?

 

[Nimroduk]

To determine how many entries you can test the ubound of the array created.

You should also look at using the Len and instr functions to determine whether they've actually entered anything at all!

 

[rickyoswaldiow]

You should also look at using the Len and instr functions to determine whether they've actually entered anything at all!

For this I have just used an If statement that checks <> ""!

 

[rickyoswaldiow]

Thankyou Nimrod, the UBound works just fine:

                Dim textBoxLines As Integer
                textBoxLines = 0

                If Me.txtBankAddr <> "" Then
                    Do While textBoxLines < UBound(sInfo)
                        !BankAddr1 = Left("" & sInfo(textBoxLines), 40)
                        textBoxLines = textBoxLines + 1
                    Loop
                End If

However, as you can see, this is writing out to the same record every time. Can I put a variable in the record i.e. !BankAddr(x) or must I construct some kind of case statement?

 

[rickyoswaldiow]

Okay, my statement is coming along nicely:

    Dim sInfo() As String
    sInfo = Split(Me.txtBankAddr, vbCrLf)
    
    Dim textBoxLines As Integer
    Dim intA As Integer
    textBoxLines = 0
    intA = 0


            If UBound(sInfo) > 5 Then
                MsgBox "You can have a maximum of 5 lines in the address!", vbOKOnly + vbInformation, "Too many address lines!"
                Exit Sub
            End If
            If Me.txtBankAddr <> "" Then
                Do While textBoxLines <= UBound(sInfo) And textBoxLines < 6
                    Do Until .EOF
                        If .Fields(intA).Name = "BankAddr" & (textBoxLines + 1) Then
                            .Fields(intA).Value = Left("" & sInfo(textBoxLines), 40)
                            textBoxLines = textBoxLines + 1
                        End If
                        intA = intA + 1
                    Loop
                Loop
            End If

I am getting this error: "Subscript out of range".
Stepping through the program I can see that after sInfo(textBoxLines) writes the last line, it carrys on executing the loop! This has bent my mind already and I am feeling pretty lost as to where I need to put a check to stop this happening...

 

[rickyoswaldiow]

Fixed!
I just had to shuffle it around a bit

            If Me.txtBankAddr <> "" Then
                'Do While textBoxLines <= UBound(sInfo)
                    Do Until .EOF
                        If .Fields(intA).Name = "BankAddr" & (textBoxLines + 1) And textBoxLines <= UBound(sInfo) Then
                            .Fields(intA).Value = Left("" & sInfo(textBoxLines), 40)
                            textBoxLines = textBoxLines + 1
                        End If
                        intA = intA + 1
                    Loop
                'Loop
            End If

 

[rickyoswaldiow]

*EDIT*

            If Me.txtBankAddr <> "" Then
                Do While textBoxLines <= UBound(sInfo)
                        If .Fields(intA).Name = "BankAddr" & (textBoxLines + 1) And textBoxLines <= UBound(sInfo) Then
                            .Fields(intA).Value = Left("" & sInfo(textBoxLines), 40)
                            textBoxLines = textBoxLines + 1
                        End If
                        intA = intA + 1
                Loop
            End If

 

[Nimroduk]

You could possibly do that a bit easier using a for loop:

Dim liCount As Integer
Dim lsInfo() As String

If txtBankAddr.Text <> vbNullString Then
    lsInfo = Split(textBoxLines.Text, vbCrLf)
    
    If UBound(lsInfo) > -1 Then
        'Loop through the address fields and update
        For liCount = 0 To UBound(lsInfo)
            .Fields("BankAddr" & CStr(liCount + 1)).Value = Left$(lsInfo(liCount), 40)
        Next liCount
    End If
End If

 

[Nimroduk]

Sorry, I also meant to say:

If what you have is working, then thats really all you need to worry about

 

[rickyoswaldiow]

I'm not going to use that as what I have does already work but thankyou for showing me the syntax of a for loop in VB! Almost every time I go to write a loop I start punching in some C++!

 

[strongm]

And just for fun a For Each solution (which allows us to avoid playing around with Ubounds or testing the length of the string ...

[blue]Dim txtLine As Variant
Dim liCount As Long

For Each txtLine In Split(txtBankAddr.Text, vbCrLf)
        .Fields("BankAddr" & CStr(liCount + 1)).Value = Left$(txtLine, 40)
        liCount = liCount + 1
Next[/blue]

 

[Nimroduk]

Nice. Got say I didn't know you could do that

 

[strongm]

Which is why it was worth illustrating

 

[BobRodes]

<If what you have is working, then thats really all you need to worry about

I dunno. For me it's just the first thing, then I try to improve it.

 

[rickyoswaldiow]

Yeh it works, I would change it but I've got quite a lot of other work to be doing :|

 

[rickyoswaldiow]

The loop seems to have broken... I have also tried using your new method strongm, if I enter less than 4 lines of address then all of the fields I enter afterwards do not save..

 

[strongm]

Since I can't see exactly how you've inserted my example into your code I can't comment. Suffice it to say that as it stands it should handle as many lines as you like (certainly more than the VB textbox is capable of holding)