simple task... for most, that is

[rss0213]

I have a field for address lines in my table (AddressLines). One or more (up to 3) address lines will be stored in this text field. Each address is separated by a colon ).

Examples:

1. PO Box 123
2. Attn FredO Box 123
3. Attn Fred:123 Any St:Suite 456

So, I want to display each address line separately on the form associated with this table, but I don't want to change the original AddressLines field. So I've defined 3 unbound text boxes (txtAddr1, txtAddr2, txtAddr3). And I've got some code to parse the AddressLines field (below), but I don't know how to make variable varLines grab the AddressLines value from the table for the current record. I've tried using Me!AddressLines.Text because the field is on this form, but it's not visible, and Access requires it to be visible so that I can set focus to that field and get its Text value.

So, I'm guessing I need to do a query to set the varLines variable to this value in the table (instead of trying to grab the value from the form)?

Code in the txtAddr1_GotFocus() procedure:
Dim varLines As String
Dim varOccurrences As Long

varLines = SupplierLocation!PostalAddressLines
MsgBox varLines

varOccurrences = StringCountOccurrences(varLines, ":") 'Function that counts the occurrences of a colon in the AddressLines field

Dim x As Variant
Dim i As Long
x = Split(varLines, ":")
If varOccurrences = 0 Then
Me.txtAddr1.Text = varLines
Me.txtAddr2.SetFocus
Me.txtAddr2.Text = ""
Me.txtAddr3.SetFocus
Me.txtAddr3.Text = ""
End If
If varOccurrences = 1 Then
Me.txtAddr1.Text = x(0)
Me.txtAddr2.SetFocus
Me.txtAddr2.Text = x(1)
Me.txtAddr3.SetFocus
Me.txtAddr3.Text = ""
End If
If varOccurrences = 2 Then
Me.txtAddr1.Text = x(0)
Me.txtAddr2.SetFocus
Me.txtAddr2.Text = x(1)
Me.txtAddr3.SetFocus
Me.txtAddr3.Text = x(2)
End If

Me.Refresh
Me.txtAddr1.SetFocus


Thanks!
Scott

 

[traingamer]

Try Me!AddressLines.Value rather than Me!AddressLines.Text

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[rss0213]

That did the trick. Don't know why I didn't think of that because I know I've used that in the past. Anyway, thanks for the quick response!

Thanks!
Scott