Creating another Form from a Form 2

[StayAtHomeMom]

I have a database with a form used by my customers where they can select certain criteria in several list boxes. When they’re done choosing their criteria, they push a button that runs code to create a report with a list of items from a table that meets their criteria ( I used DoCmd.OpenReport…)

What I’d LIKE to do is instead of opening a report is open a form with a listbox of all the items that meet the criteria. Because then, I’d like them to be able to click on an item in that listbox which would open a file (using a hyperlink??) showing them a sample of their item (like a .snp file, or Word file).

I’m stuck on how to use OpenForm with my code-created WhereString. What does the form have to have in it? I tried creating a form with a ListBox , with the form itself having a RecordSource of a query that selects everything out of that table I’m using. But it never takes my WhereString into consideration, instead listing EVERYTHING in the table, and not just those meeting the selected criteria. I think it’s because I’m confused about the properties the ListBox should have (ControlSource, RowSource, etc). This is the line in my code that’s invoking my Form, where I’m trying to get it to list only those items that have created the gstrWhere clause.

DoCmd.OpenForm "ListOfReports", , , gstrWhere

Thank you in advance for steering me in the right direction!

 

[snyperx3]

If your form will be static, but the listbox changing, i would not set the forms recordsource, but the listbox's instead. I take it your Where string is global (by the variable name gstrWhere)...why not build the query when the form loads.

Private Sub Form_Load()
ListBox1.RowSource = "SELECT * FROM whatever " & gstrWhere & ";"
End Sub

so when you open the form just use
DoCmd.OpenForm "ListOfReports"

And you could set up the on click or on double click event of the list box.

-Pete

 

[StayAtHomeMom]

Now for the dumb questions:
How do I ensure my gstrWhere is global? I don't think it is, because it's built in the Private subroutine Click on the button in the form where the users are selecting their criteria. Is it as simple as changing my "Private" to "Public"?

 

[snyperx3]

You should declare the variable in a module.
and yes make sure you use Public

Public gstrWhere as String

you can change it or access it whereever you want now.


-Pete

 

[snyperx3]

Naming conventions are important. If you're not the only one working on your project it helps EVERYONE else.

In the variable you made gstrWhere -
g = Global
str = String
Where = Something to easily define what is in the variable

When you mislabel a variable it will make everyone else confused. =]

-Pete

 

[StayAtHomeMom]

Thanks for the naming convention tips, snyperx3. I couldn't agree with you more. I "inherited" this code from an ex-employee, and I'm suffering the consequences!

I'm still having a hard time with the details! My code "works" (it opens the form without errors), but the list is empty. It's not recognizing my Where string portion.

This is the code where I'm building my gstringWhere, based on selections the user picks in ListBoxes on a form:

Public Sub FindReports_Click()
Dim stDocName As String
Dim varItem As Variant
Dim varItem1 As Variant
Dim varItem2 As Variant
Dim varItem3 As Variant
Dim gstrWhereSumDetail As String
Dim gstrWhereResaleCost As String
Dim gstrWhereReportType As String
Dim gstrWhereHistoricalData As String
Dim gstrWhereProduct As String
Dim gstrWhereSIC_Code As String
Dim gstrWhereCommRate As String
Dim gstrWherePartNumb As String
Dim gstrWhereCurrentMonth As String
Dim gstrWhere As String
.
.
.
Report:
gstrWhere = ("(" & gstrWhereSumDetail & ") and (" & gstrWhereResaleCost & ")and (" & gstrWhereReportType & ") and (" & gstrWhereCurrentMonth & ") and (" & gstrWhereHistoricalData & ") and (" & gstrWhereProduct & ") and (" & gstrWhereSIC_Code & ") and (" & gstrWhereCommRate & ") and (" & gstrWherePartNumb & ")")

DoCmd.OpenForm "ListOfReports"

Then, in my form where I want the results to go, I have:

Private Sub Form_Load()
QualifiedRpts.RowSource = "SELECT * FROM MasterReportTable where " & gstrWhere & ";"
End Sub

which is the OnLoad event procedure code. As I said, the form appears with the list box, but it's empty. When I look at the properties box of the ListBox, the RowSource is

SELECT * FROM MasterReportTable where ;

proving it's not picking up my gstrWhere.

Any suggestions??

 

[PHV]

And what about OpenArgs instead of a (not yet) global variable ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[StayAtHomeMom]

I'm having a heck of a time with the syntax if I use OpenArgs (I've never used them before). Is OpenArgs what I would use to set the RowSourceType and RowSource for the listbox in the Form I'm opening? I'm getting VERY confused!

 

[PHV]

Launch your form like this:
DoCmd.OpenForm FormName:="ListOfReports", OpenArgs:=gstrWhere

And then in the Load event procedure of the form:
QualifiedRpts.RowSource = "SELECT * FROM MasterReportTable WHERE " & Me.OpenArgs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[StayAtHomeMom]

PHV,
That was it!!!! Thank you, thank you, thank you!!