By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,096 Members | 1,348 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,096 IT Pros & Developers. It's quick & easy.

Data Report

palsonjj
P: 18
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?
Apr 4 '07 #1
Share this Question
Share on Google+
16 Replies


100+
P: 375
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.Fields(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
Apr 4 '07 #2

Dököll
Expert 100+
P: 2,364
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
Apr 7 '07 #3

palsonjj
P: 18
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 Plzzzzzzzzzzzzzzzzzzz......
Apr 20 '07 #4

palsonjj
P: 18
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.Fields(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 Plzzzzzzzzzzzzzzzzzzz......
Apr 20 '07 #5

100+
P: 375
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 Plzzzzzzzzzzzzzzzzzzz......
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.
Apr 20 '07 #6

palsonjj
P: 18
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?
Apr 20 '07 #7

100+
P: 375
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.Connection
Dim M2 As New ADODB.Recordset
Dim S1 As String

S1 = "Provider=Microsoft.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, adLockOptimistic

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

Set DataReport2.DataSource = M2
DataReport2.WindowState = vbMaximized
DataReport2.Show vbModal
M2.Close
Mycon.Close[/HTML]
Apr 20 '07 #8

palsonjj
P: 18
Try out and reply
[HTML]
Dim Mycon As New ADODB.Connection
Dim M2 As New ADODB.Recordset
Dim S1 As String

S1 = "Provider=Microsoft.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, adLockOptimistic

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

Set DataReport2.DataSource = M2
DataReport2.WindowState = vbMaximized
DataReport2.Show 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."........ ??
Apr 21 '07 #9

100+
P: 375
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
Apr 21 '07 #10

palsonjj
P: 18
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....?? :(
Apr 23 '07 #11

100+
P: 375
i've changed tat invdate datatype date/time... nw also same error occured....?? :(
Kindly put down your code
Apr 23 '07 #12

palsonjj
P: 18
Dim Mycon As New ADODB.Connection
Dim M2 As New ADODB.Recordset
Dim S1 As String
form_load()
S1 = "Provider=Microsoft.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, adLockOptimistic

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

Set DataReport2.DataSource = M2
DataReport2.WindowState = vbMaximized
DataReport2.Show vbModal
M2.Close
Mycon.Close
Apr 23 '07 #13

100+
P: 375
Dim Mycon As New ADODB.Connection
Dim M2 As New ADODB.Recordset
Dim S1 As String
form_load()
S1 = "Provider=Microsoft.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, adLockOptimistic


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

Set DataReport2.DataSource = M2
DataReport2.WindowState = vbMaximized
DataReport2.Show 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, adLockOptimistic[/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, adLockOptimistic[/HTML]

and kindly ensure that you do have datas for that particular date.
Apr 24 '07 #14

palsonjj
P: 18
Replace the code

[HTML]M2.Open "select * from tblSales1 where InvDate>=#" & DTPicker1.Value & "# and InvDate <=# " & DTPicker2.Value & "# order by InvDate",Mycon, adOpenDynamic, adLockOptimistic[/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, adLockOptimistic[/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 .... hoooooooorrrrrrrrrrrrraaaaaaaaaaaaaaayyyyyy...
Apr 24 '07 #15

Dököll
Expert 100+
P: 2,364
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 .... hoooooooorrrrrrrrrrrrraaaaaaaaaaaaaaayyyyyy...
Greetings, palsonjj!

Looks like I got your screen name incorrectly there, my apologies.

Good job cmrhema...
Apr 26 '07 #16

palsonjj
P: 18
Greetings, palsonjj!

Looks like I got your screen name incorrectly there, my apologies.

Good job cmrhema...

hello Sir,

Thnku .. thn, i'm not goin 2 leave tat way of displaying reports... so CMRHema, plz lead me 2 do tat... thnks in advance
Apr 26 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.