Data Report

[palsonjj]

Haai,

nw only i've joined here....

i've prob wit Data report... mmm.... how to show the last record frm Database to the Data Report? anyone help me plzzzzz?

[cmrhema]

Haai,

nw only i've joined here....

i've prob wit Data report... mmm.... how to show the last record frm Database to the Data Report? anyone help me plzzzzz?

I presume you want the last record of the database to be printed on the data report.

you must be having a field that is unique eg. say empno is unique
now use a recordset

Myrs.Open "select empno from emp" ,...........
Myrs.MoveLast
'Declare a variable and store the empno in it
Dim Emp as integer
Emp=val(Myrs.Fi elds(0))

So you have the last value stored in a variable named Emp
Now when you show the report try
Set m1 = Mycon1.Execute( "select empno,empname from emp where empno=" & Emp & "", , adCmdText)

Note: I have not used data environment.
Check out and lets see

[Dököll]

Haai,

nw only i've joined here....

i've prob wit Data report... mmm.... how to show the last record frm Database to the Data Report? anyone help me plzzzzz?

Greetings, Haai!

Is VB getting this report from a queried table built through a Pass-Through query or a table?

If from a Pass-Through query, you may need to refresh your make table query to gather new information from the original table VB is looking at...

Hope this helps, Haai!

Dököll

[palsonjj]

Haai Evrybody,

Thnk you vry much... it works... thanks again..

nw i've to show a monthly wise reports which user only select the month.... is't possible using Data Environment r any other idea wit code Plzzzzzzzzzzzzz zzzzzz......

[palsonjj]

I presume you want the last record of the database to be printed on the data report.

you must be having a field that is unique eg. say empno is unique
now use a recordset

Myrs.Open "select empno from emp" ,...........
Myrs.MoveLast
'Declare a variable and store the empno in it
Dim Emp as integer
Emp=val(Myrs.Fi elds(0))

So you have the last value stored in a variable named Emp
Now when you show the report try
Set m1 = Mycon1.Execute( "select empno,empname from emp where empno=" & Emp & "", , adCmdText)

Note: I have not used data environment.
Check out and lets see

Haai,

Thnk you vry much... it works... thanks again..

nw i've to show a monthly wise reports which user only select the month.... is't possible using Data Environment r any other idea wit code Plzzzzzzzzzzzzz zzzzzz......

[cmrhema]

Haai,

Thnk you vry much... it works... thanks again..

nw i've to show a monthly wise reports which user only select the month.... is't possible using Data Environment r any other idea wit code Plzzzzzzzzzzzzz zzzzzz......

Yes you can easily show monthly wise reports. But kindly let me know how are you going to use the month field. Are you using dtpicker control or any other control. Further plz send details of your fields in the table eg. empid,empno etc. particularly the field where you are going to use the month.
I will send a snapshot of the code after you send me the details.

[palsonjj]

Yes you can easily show monthly wise reports. But kindly let me know how are you going to use the month field. Are you using dtpicker control or any other control. Further plz send details of your fields in the table eg. empid,empno etc. particularly the field where you are going to use the month.
I will send a snapshot of the code after you send me the details.

Hello,

first of all vry vry vry thnks 4 ur replyin...

yes, i am using 2 DtPicker ctrl for select a month.. thn, i've two tables Named tblSales1 & tblSales2...
tblSales1 :
InvId-text-Invoice Id--> System Date
InvNo-number-Invoice No--> User Input
CustName-text--> User Input
CustAddress-text-->user Input
InvDate-text---> Date of Sales********* v've to select records based dis field
RefNo-Text
SalesMan-text
Received By-text

dis Inv No has more Products to sales. so, i've created other table named tblSales2:
InvId-Text--> dis s related to tblSales1's InvNo..
PartNo-text-Products Id
Description-text--->wat product dis s..
Price--Number-->price of Product
Quantity-Number-->quantity of product
Amount--Number--> sum of price and quantity

frm these 2 tables , v've 2 display monthly wise reports....for example, if user select 1/1/2007 to 2/1/2007 , thn within tat months datas only display in Report..

k?

[cmrhema]

Hello,

first of all vry vry vry thnks 4 ur replyin...

yes, i am using 2 DtPicker ctrl for select a month.. thn, i've two tables Named tblSales1 & tblSales2...
tblSales1 :
InvId-text-Invoice Id--> System Date
InvNo-number-Invoice No--> User Input
CustName-text--> User Input
CustAddress-text-->user Input
InvDate-text---> Date of Sales********* v've to select records based dis field
RefNo-Text
SalesMan-text
Received By-text

dis Inv No has more Products to sales. so, i've created other table named tblSales2:
InvId-Text--> dis s related to tblSales1's InvNo..
PartNo-text-Products Id
Description-text--->wat product dis s..
Price--Number-->price of Product
Quantity-Number-->quantity of product
Amount--Number--> sum of price and quantity

frm these 2 tables , v've 2 display monthly wise reports....for example, if user select 1/1/2007 to 2/1/2007 , thn within tat months datas only display in Report..

k?

Try out and reply
[HTML]
Dim Mycon As New ADODB.Connectio n
Dim M2 As New ADODB.Recordset
Dim S1 As String

S1 = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & _
App.Path & "tblSales1. mdb"
M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimisti c

'Here I have used two dtpicker controls which consists of any two dates

Set DataReport2.Dat aSource = M2
DataReport2.Win dowState = vbMaximized
DataReport2.Sho w vbModal
M2.Close
Mycon.Close[/HTML]

[palsonjj]

Try out and reply
[HTML]
Dim Mycon As New ADODB.Connectio n
Dim M2 As New ADODB.Recordset
Dim S1 As String

S1 = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & _
App.Path & "tblSales1. mdb"
M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimisti c

'Here I have used two dtpicker controls which consists of any two dates

Set DataReport2.Dat aSource = M2
DataReport2.Win dowState = vbMaximized
DataReport2.Sho w vbModal
M2.Close
Mycon.Close[/HTML]

mmmmmm.....The followin error occured

" Run-Time Error '3709;

The Connection cannat be used to perform this operation . it is either clsoed or invalid in this contest."...... .. ??

[cmrhema]

InvDate-text---> Date of Sales********* v've to select records based dis field

k?

Change the datatype from text to Date format in MS-Access
Or in the query first change the text to date format

[palsonjj]

Change the datatype from text to Date format in MS-Access
Or in the query first change the text to date format

i've changed tat invdate datatype date/time... nw also same error occured....?? :(

[cmrhema]

i've changed tat invdate datatype date/time... nw also same error occured....?? :(

Kindly put down your code

[palsonjj]

Dim Mycon As New ADODB.Connectio n
Dim M2 As New ADODB.Recordset
Dim S1 As String
form_load()
S1 = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & _
App.Path & "tblSales1. mdb"


cmd_click()
M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimisti c

'Here I have used two dtpicker controls which consists of any two dates

Set DataReport2.Dat aSource = M2
DataReport2.Win dowState = vbMaximized
DataReport2.Sho w vbModal
M2.Close
Mycon.Close

[cmrhema]

Dim Mycon As New ADODB.Connectio n
Dim M2 As New ADODB.Recordset
Dim S1 As String
form_load()
S1 = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & _
App.Path & "tblSales1. mdb"


cmd_click()
M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimisti c


'Here I have used two dtpicker controls which consists of any two dates

Set DataReport2.Dat aSource = M2
DataReport2.Win dowState = vbMaximized
DataReport2.Sho w vbModal
M2.Close
Mycon.Close

Replace the code

[HTML]M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimisti c[/HTML]

to below and check whether you get the relevant data for the particular date and then let me know.

[HTML]M2.Open "select * from tblSales1 where InvDate=#" & DTPicker1.Value & "# ",Mycon, adOpenDynamic, adLockOptimisti c[/HTML]

and kindly ensure that you do have datas for that particular date.

[palsonjj]

Replace the code

[HTML]M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimisti c[/HTML]

to below and check whether you get the relevant data for the particular date and then let me know.

[HTML]M2.Open "select * from tblSales1 where InvDate=#" & DTPicker1.Value & "# ",Mycon, adOpenDynamic, adLockOptimisti c[/HTML]

and kindly ensure that you do have datas for that particular date.

Hello Hema,

first of all thnku vry vry much 4 ur helpin..... i've d datas within d date...

anyway, i've tried other way. create a query table[view table] dynamically at runtime using "queryDef". . then, call tat table 2 d data environment... nw it'll works .... hoooooooorrrrrr rrrrrrraaaaaaaa aaaaaaayyyyyy.. .