DE Report with Query question

Hi,
I'm creating a report showing a list of names with a list of items belong to each name.  I have 1 table, and 1 query, they connect to each other by FirstName and LastName fields.  Another word, i have tblName with NameID, FirstName, LastName, DOB, and other fields.  qryItem has ItemName, FirstName, LastName, and some other fields.  I created a DE with a command named cmdTblName.  This command is connected to table tblName and i grouped them with fields FirstName and LastName.  I added a Child Command to this cmdTblName and name it cmdItem.  This child command is a SQL statement select from the qryItem.  And i defined the relationship between the 2 commands to be FirstName TO FirstName, and LastName TO LastName.  On the report, i connected the DataMember to the cmdTblName_Grouping and display the report.  The report looks like this...
FirstName: aaa  LastName: bbb
Item: ia1
Item: ia2
Item: ia3
FirstName: bbb  LastName: ccc
Item: ib1
Item: ib2
...

That works fine.  Now the problem is, i have to get an input from the form, so i have to write a query to do the same thing.  How do i do that?

Thanks!

TommyN14
LVL 1
TommyN14Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
K7Connect With a Mentor Commented:
ok the way the whole thing works is by changing the DE at runtime to include the filtering for the names.  This is done by appending a where clause that filters the names to the SQL in the first command.  Bc the list of names that should aper is determaned at runtime, the where clause must also be constructed at runtime.  Once the where caluse is built it is appended to the SQL in the first command.

In the code above SQLtail starts empty as VB initialises all string to an emptystring.

Set DE = New datEnv
This line crates a new instance of your data environment and stores a reference to that instance in DE.  Note the name of the variable (DE) and the name of the data environment  should be different

SQLtail = SQLtail & " WHERE SecName IN (" & "'FirstNameInList'"
This line stores the beginning of the where clause( inc the first of the names that should paear in the report) into SQLtail.  I don't know how the user enters the names that shoud apear so it is difficult to write the correct code.  so u would have to replace "'FirstNameInList'"
with apropreate code, eg if the name were in a txt box it would look like    "'" & txtName1.Text & "'"

actualy instead of me writing a lot of unrelevant things how about u email me the project and i will incerte the required code and fully coment it so that u understand wht is going on.

my address is mattess@alphalink.com.au


Cat
0
 
K7Commented:
Hi
by "input from the form" do u mean some kind of limitation on which names and items are used in the report??
U can do this by passing parameters to the command when executing it befor displaying the data report.
You can also create the shaped recordset (the object the data rep uses) in code but it a bit complex than just using the datEnv with parameters.

if this is what u want to do let me know and i can tell u how to go about it.

Cat
0
 
TommyN14Author Commented:
hi K7,
Well, i have a form with a list of Name, the user will select a Name from the list to run the report.  The report will filter and show only the name that selected from the form.  Now i only have the DE and command that works without the capability of filter by Name.  Codes are appreciated.

Thanks!

TommyN14
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
TommyN14Author Commented:
Any1 have any idea?

TommyN14
0
 
K7Commented:
Hi
i had a quick try at sending a list of names as a singe parameter but failed. Maybe another expert knows more about this.
But there is another way, and that is to generate the SQL for the command that gets the names.

firsly make the command that gets the names use a SQL statement like  SELECT FirstName, SecName from TableNames

so then your code will looke somthing like this:

Dim DE As datEnv 'where DatEnv is the name of ur data environment
Dim SQLtail As String

Set DE = New datEnv
'BTW don't move the New into the Dim statement bc this will create a new instance as soon as DE is set to Nothing

SQLtail = SQLtail & " WHERE SecName IN (" & "'FirstNameInList'"

'loop to add names to SQL
Do Until AtEndOfList = True
    SQLtail = SQLtail & ", '" & NameFromList & "'"
Loop

SQLtail = SQLtail & ")"

'here u could amend a order by  clasuse to the SQLtail

DE.Commands("NameCommand").CommandText = SQLtail

'display report
DataReport1.Show vbModal

'free recources
DE.Conection1.Close
Set DE = Nothing


I hope this helps

Cat
0
 
K7Commented:
Hi
i had a quick try at sending a list of names as a singe parameter but failed. Maybe another expert knows more about this.
But there is another way, and that is to generate the SQL for the command that gets the names.

firsly make the command that gets the names use a SQL statement like  SELECT FirstName, SecName from TableNames

so then your code will looke somthing like this:

Dim DE As datEnv 'where DatEnv is the name of ur data environment
Dim SQLtail As String

Set DE = New datEnv
'BTW don't move the New into the Dim statement bc this will create a new instance as soon as DE is set to Nothing

SQLtail = SQLtail & " WHERE SecName IN (" & "'FirstNameInList'"

'loop to add names to SQL
Do Until AtEndOfList = True
    SQLtail = SQLtail & ", '" & NameFromList & "'"
Loop

SQLtail = SQLtail & ")"

'here u could amend a order by  clasuse to the SQLtail

DE.Commands("NameCommand").CommandText = SQLtail

'display report
DataReport1.Show vbModal

'free recources
DE.Conection1.Close
Set DE = Nothing


I hope this helps

Cat
0
 
K7Commented:
I swear that i click submit only once  :)
0
 
TommyN14Author Commented:
Hi K7,
I'm kinda confuse here.  First of all, what is SQLtail starts out with? an empty string?  Secondly, do you really understand my question?  Am i making myself clear?

I'll increase 30 more pts if i get the answer.
TommyN14
0
 
TommyN14Author Commented:
I solved the problem by myself couple months ago.  Anyway thanks for you help, K7.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.