Update a text box ControlSource with VBA

Copper Contributor

I have a textbox named Data_Source and the controlSource is set to a Path that I use when getting data from an external database.   "=D:\Datasource.........."

I would like to be able to update and save the control source with VBA.  I use code as follows, that works fine, but when I go to design view, the ControlSource has not changed.  How can I keep the change?

Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant

Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show Then
    For Each varItem In f.SelectedItems
        strFile = Dir(varItem)
        strFolder = Left(varItem, Len(varItem) - Len(strFile))
        'MsgBox "Folder: " & Chr(34) & strFolder & strFile & Chr(34)

        Forms!tmp_tblWorkLocations.xDataSource.ControlSource = Chr(34) & "=" & strFolder & strFile "_

        &  Chr(34)
        DoCmd.Save acForm, "tmp_tblWorkLocations"
End If
Set f = Nothing


Any help is greatly appreciated

5 Replies

@chipmunkwhiz Where and when does that code run. You left off the signature indicating that.


If I understand your requirement correctly, you want to change the control's control source permanently from the reference to an external database to the value of a string generated by your code. Is that correct?

Or are there two different controls involved? I see one slight difference. In one place you refer to "...a textbox named Data_Source..." and in the code you refer to a textbox named Forms!tmp_tblWorkLocations.xDataSource.ControlSource 


I.e. there is a x prefix in the code, suggesting it is a different control. Is that correct?


In any event, in order to persist calculated values, you have to store them in a table. You can use your code to update the field in the table where this value is stored and bind it to the appropriate control.

I apologize, it is just the textbox named xDataSource.
and yes, my goal is to basically store the location of the data source in the form, but if the data source gets moved someone with no experience needs to be able to update it. So my plan was to have the location as the data source for a text box, then just update it as needed.

@chipmunkwhiz No apologies needed. It's just that sometimes we have been known to answer the wrong question based on faulty assumptions.


In this case, you do need to create a table to store the location. Bind the field with that value to the control on the form. You can update it as needed, based on the code shown. Or, you can use a DLookup to get it when needed from the table.


Keep in mind that forms are the interface to the data, not the actual data. Persisting data requires tables. 

At the risk of going too far into the details, all relational database applications include three components:

Data Tier: Tables holding the data.

Interface Tier: The interface objects used to interact with your data; forms, reports

Logic Tier: Code that manipulates both interface and data.


Queries are hard to classify because in some ways they are part of the interface in that they are used to retrieve data for forms and reports from tables. They are also part of the logic in that they are used to update, add and delete data from tables.


So, here you want to persist data values. That calls for a table, but you can DISPLAY them in the interface, i.e. in a control on a form.


perfect, thank you for the info.
I'll store the location in its own database then.
You don't need a separate database. Create a table in this same database for this sort of information.