User defined entries in a report 1

[Yukonbanzai]

I have a report that lists information about students for going on trips. I need a way that the user can decide which students names will appear on the report. Students that are not on the trip shouldn't be on the report.

Any help appreciated

 

[scriverb]

Create a form named frmSelectStudent. On this form create a ListBox and a Command Button. Name the ListBox: lstStudents. Name the Command Button: cmdRunReport The ListBox should have its MultiSelect property set to Simple. The RowSource should be a table with all of the student names as they appear in your data table.

The VBA code in the command button's OnClick event procedure should look like this:

Dim frm As Form, ctl As Control, vStudents As String
Dim varItm As Variant
Set frm = Forms!frmSelectStudents
Set ctl = frm!lstStudents
    For Each varItm In ctl.ItemsSelected
        vStudents = vStudents & ctl.ItemData(varItm) & ","
    Next varItm
vStudents = Left(vStudents, Len(vStudents) - 1)
DoCmd.OpenReport "[red]rptStudentList[/red]", , , "InStr(1,vStudents,[[red]Name[/red]])>0"

Now when you want to run this report open this form, select the students to be included and then click the command button. The WHERE parameter of the OpenReport command should select the students for the report that have been selected in the list box.

Post back if you have more questions or problems.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Yukonbanzai]

Obviously I mucked something up.
Tables that I have are
"Data" - contains the names of the cadets(students)
Have created the form "frmSelectCadet"
List box is named "lstCadets"
This is the code behind the On Click

Dim frm As Form, ctl As Control, vData As String
Dim varItm As Variant
Set frm = Forms!frmSelectCadet
Set ctl = frm!lstCadets
For Each varItm In ctl.ItemsSelected
vData = vData & ctl.ItemData(varItm) & ","
Next varItm
vStudents = Left(vData, Len(vData) - 1)
DoCmd.OpenReport "Medical Data", , , "InStr(1,vData,[Medical Data])>0

 

[Yukonbanzai]

Forgot to mention that the report name is"Medical Data

 

[Yukonbanzai]

Also forgot to say that with the above code I get a pop up that requests "Enter Parameter Value" and "vData

 

[scriverb]

Just missed a couple of items. See red update:

Dim frm As Form, ctl As Control, vData As String
Dim varItm As Variant 
Set frm = Forms!frmSelectCadet
Set ctl = frm!lstCadets
    For Each varItm In ctl.ItemsSelected
        vData = vData & ctl.ItemData(varItm) & ","
    Next varItm
[red]vData[/red] = Left(vData, Len(vData) - 1)
DoCmd.OpenReport "Medical Data", , , "InStr(1, vData,[[red]NameDataField[/red]])>0"

The NameDataField is the field in your table that has the cadet names. You have not posted this info so identify that Field name and replace the red NameDataField above.

Post back with questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

I still get the pop up "Enter Parameter Value" for "vData"
If I click enter without entering a value, it prints the report without any information, just the headers, etc.

 

[Yukonbanzai]

The field for the cadet name is "Last Name". Also have a field for "First Name".

Tried entering a cadet's name and it printed out fine.

Is there also a way to add the first name? Have multiple last names.

 

[scriverb]

Yes, we now should use a query for the Row Source for the ListBox rather than the table. That way we can combine the Last Name and First Name into one name seperated by a comma. Use the following SQL to create a saved query and name it qryCadetNames:

SELECT A.[Last Name] & ", " & A.[First Name] AS Cadet_Names
FROM Data as A 
GROUP BY A.[Last Name] & ", " & A.[First Name]
ORDER BY A.[Last Name] & ", " & A.[First Name];

Update the Row Source of the list box to qryCadetnames. The bound column = 1, column count = 1, column widths = 2".

Now we need to use a query instead of the table as the Record Source for the report Medical Data. Use the following SQL in a saved query and name it qryData.

Select A.*, A.[LastName] & ", " & A.[FirstName] AS Cadet_Names 
FROM Data as A
Order By A.[LastName] & ", " & A.[FirstName];

Now we need to change just one line in the code above to accomodate the new scheme:

Dim frm As Form, ctl As Control, vData As String
Dim varItm As Variant
Set frm = Forms!frmSelectCadet
Set ctl = frm!lstCadets
    For Each varItm In ctl.ItemsSelected
        vData = vData & ctl.ItemData(varItm) & "[red];[/red]"
    Next varItm
vData = Left(vData, Len(vData) - 1)
DoCmd.OpenReport "Medical Data", , , "InStr(1, vData,[Cadet_Names])>0"

Follow the above instructions and you should be able to pick specific cadets by a combination of last and first names and they will be the records displayed in the report.

Post back if you have any other problems.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

Before I change things, The list box that I currently have is showing the cadets full name. If I can get rid of the "enter parameter" pop up, it should be OK??

 

[scriverb]

The Enter Parameter Value is caused by the variable vData. We need vData to be declared in a database module as a Global variable and remove it from the Dim statement in the OnClick event code. Then in the database module create a Function so that the query can use it to makes is selection.

Global vData as String
Public Function Cadet_Names() as String
Cadet_Name = vData
End Function

Now change the last line of code in the code I provided to the following:

DoCmd.OpenReport "Medical Data", , , "InStr(1, Cadet_Names(),[Cadet_Names])>0"

Now remember you said that the list box has the full name but it must have the full name in exactly the same format as the data from your table in the name field. What I provided before will match. So, I am not sure if this is going to work as I can't see exactly what you have.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

The row source in the list box is

SELECT Data.[Health Care #], Data.[Last Name], Data.[First Name] FROM Data ORDER BY [Last Name];

 

[scriverb]

Okay, we can do this without changing your query but what I gave you is already completed and it will work. So, do you want to try what I have given you or do you want me to redo it with your existing query and table? The method I provided is a little easier that what we will have to do using your setup right now.

Why don't you make a copy of your database and make the changes in the copy and run it and see if you can make it work with what I have provided for you in my original postings.

Let me know.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

I've created the queries "qryCadetNames" and "qryData"
Following code is behind my command button.
Getting a pop up to enter parameter "Cadet_Names"

Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click

Dim frm As Form, ctl As Control, vData As String
Dim varItm As Variant
Set frm = Forms!frmSelectCadet
Set ctl = frm!lstCadets
For Each varItm In ctl.ItemsSelected
vData = vData & ctl.ItemData(varItm) & ";"
Next varItm
vData = Left(vData, Len(vData) - 1)
DoCmd.OpenReport "Medical Data", , , "InStr(1, [Cadet_Names],[Cadet_Names])>0"

Exit_cmdRunReport_Click:


Exit Sub

Err_cmdRunReport_Click:
MsgBox Err.Description
Resume Exit_cmdRunReport_Click

End Sub

 

[scriverb]

Go back to my posting about the Dim statement and the Global variable and function. Add those changes.

After making those changes try running it. That should clear it up.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

I've put the code into a module.
What exactly do I have to do to remove the vdata from the DIM statement. Deleting it doesn't work.

Sorry for being so dense

 

[scriverb]

Remove the red code including the comma:

Dim frm As Form, ctl As Control[red], vData As String[/red]

Make sure that the database module has the following:

Global vData as String
Public Function Cadet_Names() as String
Cadet_Name = vData
End Function

That should do it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

Deleted the DIM part, no problem
Pasted the code into a module. Has name of "Module1"

 

[scriverb]

That's okay for a name or you can name it something like VariableAndFunctions. The name is not important, just the code within is necessary.

You see in a query you cannot reference a variable value except through a Function call that passes the value of a global variable. In the code behind the command button we are building a string of Cadet names (Last, First) seperated by a semicolon. That string will be however large it needs to be to represent all of the selected cadets from the list box.

Then the Where clause parameter of the OpenReport will pass the expression that we created that looks for the existence of the cadets name in this string. We are looking for the Position of the name in the large string. So, if it brings back a number larger than 0 then we must have found the name in the string so include this record in the report. This really does work and is a good technique to use in this instance.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Yukonbanzai]

When I click the report button I still get a parameter request for "Cadet_Names".

Also curious if the "qryData" should end up bring up the entire "Data" table?