Set form to automatically update field based on 2 other fields

[rossi45]

Hey all, this is my first post.

I am building a form for the first time and I have a field that requires me to concatenate a text string from 2 other text fields. For example I have the following 2 fields:

SMU	ECO
M1	d1
M1	d2
M23b	ds(d2)
M2	e1

For each of the above fields, I have comboboxes with a drop down so that the user can choose the value for each one. When these fields are populated I want a 3rd field, called TERR_UNIT to automatically populate from the 2 previous fields as:

[SMU] & "-" & [ECOSITE]

So in other words, as soon as the values are populated for SMU and ECO by the user, I want TERR_UNIT to automatically populate from those fields

I've entered into the 'Default Value' property for the combo box

= [SMU] & "-" & [ECOSITE]

but nothing seems to be happening. I'm sure I'm doing something wrong, so any suggestions are welcome.



Thanks,
Mike

[pbaldy]

I probably wouldn't save the field, since it can be so easily derived from existing values. That said, the default value wouldn't work because the other values are not present at the time the new record is initiated. You could do this:

http://allenbrowne.com/casu-14.html

[rossi45]

Thanks Paul. I think this is pointing me in the right direction. I created a query and ran it. The results populated what I was looking for, but I'm still hung on trying to get the query results to update the field in my original table....any suggestions? I'm a serious noob with Access...

Thanks,
Mike

[pbaldy]

Did you see the code at the bottom of the link?

[rossi45]

Thanks again Paul.

I'm not sure what the code is supposed to do. I'm still fairly new with VB as well. I guess to clarify my vision, I want to do this:

1. Enter the values in each field (like enter an M1 into the SMU field and then a d1 in the ECO field)
2. As soon as they are populated, they get concatenated and then the reult just appears in the TERR_UNIT field.

I tried the code. I don't know what it's doing? Is what I want even possible?

[pbaldy]

Sure, it's just a little harder because you generally shouldn't do it, as noted in the link. Basically you would have similar code in the after update event of your 2 base fields to populate a textbox bound to the concatenated field.

[rossi45]

Thanks Paul. I did the following:

1. Opened my form(TE_TERRAINUNIT_R) in design view
2. CLicks on the 'ECO' field, set the After Update to 'Event Procedure'
3. In code window I set:

Private Sub ECO_AfterUpdate()
TE_TERRAINUNIT_R.TERR_UNIT = TE_TERRAINUNIT_R.SMU & "-" & TE_TERRAINUNIT_R.ECO
End Sub

Private Sub TERR_UNIT_AfterUpdate()
Call ECO_AfterUpdate
End Sub

4. Save this. I am assuming that 'me' in the example in the page refers to the form name? That's why I changed it to TE_TERRAINUNIT_R.field
5. Opened my form and entered a value in my ECO field. My expectation is, now that I typed the value in, the TERR_UNIT field in the form would update appropriately.

Nothing happened. I checked the table and nothing got update. Do I have to do something else?

Mike

[pbaldy]

Actually you can leave it as Me, which is a shortcut to the full form reference of

Forms!FormName

Is code running at all (the db would need to be in a trusted location or you would have to have enabled code)? Can you post the db here?

[rossi45]

Paul, thanks for sticking with me on this one. This is my first foray into MS Access building and programming. The code you provided me works perfectly now. The one thing I didn't realize I had to do is to enable macros, so after changing the reference to the form back to 'Me' and enabling macros, my field now updates automatically. I appreaciate your help.

Mike

[pbaldy]

Excellent, glad we got it sorted out. Welcome to the site by the way!