You are here:

Using MS Access/Pass value from main form to subform

Advertisement


Question
Hi Julie,

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.

Answer
Hi Oleg,

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()

End Sub

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
rst.MoveFirst

Do Until rst.EOF
rst.Edit
rst.LogDate = dt
rst.Update
rst.MoveNext
Loop
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.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.