Call a variable saved in a previous sub...?

[Rmck87]

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.

 

[Jooky68]

to call a variable from anywhere in your project you must make it global. It can not be a form level private variable. If you want just all the coding in a form or sheet to see the variable put a private/global variable

global variable as double 'for example

if you want your entire project to be able to use the variable you must declare the variable in a module at the top of a module(where option explicit woudl be) declare a global variable. Now all the forms/modules/etc... will be able to use the variable

is this what you were looking for?

 

[Rmck87]

Well, im not so sure that i understand you. I dont quite know how to create a module. So, instead of putting 'Option Explicit' at the top of the Module. I would put 'global variable as double' and variable, is the variable that i select? Then below taht i would put my code?

And some things, that should not have been forgotten, were lost.

 

[posselman]

Maybe i can give you some where speed is concerned,

first:

Isn't it more usefull to retrive the member id's on the activate event of the form and fill the projects there and after onexit event of the member id combobox?

second:

Ask yourself how may rows are retrieved in the case that projects for ALL the members id's are retrieved when activating the form, and stored for later use (only usefull in the case that a lot of switching between member id's is done )

Third:

How efficient is your query . You are doing a lot of joins
and it seems that a large cartesian product is made between
dbo_member and dbo_TE_Period (look at the formatted query)
This could be a major cause of speed decrease.

Formatted query:

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 & "'))"

 

[Rmck87]

First: Yes filling the member id in the activate of the form procedure sounds like a better idea. But i dont need to know the projects, until a member is selected. So i dont think that finding the projects would be all that great if found in the activate procedure.
Second: There are thousands of rows that will be retrieved in the case that projects for ALL the members id's are retrieved when activating the form. And im not quite sure i understand what you mean when you said it was only usefull in the case that a lot of switching between member id's is done.
Third: The formatted query doesn't look any different than the reqular one. Unless it's not supposed to! But, what if i took out all Inner Joins and put everything in the WHERE clause of the query? Would that speed it up? Like this:


Private Sub cboProjectID_GotFocus()
cboProjectID.RowSource =
"SELECT Distinct dbo_pm_project.Project_id
FROM dbo_member, dbo_member_company, dbo_company, dbo_pm_Project, dbo_time_entry, dbo_te_period, dbo_time_sheet
"WHERE dbo_te_period.te_period_recid = dbo_time_sheet.te_period_recid and dbo_time_sheet.time_sheet_recid = dbo_time_entry.time_sheet_recid and dbo_time_entry.pm_project_recid = dbo_pm_project.pm_project_recid and dbo_pm_project.company_recid = dbo_company.company_recid and dbo_time_entry.company_recid = dbo_company.company_recid and dbo_member.member_recid = dbo_time_entry.member_recid and dbo_member.member_recid = dbo_member_company.member_recid and dbo_company.company_recid = dbo_member_company.company_recid and dbo_member.member_id = '" & me.cboMemberID.value & "'"
End Sub

I threw in an extra field. But it makes things limited a little better.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Jooky68]

Under where you have option explicit you can declare variables. This is where youc an declare global variables


option explicit
public variable as double
private variable2 as string



setting it to global in a forms coding will allow the variable to be seen by all forms. Setting it to private will allow the variable to be seen by all procedures within the form.

If you go to project explorer within vb and right clikc the project, add a module. Go to that module and in the module declare your variables (public). Those variables will be able to be seen by all procedures within your project.

 

[Rmck87]

Ok i get it.... Thanks Jooky68

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[posselman]

Morning,



The reformatting that i did was for readability purposes only, i did not change anything.
I'm afraid that the only way to speed up will be to make the query more efficiënt. Did you look at my suggestion of the cartesian produkt? I tried to find an obvious connection between dbo_member and dbo_TE_Period and i found a indirect one in the where clause. I think you can save a lot of time by analyzing the query in the mssql query analyzer, setting the most restrictive joins and where equations first. Another suggestion: Where clauses are often processed on the recordset created before in the joins. So it can save processing time to analyze the where clause for equations that should be placed in a join. I favour the join construct for the implicit variant, so i would not take out the inner joins.

You probably looked at it, but are al the search argument indexed reads?

What i meant by the switching thing is:
in the cliënt server way it can be worthwile to retrieve al the project member info on beforehand. This will take the form a while longer to load, but will be faster in the search (the data is already cliënt side, in memory). What i meant by a 'lot of switching' is that in your form the member_id is changing a lot (there is a lot of project searching done). If it's still not clear what i mean than just leave it that way; it was not the most important suggestion

 

[Rmck87]

Oh, so what your saying is to find all of the fields in the tables whenever i load the form, instead of whenever the combo gets the focus, that way it only has to select the specific fields?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.