Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DE Report with Query question

Posted on 2002-06-05
9
Medium Priority
?
266 Views
Last Modified: 2010-05-03
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
0
Comment
Question by:TommyN14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 1

Expert Comment

by:K7
ID: 7058668
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
 
LVL 1

Author Comment

by:TommyN14
ID: 7059543
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
 
LVL 1

Author Comment

by:TommyN14
ID: 7060898
Any1 have any idea?

TommyN14
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:K7
ID: 7061101
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
 
LVL 1

Expert Comment

by:K7
ID: 7061102
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
 
LVL 1

Expert Comment

by:K7
ID: 7061106
I swear that i click submit only once  :)
0
 
LVL 1

Author Comment

by:TommyN14
ID: 7061196
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
 
LVL 1

Accepted Solution

by:
K7 earned 150 total points
ID: 7061362
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
 
LVL 1

Author Comment

by:TommyN14
ID: 7831669
I solved the problem by myself couple months ago.  Anyway thanks for you help, K7.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question