Missing the obvious? use expression builder to populate field in a new record

[kelunaboy]

Microsoft Access 2013, result of expression builder bound to Control Source of a textbox named FullSiteData.

The expression is here: ="50" & "-" & [IslandCode] & "-" & [QuadData] & "-" & [SiteData]

where IslandCode is a combobox and QuadData and SiteData are validated textboxes, and the result is FullSiteData.

Question - how do I use this expression value to populate a field in an Access table?

Seems like it should be easy, but I struggle with this. Of course I can make the Control Source the table field and update that way, but the expression builder is my validation.

I've built web forms using PHP and MySQL and understand the process of a query adding data to a table, but don't understand how to do this.

Thank you, Alan

 

[dhookom]

Do you have a good justification for storing a value that can be calculated from other fields?

You could use code to update a text box bound to a single field in your table.

Duane
Hook'D on Access
MS Access MVP

 

[kelunaboy]

Aloha dhookom,

Thanks for the reply! The fields used in the expression aren't stored in the table. They are used to create the final string, which is stored in the table.

First the user picks the correct Island from the IslandCode combobox, then enters QuadData, and then enters SiteData, the expression appends a "50" and inserts hyphens to assemble the final string that is stored in a table.

I was looking for a way to assign the assembled string to a variable (FullSiteData) and write it out to the table.

Thought this would be easy, but it's looking more and more like something Access can't do.

Guess I could add fields for each component in the table, but it's used in reports as a complete string.

Alan

 

[dhookom]

If you have multiple pieces of data, they should be in multiple fields. For reporting and/or display purposes, you can always combine them.

="50-" & [IslandCode] & "-" & [QuadData] & "-" & [SiteData]

Duane
Hook'D on Access
MS Access MVP

 

[kelunaboy]

Thanks again.

I should have mentioned that I'm updating the forms for an existing table with 20k records, it already uses a single string to enter and store this same data, and many of those records need to be edited.

Does anyone have suggestions or hints on how to save the result of an expression in a table field?

Thank you,
Alan

 

[dhookom]

You would need code in the after update of the controls on the form like:

   Me.txtCombined="50-" & [IslandCode] & "-" & [QuadData] & "-" & [SiteData]

You would want to trigger this code from each of the controls for IslandCode, QuadData, and SiteData. I would create a small sub with the above line and then call that sub from each of the After Update events of the three controls.

Duane
Hook'D on Access
MS Access MVP