Linking a subform to a form using more than one field

[instructorgirl]

I have a subform on a form. On the subform I have a details button. When I click on the details button I want to show the records for the current week. My linking fields are [lngEmpID] and [Monday]. My field [Monday] is the field I want to use pull the current record.

My code is as follows:

stLinkCriteria = "[lngEmpID]=" & Me![lngEmpID] & " and [Monday]=" & Chr(34) & Me![Monday] & Chr(34)

When I click on the details button, it pulls up only the records for the current employee but also ALL of the records for the time entry records as well.

Any help would be greatly appreciated. Thanks!

 

[PHV]

What is the data type of [Monday] in your table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya instructorgirl . . .

I don'know the data type of [blue]Monday[/blue] but if its [blue]date data type[/blue] try the following in your button instead! . . .

[blue]   Dim StartDate As Date, EndDate As Date, curDate As Date
   
   'StartDate = Date
   curDate = Date
   
   If Weekday(curDate) = vbSunday Then
      StartDate = curDate - 6
   Else
      StartDate = curDate - (Weekday(curDate) - 2)
   End If
   
   EndDate = StartDate + 6
   Me.Filter = "[lngEmpID]=" & Me![lngEmpID] & " AND " & _
               "[MonDay] >= #" & Format(StartDate, "mm-dd-yyyy") & "# AND " & _
               "[Monday] <= #" & Format(EndDate, "mm-dd-yyyy") & "#"
   Me.FilterOn = True[/blue]

This may/may not have to be trimmed! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[instructorgirl]

My data type is date. I tried the code that AceMan1 suggested, but it still brought up everything. Any other ideas? Thanks!

 

[PHV]

My data type is date
stLinkCriteria = "[lngEmpID]=" & Me![lngEmpID] & " and [Monday]=#" & Format(Me![Monday], "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886