Solved

DE Report with Query question

Posted on 2002-06-05
9
253 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now