Add text of two unbound text boxes into one field.

[Jugnu]

i want to add text from two unbound text box into one field, when i write an expression for this, the result appear in the form but the field table remain empty.
Please suggest a solution.

[NeoPa]

If it's an expression then, by definition, it's not bound to any table.

If you want a value in the table (.RecordSource) to be updated then you need to bind a control to the relevant field and update the value whenever either of the two unbound controls is updated. Use the .AfterUpdate() events for this.

[Jugnu]

Thanks NeoPa,
But i am completely new to VBA and don't know what to write in after update event.
Will you please guide how to do this.
I have a field in database table named as
Home address and i have two unbound boxes in my form1 named as city and pin number.
I want enter data of city and pin number boxes in to home address field. Please guide me with a VBA code how to do this.
Thanks for your kind reply. ��

[NeoPa]

Well, I should start off by saying that this is really a site for developers to help each other. Simply asking for the work to be done for you is generally frowned upon.

However, this once I'll show you how to manage it. You will be expected to be able to cope with the basics in future though. Possibly visit Microsoft Access Tutorials (Strive4Peace) and learn the basics first if you need more help.

Here's another point. In order to be able to help I need to know what you're working with as far as control names go and various other basic information required for your question to make proper sense. For now I'll assign my own names and hope you can recognise what's what. If you ever write another question make sure you do a better job of explaining the situation fully. As you haven't included any such information I will also have to assume that you're working in India and the PIN you refer to is the six-digit PIN they use there.

Anyway, for your situation you first need to ensure that the two unbound controls properly reflect the correct data each time a record is made the current one. This requires them to be set in the Form's Current event procedure ([txtHomeAddress] may be invisible but must exist. If visible then it should be locked.) :

Private Sub Form_Current()
    With Me
        .txtCity = Null
        .txtPIN = Null
        If .txtHomeAddress Like "* ######" Then
            .txtCity = Trim(Left(.txtHomeAddress, Len(.txtHomeAddress) - 6)
            .txtPIN = Right(.txtHomeAddress, 6)
        ElseIf .txtHomeAddress Like "######" Then
            .txtPIN = .txtHomeAddress
        ElseIf .txtHomeAddress > "" Then
            .txtCity = Trim(.txtHomeAddress)
        End If
    End With
End Sub

Next you need to make sure that if either is updated then the updated value is applied back into Me.txtHomeAddre ss appropriately.

Private Sub txtCity_AfterUpdate()
    With Me
        .txtHomeAddress = Trim((.txtCity + " ") & .txtPIN)
    End With
End Sub

Private Sub txtPIN_AfterUpdate()
    With Me
        .txtHomeAddress = Trim((.txtCity + " ") & .txtPIN)
    End With
End Sub

[NeoPa]

Another very important thing you should understand if you're ever working with databases is Database Normalisation and Table Structures. Storing an address as a single field and then trying to separate it out when working with it is really not a sensible approach. If you need the data to be separate then it shouldn't be stored together. A proper design helps you avoid all the unnecessary complications required when you do it the wrong way.

[Jugnu]

Thanks NeoPa.
It completely works for me.
You really such a genius ��

[NeoPa]

I'm happy that I helped, but don't forget the other point about not using such a design in the first place.

Your mother would have warned you all about that when you were very young - if she'd known anything about databases ;-)