I was wondering if there was a way that i could call a variable that was saved in a Sub Routine previously.... I have two combo boxes cboMemberID, and cboProjectID. Inside cboMemberID i have the member ID's and whenever they select those, the projects in cboProjectID are limited to those that the selected Member ID has worked on. And this works well, but the computer thinks too slowly, on what the projects are. I want to speed this up,
First of all, are there any ideas?
Secondly this is my VBA Code for it so far:
Private Sub cboMemberID_gotfocus()
cboMemberID.RowSource = "SELECT DISTINCT dbo_Member.Member_ID FROM dbo_Member ORDER BY dbo_member.member_id "
End Sub
Private Sub cboMemberID_AfterUpdate()
Me.cboProjectID.Requery
End Sub
Private Sub cboProjectID_GotFocus()
cboProjectID.RowSource = "SELECT Distinct dbo_pm_project.Project_id FROM dbo_TE_Period INNER JOIN dbo_Time_Sheet ON dbo_TE_Period.TE_Period_RecID = dbo_Time_Sheet.TE_Period_RecID, dbo_MEmber INNER JOIN ((dbo_Company INNER JOIN dbo_PM_Project ON dbo_Company.Company_RecID = dbo_PM_Project.Company_RecID) INNER JOIN dbo_Time_Entry ON (dbo_Company.Company_RecID = dbo_Time_Entry.Company_RecID)) ON dbo_Member.Member_RecID = dbo_Time_Entry.Member_RecID, dbo_member_company inner join dbo_Member as [mem] ON dbo_member_company.member_recid = [mem].member_recid, dbo_member_company as [memco] inner join dbo_company as [com] ON [memco].company_recid = [com].company_recid " & _
"WHERE (([dbo_Company].[company_recid] = [dbo_time_entry].[company_recid]) And dbo_Member_Company.member_recid = dbo_member.member_recid And ((dbo_Company.company_recid) = (dbo_Member_Company.company_recid)) And ([dbo_pm_project].[company_recid] = [dbo_Company].[company_recid]) And ([dbo_member].[member_recid] = [dbo_time_sheet].[member_recid]) And ([dbo_TE_Period].[te_period_recid] = [dbo_time_sheet].[te_period_recid] And [dbo_time_entry].[time_sheet_recid] = [dbo_time_sheet].[time_sheet_recid]) And ([dbo_TE_Period].[te_period_recid] = [dbo_time_sheet].[te_period_recid] And [dbo_time_entry].[time_sheet_recid] = [dbo_time_sheet].[time_sheet_recid] And dbo_member.Member_ID = '" & Me.cboMemberID.Value & "'))"
End Sub
-------------------------------------------------------------------------------------
Thirdly, to speed it up, i was thinking of commensing the first half of the SQL Statement in the afterupdate property of cboMemberID. But, i would have to put it into a variable, and i dont know how to call a variable from another Sub, which i would call it in the gotfocus property of cboProjectID.
Fourthly, would this speed up the process, or is there a better way in doing this? Thanks in advance!
And some things, that should not have been forgotten, were lost.
First of all, are there any ideas?
Secondly this is my VBA Code for it so far:
Private Sub cboMemberID_gotfocus()
cboMemberID.RowSource = "SELECT DISTINCT dbo_Member.Member_ID FROM dbo_Member ORDER BY dbo_member.member_id "
End Sub
Private Sub cboMemberID_AfterUpdate()
Me.cboProjectID.Requery
End Sub
Private Sub cboProjectID_GotFocus()
cboProjectID.RowSource = "SELECT Distinct dbo_pm_project.Project_id FROM dbo_TE_Period INNER JOIN dbo_Time_Sheet ON dbo_TE_Period.TE_Period_RecID = dbo_Time_Sheet.TE_Period_RecID, dbo_MEmber INNER JOIN ((dbo_Company INNER JOIN dbo_PM_Project ON dbo_Company.Company_RecID = dbo_PM_Project.Company_RecID) INNER JOIN dbo_Time_Entry ON (dbo_Company.Company_RecID = dbo_Time_Entry.Company_RecID)) ON dbo_Member.Member_RecID = dbo_Time_Entry.Member_RecID, dbo_member_company inner join dbo_Member as [mem] ON dbo_member_company.member_recid = [mem].member_recid, dbo_member_company as [memco] inner join dbo_company as [com] ON [memco].company_recid = [com].company_recid " & _
"WHERE (([dbo_Company].[company_recid] = [dbo_time_entry].[company_recid]) And dbo_Member_Company.member_recid = dbo_member.member_recid And ((dbo_Company.company_recid) = (dbo_Member_Company.company_recid)) And ([dbo_pm_project].[company_recid] = [dbo_Company].[company_recid]) And ([dbo_member].[member_recid] = [dbo_time_sheet].[member_recid]) And ([dbo_TE_Period].[te_period_recid] = [dbo_time_sheet].[te_period_recid] And [dbo_time_entry].[time_sheet_recid] = [dbo_time_sheet].[time_sheet_recid]) And ([dbo_TE_Period].[te_period_recid] = [dbo_time_sheet].[te_period_recid] And [dbo_time_entry].[time_sheet_recid] = [dbo_time_sheet].[time_sheet_recid] And dbo_member.Member_ID = '" & Me.cboMemberID.Value & "'))"
End Sub
-------------------------------------------------------------------------------------
Thirdly, to speed it up, i was thinking of commensing the first half of the SQL Statement in the afterupdate property of cboMemberID. But, i would have to put it into a variable, and i dont know how to call a variable from another Sub, which i would call it in the gotfocus property of cboProjectID.
Fourthly, would this speed up the process, or is there a better way in doing this? Thanks in advance!
And some things, that should not have been forgotten, were lost.