Solved

DE Report with Query question

Posted on 2002-06-05
9
257 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 50 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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