Using MS Access/Pass value from main form to subform
On the main form (call it MainForm1) I have a date field Text80 (unbound control) with Format as Date. I need to pass it to each record in the subform. Subform has field LogDate. This is where the date entered in Text80 should be passed to automatically. Please do not ask about the logic. There is one, it is just easier to only explain the situation rather then going into details why it is like this. Thank you.
Many thanks for the question.
To do what you ask you will need to write a bit of code. I am not sure of your level of expertise in VBA so I will step you through it.
You also have not mentioned which version of Access you are using. In my answer I will be using Access 2010. I am reasonable confident it will work with any version, but some of the steps maybe slightly different for other versions.
The code you will be adding will be to the 'After Update' event of Text80. To create the code, open the properties window. Clock on Text80, so the properties for that object appear. Click on the Event Tab. Place your cursor next to After Update. Click on the drop down arrow and select [Event Procedure], then click on the button with the three dots (...).
This will open the coding window with the following two lines of code:
Private Sub Text80_AfterUpdate()
In between these two lines of code add the following code:
Dim dt As Date
Dim rst As Object
dt = Me!Text80
Set rst = Forms![MainForm1]![SubForm].Form.Recordset
Do Until rst.EOF
rst.LogDate = dt
Set rst = Nothing
What this code does:
Stores the date from the main form in the variable dt
It then sets rst to the recordset (table or query) used in the subform, moving to the first record. It tell the recordset, that it wishes to edit the record, and then changes the value in LogDate to equal the value of the variable dt. It then saves that record (update) and moves to the next record. Until it reaches the end of the records (EOF)
Hope that all makes sense. Good luck.