Filter a query on one form with data from another form

[annmv888]

I need help with passing data from one form's query to another.

I have a form with mainform and a subform. The mainform has the lngTrainerID field. The subform has the dtmDate field. I use that field to create two fields in my query, FirstDayofWeek: ([dtmDate]-Weekday([dtmDate]))+2 and LastDayofWeek: ([dtmDate]-Weekday([dtmDate]))+6. These appear on my subform along with the dtmDate field.

I want to link this form to another form by using the lngTrainerID, FirstDayofWeek and LastDayofWeek fields. The lngTrainerID field iinks fine using DoCmd.OpenForm "frmWeeklyHours", acNormal, , "lngTrainerID = " & Me.lngTrainerID but I want to also link the FirstDayofWeek and LastDayofWeek fields the same way but it won’t work. These two fields are also in my second forms query. I have tried everything in know but can’t get it to work. Can anyone help me? Thanks in advance.

[pbaldy]

The second form could be based on a query that included a date criteria, or you should be able to add to your wherecondition:

"lngTrainerID = " & Me.lngTrainerID & " AND DateField Between #" & Me.FirstDate & "# And #" & Me.SecondDate & "#"

[annmv888]

Thanks for the help. I pasted your code but now I get an error, "Member or data member not found" for the Me.lngTrainerID. This works without the date part.

I also tried taking the Me.lngTrainerID part out to see if the dates would work on their own and I get the same message on the Me.FirstDate.

[pbaldy]

Did you change all the names to match your field and textbox names?

[annmv888]

Yes I did and if I take out the line from & " AND... it works again for the lngTrainerID.

Also, as soon as I type the me. the list appears and I can find the field lngTrainerID. When I did that for my me.FirstDayofWeek field I can't find that field in the list.

[pbaldy]

If those are on the subform (more accurately, not in the object containing the code), you'd need the full reference to them:

Forms Refer to Form and Subform properties and controls

[annmv888]

Thanks so much that worked great!

[pbaldy]

Happy to help and welcome to the site by the way!