Access.2007.VBA.Bibl..

Dim strProjectCode As String

Dim strSearch As String

Dim strSQL As String

Dim frm As Access.Form

Dim strForm As String

Create a filtered query and run it to create the form’s record source:

strForm = “frmSelectedTimesheet”

lngID = Nz(Me![EmployeeID])

strClientCode = Nz(Me![ClientCode])

strProjectCode = Nz(Me![ProjectCode])

strSQL = “SELECT tblWeeklyTimesheet.*, “ _

& “qryEmployees.EmployeeName, “ _

& “qryEmployees.ManagerName, “ _

& “qryClientsAndProjects.ClientProject “ _

& “INTO tmakSelectedTimesheetTemp “ _

& “FROM qryClientsAndProjects “ _

& “INNER JOIN (tblWeeklyTimesheet “ _

& “INNER JOIN qryEmployees “ _

& “ON tblWeeklyTimesheet.EmployeeID = “ _

& “qryEmployees.EmployeeID) “ _

& “ON (qryClientsAndProjects.ProjectCode = “ _

& “tblWeeklyTimesheet.ProjectCode) “ _

& “AND (qryClientsAndProjects.ClientCode = “ _

& “tblWeeklyTimesheet.ClientCode) “ _

& “WHERE tblWeeklyTimesheet.EmployeeID=” _

& lngID & “ AND tblWeeklyTimesheet.ClientCode=” _

& Chr$(39) & strClientCode & Chr$(39) _

& “ AND tblWeeklyTimesheet.ProjectCode=” _

& Chr$(39) & strProjectCode & Chr$(39) _

& “ AND tblWeeklyTimesheet.WeekEnding = “ _

& “CurrentWeekEnding();”

Debug.Print “SQL string: “ & strSQL

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

Open form for editing selected timesheet:

DoCmd.OpenForm FormName:=strForm

Set frm = Forms![frmSelectedTimesheet]

frm.Caption = “Weekly Timesheet for “ _

& Me![EmployeeName]

DoCmd.Close acForm, Parent.Name

ErrorHandlerExit:

Exit Sub

Part II Writing VBA Code to Exchange Data between Office Components