423,688 Members | 2,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Add text of two unbound text boxes into one field.

P: 16
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.
May 12 '18 #1

✓ answered by 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.) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     With Me
  3.         .txtCity = Null
  4.         .txtPIN = Null
  5.         If .txtHomeAddress Like "* ######" Then
  6.             .txtCity = Trim(Left(.txtHomeAddress, Len(.txtHomeAddress) - 6)
  7.             .txtPIN = Right(.txtHomeAddress, 6)
  8.         ElseIf .txtHomeAddress Like "######" Then
  9.             .txtPIN = .txtHomeAddress
  10.         ElseIf .txtHomeAddress > "" Then
  11.             .txtCity = Trim(.txtHomeAddress)
  12.         End If
  13.     End With
  14. End Sub
Next you need to make sure that if either is updated then the updated value is applied back into Me.txtHomeAddress appropriately.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCity_AfterUpdate()
  2.     With Me
  3.         .txtHomeAddress = Trim((.txtCity + " ") & .txtPIN)
  4.     End With
  5. End Sub
  6.  
  7. Private Sub txtPIN_AfterUpdate()
  8.     With Me
  9.         .txtHomeAddress = Trim((.txtCity + " ") & .txtPIN)
  10.     End With
  11. End Sub

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,084
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.
May 12 '18 #2

P: 16
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. ��
May 18 '18 #3

NeoPa
Expert Mod 15k+
P: 31,084
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.) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     With Me
  3.         .txtCity = Null
  4.         .txtPIN = Null
  5.         If .txtHomeAddress Like "* ######" Then
  6.             .txtCity = Trim(Left(.txtHomeAddress, Len(.txtHomeAddress) - 6)
  7.             .txtPIN = Right(.txtHomeAddress, 6)
  8.         ElseIf .txtHomeAddress Like "######" Then
  9.             .txtPIN = .txtHomeAddress
  10.         ElseIf .txtHomeAddress > "" Then
  11.             .txtCity = Trim(.txtHomeAddress)
  12.         End If
  13.     End With
  14. End Sub
Next you need to make sure that if either is updated then the updated value is applied back into Me.txtHomeAddress appropriately.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCity_AfterUpdate()
  2.     With Me
  3.         .txtHomeAddress = Trim((.txtCity + " ") & .txtPIN)
  4.     End With
  5. End Sub
  6.  
  7. Private Sub txtPIN_AfterUpdate()
  8.     With Me
  9.         .txtHomeAddress = Trim((.txtCity + " ") & .txtPIN)
  10.     End With
  11. End Sub
May 18 '18 #4

NeoPa
Expert Mod 15k+
P: 31,084
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.
May 18 '18 #5

P: 16
Thanks NeoPa.
It completely works for me.
You really such a genius ��
May 24 '18 #6

NeoPa
Expert Mod 15k+
P: 31,084
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 ;-)
May 24 '18 #7

Post your reply

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