parameters in subform pulled from main form

Question

I have done this a couple years ago but can't seem to remember how; I am working with access 2010 and the macro builder as opposed to VBA ( I don't do this enough for the coding).

Anyways what I have is a main form that has a subform that displays as a datasheet. This subform data source is a query that asks for three values which are applied as filters using just a where statement.

The query SQL is a select statement, followed by from and then a where statement and no parameter statement.

What I am trying to do is get the subform when it opens to pull the parameters from the main form record it is under.

I know this is possible because I have done this a couple years ago but don't have that database anymore. I have gone through all the books I have and still can't seem to find a combination that works.

Any suggestions or help with what I am missing?

****Update****** The set value isn't working... What I have is a form SQ_Ticket that has fields Site Number, Date Submitted, and End Date. I then I have a sub form that is based on a parameter query, it has a different number of fields then then the ticket query so a Union is too much of a hassle. But the fields in the sub form that would relate to the main form are Site Number and create date.

What I am trying to do is I have the main form (SQ_Ticket) with a sub form (SQ_Alarm_Parameter subform) and the form is a datasheet. When I click to expand the subdatasheet I want the Parameter query to pull the Site Number from the main form and use it to match to the site number in the subform and the pull the Date Submitted and End Date and use it as the start and end in a between statement for the create date in the sub form.

Basically I want to use find all records in the sub form (SQ_ALarm_Parameter subform) that have the same Site Number as the record in SQ_Ticket and that are Created between the Date Submitted and End Date of the Record in SQ_Ticket.

I seem to recall needing to pull the specific data from the main form and then using it as a temp value in the subform, but again can't remember how do to do that.

I know I am missing something obvious since I know I have done this before, but I didn't think to keep a copy of that work and it was quite a while ago. So I really appreciate the help

Answer 1

Firstly, I think taking a few minutes to do the VBA would be valuable and easier to work with. However with the macro method you can just use the Set Value submacro. This won't show up by default so you will need to select "Show All Actions" on your ribbon at the top of Access.

The Macro:

This will allow you to set the RecordSource for one form based off of value of a textbox on another form. You just need to adjust now work with your fields and parameters.