Okay I need to add some more where clause to the select statement that is being passed to the MakeCSV module. I have the select statement that is used to create the report with the new criteria. I made strTemp a global variable in hopes that when I assign a string to it in the List109_update that I could still reference that in the function that creates the State csv. But when I get to the function that creates the csv strTemp is blank.

This is the code I have that creates the select statement to make the CSV list of states


strSQL = "SELECT DISTINCT State FROM School "
strSQL = strSQL & " INNER JOIN Scholarship "
strSQL = strSQL & " ON School.SchoolID = Scholarship.SchoolAffiliation "
strSQL = strSQL & " WHERE Scholarship.MinimumGPA <= " & Me.GPA
strSQL = strSQL & " AND Scholarship.MinimumSATACT <= " & Me.SAT


And this is the code that I made that creates the select statement to create the report.


strSQL = "PARAMETERS [FORMS]![SAT].[ACT] Long, [FORMS]![SAT].[GPA] IEEEDouble; SELECT Scholarship.SchoolAffiliation, Scholarship.ScholarshipWeb, Scholarship.AmountOffered, School.Name, School.Street, School.City, School.State, School.ZipCode, School.Phone, School.AdmissionRegDeadline, School.AdmissionEarlyDeadline, School.FinaidRegDeadline, School.FinaidEarlyDeadline, School.CollegeHomePage, School.AdmitEmail, School.FinaidEmail, Scholarship.ScholarshipName, Scholarship.MinimumGPA, Scholarship.MinimumSATACT, Scholarship.minimumACT, Scholarship.MinimumClassRank, Scholarship.ScholarshipDeadline, Scholarship.[Renewable?], Scholarship.ScholarshipWeb, Scholarship.SeparateEssay, Scholarship.SeparateInterview, Scholarship.Major, Scholarship.Audition, Scholarship.Notes, Scholarship.RenewNotes, School.Field15 FROM School INNER JOIN Scholarship ON School.SchoolID = Scholarship.SchoolAffiliation WHERE (((Scholarship.MinimumGPA)<=[FORMS]![SAT].[GPA]) AND ((Scholarship.MinimumACT)<=[FORMS]![SAT].[ACT])) AND "
List109.SetFocus
For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "(State) = " & "'" & Me.ActiveControl.ItemData(varItem) & "'" & " Or "

Next

strSQL = strSQL & "(" & Left(strTemp, Len(strTemp) - 4) & ");"


I need to add the strTemp to the select statement that is used to make the CSV.

Should be as simple as

strSQL = "SELECT DISTINCT State FROM School "
strSQL = strSQL & " INNER JOIN Scholarship "
strSQL = strSQL & " ON School.SchoolID = Scholarship.SchoolAffiliation "
strSQL = strSQL & " WHERE Scholarship.MinimumGPA <= " & Me.GPA
strSQL = strSQL & " AND Scholarship.MinimumSATACT <= " & Me.SAT
strSQL = strSQL & "(" & Left(strTemp, Len(strTemp) - 4) & ");"


But strTemp is blank by the time this code is ran. Nothing clears out the string and strTemp is on top of the VBA code under Option Explicit which as far as I remember in my VB6 class in college makes it global. I even tried setting a textbox to strTEmp and then getting the contents out that textbox back into strTemp when needed and it gave an error. Not at home right now so I cannot give you the error. Something cannot do that when updating a control. I guess I cannot setfocus on a textbox inside a List190_afterUpdate function.

Going to try and define the variable as
Dim Global public strTemp as String

when I get home to see if that works it is defined now as
Dim strTemp as String
and neither function has problems finding the variable so maybe that's not the issue.

Hi James,

Global variables are generally a bad idea. Sometimes even for the reason you're having this problem.

The correct declaration would be:
Global strTemp as String

If it is already declared that way (I see you've been experimenting), what module is it declared in? Is it a form module (wrong way) or a regular project module?

Another thing you could do is "encapsulate" the Global in its own module along with the procedures/functions that work with it. In a case like this, you might have a GetStrTemp() as String function and a SetStrTemp(strIn as String) subroutine, both Public, of course.

I think I'm rambling. Let us know if this helps or you need more help.

The strTemp variable is declared as Dim strTemp as String right underneath Option Explicit on the top there.

Yes this code is inside a form.
I have the code that makes the CSV inside List190_AfterUpdate()-List190 is a list box that holds a list of states

I have the code that creates the select statment that the report uses under a button_click event.

If I could just get that variable from the AfterUpdate event and to the Click event I would be good.

I have seen Get and Set with Java and other OOP languages. Im just looking for easiest way to get this done. Im going to vacation tomorrow and need to have it done tonight.

Since I have not done this before with VB can you possibly give an example. Im not sure how to name the module, how to call a module function from inside an event. What would I use to store the strTemp variable?

Simplest way is just put it in a universal module with
Global strTemp as String

You should be able to manipulate it anywhere pretty much invisibly. Be sure to get rid of the other one, though.

So I create a module and the only code I have in it would be Global strTemp as String. I can then use it like a regular variable, but I can accees the contents in List190_AfterUpdate and Button_Click events

If you want it available throughout the single form module, declare it at the top of the form module like:

Private strTemp As String

If you want it available from anywhere (at the top of a standard module):

Public strTemp As String

You can also declare it as Public in a Form Module but then you need to refer to the form class to get to it from outside of the form:

Form_FormNameHere.strTemp

Global and Public are the same things. It is just that Global is left over from Access 97 and prior and Access 2000 started using VB6 based VBA which Public was now the current "Global."

If you want it available throughout the single form module, declare it at the top of the form module like:

Private strTemp As String

If you want it available from anywhere (at the top of a standard module):

Public strTemp As String

You can also declare it as Public in a Form Module but then you need to refer to the form class to get to it from outside of the form:

Form_FormNameHere.strTemp

I did not declare it as Private I will try and when I get home. I have Access 97 here at work and some code does not run on 97. I am using 2000 at home.

If you are using 97 then I would stick with GLOBAL as the keyword.

If you are using 97 then I would stick with GLOBAL as the keyword.

No I mean that some of my code inside my Module that takes the results of query and puts them into a comma delimited string does not work in Access 97. Plus I did not email myself the database today since it would not work.

I just tried using strTemp as you stated and it worked, I then just declared it as

Dim strTemp as String

and it also worked. This is how it's setup at home and still strTemp is blank. I will have to take a closer look and see why this is.

I put the code Private strTemp as String under Option Explicit instead of above it and that worked. Just in time for vacation. Hope that guy Im doing a project for does not try to add anything else.