You are here:

Visual Basic/vb6.0+ms access

Advertisement


Question
QUESTION: Respected Sir,
I am developing an application in VB6.0 and Access.In that I have a doubt in working with data report.What sql query I should give  in data environmentt to combine two tables [where billno is a common field in both the tables and in one table bill no is
repeating in that table it stores details of all the items sold from a shop in the other table only the total amount, billno and billdate is stored] .I have tried the follwing query
select billtable.Billno,billtable.billdate,billtable.total,billtable.vat,billtable.final ,completebill.itemname, completebill.rate,completebill.quantity,completebill.total from billtable equi join completebill where billtable.Billno=completebill.Billno
but the billno is repeating in the datareport .How will i generate a sales bill.If you can help me i would be grateful to you sir
thank you

ANSWER: put a dataenvironment on form and point it to your database

add a command
it its sql properties

in the textbox enter
Select * from testable where itemNo=?

'where testable is your table and itemNo is the fieldname

add a datareport and drag all the fields of the command to the report designer.

to the form add a textbox and a commandbuttong and add this code:

Option Explicit

Private Sub Command1_Click()
    With DataEnvironment1

        .Command1 Text1

         DataReport1.Show
     
    End With
End Sub

'enter a value for the field  in the textbox and click the commandbutton

'this could be an item from listbox.
if field is number maybe you add val(text1) or ascribe it to a variable, convert it and use in command.
do some checking out. it works fine

---------- FOLLOW-UP ----------

QUESTION: Sir,
Thanks for u r reply. I have followed your method but it works for only bills which have one item. In case if a bill has multiple items the billno will be repeating in the table and in the report also it is repeating. what can I do to avoid repetition of bill no and billdate in data report.
Thanks

Answer
this can be done only if the bill no and date fields are left blank for the second and further items in the invoice. ie   bill no   date          item
         1       1/1/          spanner
         2        2/1          Tape
         Gum
         Stapler
         3        2/1          oil

and in data report drag the header to the page header. then you get the format you want.but if you enter the dates and bill nos they need to be repeated. maybe some method exists

Visual Basic

All Answers


Answers by Expert:


Ask Experts

Volunteer


Ravindra

Expertise

visual basic application programming from design to access information, sql, engineering and commercial applications. access databases, excel.(VB6) basic questions in vb 2008. Optional: 1)a large number of people want me to do work which takes some time and effort. can do projects in vb 2008. Pl.note that i would like to be paid for such work. 2) if you want me to spend quality time and do special work, i expect to be paid a reasonable price for my time. 3) if you are pleased with my reply you could consider a good amount. 4) you can visit my website http://ravindra.coolpage.biz

Experience

programming since the past 15 years. have completed a large number of projects in Industrial applications, finance,accounts, correspondence. excel macros etc

Education/Credentials
engineering, management degrees.

Past/Present Clients
project work for a Norway company,USA company and a Canadian company completed. Freelance Project work and Teaching. helping programmers with their work
teaching vb

©2016 About.com. All rights reserved.