Controling recordset

[Wiscow]

I have the Mainform (formEmplyoee) and a subform (formContractPreview)
Each employee can have many contracts and contracts of the same type
(type 1,2,3)

I have a way to browse through emplyoees based on type of contract,
and limit the subform to show only the type of contract selected in a
control. It gives me the results I want, except for one thing.

Consider my example:
I select to only view employees with contract type 1
My results are Employee A has two contracts of type 1 and Emplyoee B
has one.
Great it is working!
Except that when I click through the record selectors for the main
form I get three records; two for Emplyoee A and one for Emplyoee B
I want my main form to jump between Employees so therefore my record
selector should be two.
?


MY code as follows;

Private Sub btnApply_Click()

Dim strSQLmain As String
Dim strSQLsub As String
Dim strCRImain As String
Dim strCRIsub As String
Dim strORDmain As String
Dim strORDsub As String

strSQLmain = "SELECT tblEmployees.*, [tblEmployees]![EmployeeID]
AS LinkMaster " & _
"FROM tblEmployees INNER JOIN tblContract ON
tblEmployees.EmployeeID = tblContract.EmployeeID " & _
"WHERE "

strSQLsub = "SELECT tblContractType.*, tblContract.*,
tblProduction.*, tblContract!EmployeeID AS LinkChild " & _
"FROM tblProduction INNER JOIN (tblContractType INNER
JOIN tblContract ON tblContractType.ContractTypeID =
tblContract.ContractTypeID) ON tblProduction.ProductionID =
tblContract.ProductionID " & _
"WHERE "


strORDsub = " ORDER BY tblContract.ContractID DESC"

strCRImain = "ContractTypeID=" & Me.txtbox
strCRIsub = "tblContract.ContractTypeID=" & Me.txtbox

strSQLmain = strSQLmain & strCRImain
strSQLsub = strSQLsub & strCRIsub & strORDsub

Form_formEmployee.RecordSource = strSQLmain
Form_formContractPreview.RecordSource = strSQLsub

End Sub

 

[Marshall Barton]

I have the Mainform (formEmplyoee) and a subform (formContractPreview)
Each employee can have many contracts and contracts of the same type
(type 1,2,3)

I have a way to browse through emplyoees based on type of contract,
and limit the subform to show only the type of contract selected in a
control. It gives me the results I want, except for one thing.

Consider my example:
I select to only view employees with contract type 1
My results are Employee A has two contracts of type 1 and Emplyoee B
has one.
Great it is working!
Except that when I click through the record selectors for the main
form I get three records; two for Emplyoee A and one for Emplyoee B
I want my main form to jump between Employees so therefore my record
selector should be two.
?


MY code as follows;

Private Sub btnApply_Click()

Dim strSQLmain As String
Dim strSQLsub As String
Dim strCRImain As String
Dim strCRIsub As String
Dim strORDmain As String
Dim strORDsub As String

strSQLmain = "SELECT tblEmployees.*, [tblEmployees]![EmployeeID]
AS LinkMaster " & _
"FROM tblEmployees INNER JOIN tblContract ON
tblEmployees.EmployeeID = tblContract.EmployeeID " & _
"WHERE "

strSQLsub = "SELECT tblContractType.*, tblContract.*,
tblProduction.*, tblContract!EmployeeID AS LinkChild " & _
"FROM tblProduction INNER JOIN (tblContractType INNER
JOIN tblContract ON tblContractType.ContractTypeID =
tblContract.ContractTypeID) ON tblProduction.ProductionID =
tblContract.ProductionID " & _
"WHERE "


strORDsub = " ORDER BY tblContract.ContractID DESC"

strCRImain = "ContractTypeID=" & Me.txtbox
strCRIsub = "tblContract.ContractTypeID=" & Me.txtbox

strSQLmain = strSQLmain & strCRImain
strSQLsub = strSQLsub & strCRIsub & strORDsub

Form_formEmployee.RecordSource = strSQLmain
Form_formContractPreview.RecordSource = strSQLsub

End Sub

Since the main form's query does not return any data from
the contracts table, you can just use the DISTINCT predicate
to eliminate the duplicate records.

 

[John W. Vinson]

strSQLmain = "SELECT tblEmployees.*, [tblEmployees]![EmployeeID]
AS LinkMaster " & _
"FROM tblEmployees INNER JOIN tblContract ON
tblEmployees.EmployeeID = tblContract.EmployeeID " & _
"WHERE "

I'd suggest that you simply remove tblContract from the mainform's
recordsource. You're not using any of its fields, and in order to edit
contract information, you can just use the Subform; it's not necessary
for the main form to reference both tables. That's the reason you are
getting duplicates!

John W. Vinson [MVP]

 

[Wiscow]

I have the Mainform (formEmplyoee) and a subform (formContractPreview)
Each employee can have many contracts and contracts of the same type
(type 1,2,3)

I have a way to browse through emplyoees based on type of contract,
and limit the subform to show only the type of contract selected in a
control. It gives me the results I want, except for one thing.

Consider my example:
I select to only view employees with contract type 1
My results are Employee A has two contracts of type 1 and Emplyoee B
has one.
Great it is working!
Except that when I click through the record selectors for the main
form I get three records; two for Emplyoee A and one for Emplyoee B
I want my main form to jump between Employees so therefore my record
selector should be two.
?

MY code as follows;

Private Sub btnApply_Click()

Dim strSQLmain As String
Dim strSQLsub As String
Dim strCRImain As String
Dim strCRIsub As String
Dim strORDmain As String
Dim strORDsub As String

strSQLmain = "SELECT tblEmployees.*, [tblEmployees]![EmployeeID]
AS LinkMaster " & _
"FROM tblEmployees INNER JOIN tblContract ON
tblEmployees.EmployeeID = tblContract.EmployeeID " & _
"WHERE "

strSQLsub = "SELECT tblContractType.*, tblContract.*,
tblProduction.*, tblContract!EmployeeID AS LinkChild " & _
"FROM tblProduction INNER JOIN (tblContractType INNER
JOIN tblContract ON tblContractType.ContractTypeID =
tblContract.ContractTypeID) ON tblProduction.ProductionID =
tblContract.ProductionID " & _
"WHERE "

strORDsub = " ORDER BY tblContract.ContractID DESC"

strCRImain = "ContractTypeID=" & Me.txtbox
strCRIsub = "tblContract.ContractTypeID=" & Me.txtbox

strSQLmain = strSQLmain & strCRImain
strSQLsub = strSQLsub & strCRIsub & strORDsub

Form_formEmployee.RecordSource = strSQLmain
Form_formContractPreview.RecordSource = strSQLsub

End Sub

Since the main form's query does not return any data from
the contracts table, you can just use the DISTINCT predicate
to eliminate the duplicate records.

Thank you Marsh!