using SQL statement in VB

[Guest]

I have a query that pulls employees names and training history. There are
multiple depts and locations for employees to be assigned to. We thought
that if I used a query to define the rst, then I could allow the user to
manipulate reports. How can I insert a sql statement into VB and use the
Dept and Region as a variable that can be changed within VB and the
recordset. Here is the sql statement displayed in Access:

SELECT Employees.LastName, Employees.FirstName, tblJobTitle.JobTitle,
tblCostCenter.CostCenter, [tblSub-region].SubRegion, Employees.IsSupervisor,
Employees.SupervisorID, tblCourse.Title, tblCourseHistoryNew.CourseDate

FROM (((((Employees LEFT JOIN tblCostCenter ON Employees.CostCenterID =
tblCostCenter.CostCenterId) LEFT JOIN tblJobTitle ON Employees.JobTitleID =
tblJobTitle.JobTitleID) INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID)
INNER JOIN [tblSub-region] ON Employees.SubRegionID =
[tblSub-region].SubRegionID) INNER JOIN tblCourseHistoryNew ON
Employees.EmployeesID = tblCourseHistoryNew.EmployeesID) INNER JOIN tblCourse
ON tblCourseHistoryNew.CourseID = tblCourse.CourseID

WHERE ((([tblSub-region].SubRegion) Like "*bangor*" Or
([tblSub-region].SubRegion) Like "*bremerton*" Or ([tblSub-region].SubRegion)
Like "*jackson*" Or ([tblSub-region].SubRegion) Like "*keyport*") AND
((tblOrg.Org)="n472") AND ((Employees.Active)=True));

The idea is to allow the user to select Org and Sub-region from option boxes
on a form. The query would then populate the recordset.

Thanks for the help in advance.

 

[Nikos Yannacopoulos]

Here's the general idea:

vSelect = "Table1.Field1, Table2.Field2,"
vSelect = vSelect & " Table2.FieldA, Table2.FieldB"

vFrom = "Table1 LEFT JOIN Table2 ON Table1.Field1 ="
vFrom = vFrom & " Table2.FieldA"

vWhere = "Table1.Field1=" & Forms!MyForm.Txt1
vWhere = vWhere & " AND Table1.Field2 Like '*"
vWhere = vWhere & " Forms!MyForm.Txt2 & "*'"

strSQL = "SELECT" & vSelect & " FROM " vFrom & " WHERE " & vWhere

Notice how the references to the form controls are left outside the
quotes, which does the trick.
Also notice the use of quotes around Forms!MyForm.Txt2, which is because
I have assumed Field2 to be text; on the contrary, no quotes around
Forms!MyForm.Txt1, because I have assumed Field1 to be numeric.

HTH,
Nikos