Jun 06 2022 06:45 AM
Jun 06 2022 06:45 AM
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 "_
DoCmd.Save acForm, "tmp_tblWorkLocations"
Set f = Nothing
Any help is greatly appreciated
Jun 06 2022 07:19 AM
@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.
Jun 06 2022 07:27 AM
Jun 06 2022 07:35 AM
@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.
Jun 07 2022 07:05 AM