?
Solved

Need to make a simple 1 page invoice using Crystal Reports/VB6

Posted on 2003-02-28
2
Medium Priority
?
378 Views
Last Modified: 2010-04-07
I need to make a one page invoice.  First of all is Crystal Reports part of Visual Basic 6?  Secondly I tried creating this report in Visual Basic alone but Visual Basic has limitations for this sort of thing.  Can someone show me how to make this small one page invoice in crystal reports within VB6?  The following is my code for frmInvoice.  

Option Explicit
Dim adoInvoice As New ADODB.Connection
Dim rsInvoice As New ADODB.Recordset
Dim adoCmdInvoice As New ADODB.Command
Dim strConn As String
Dim strSQL As String
Dim str As String

Private Sub cmdCancel_Click()
cmdPrint.Enabled = True
cmdDelete.Enabled = True
cmdClose.Enabled = True
lblRed.Visible = False
cmdSubmit.Visible = False
cmdCancel.Visible = False
cmdMakeChanges.Enabled = True
txtStarting.Locked = True
txtStarting.BorderStyle = vbBSNone
txtEnding.Locked = True
txtEnding.BorderStyle = vbBSNone
txtHours.Locked = True
txtHours.BorderStyle = vbBSNone
txtExpenses.Locked = True
txtExpenses.BorderStyle = vbBSNone
End Sub

Private Sub cmdClose_Click()
frmModify.Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.Adodc1.CommandType = adCmdText
frmModify.Adodc1.RecordSource = "Select DirectReviewClaimNo, DateofReferral, referredby.Company, adjuster, FileNo, assignedto.Nurse, DateOfLoss, Type, SpecialInstructions, ClaimantsFirstName, ClaimantsLastName, ClaimantsAddress1, ClaimantsAddress2, City, State, ZipCode, Phone, Status, DateofBirth, SocialSecurity, ProgressReport1, ProgressReport2, ProgressReport3, ProgressReport4, ProgressReport5, ProgressReport6 from Claim, Type, ReferredBy, AssignedTo where claim.typeid=type.typeid and claim.assignedtoid=assignedto.assignedtoid and claim.referredbyid=referredby.referredbyid and directreviewclaimno='" & txtDirectReviewClaimNo.Text & "'"

Set frmModify.txtClaimNumber.DataSource = frmModify.Adodc1
frmModify.txtClaimNumber.DataField = "DirectReviewClaimNo"
Set frmModify.txtReferralDate.DataSource = frmModify.Adodc1
frmModify.txtReferralDate.DataField = "DateofReferral"
Set frmModify.txtAdjuster.DataSource = frmModify.Adodc1
frmModify.txtAdjuster.DataField = "Adjuster"
Set frmModify.cmbAssignedTo.DataSource = frmModify.Adodc1
frmModify.cmbAssignedTo.DataField = "Nurse"
Set frmModify.cmbType.DataSource = frmModify.Adodc1
frmModify.cmbType.DataField = "Type"
Set frmModify.txtSpecial.DataSource = frmModify.Adodc1
frmModify.txtSpecial.DataField = "SpecialInstructions"
Set frmModify.cmbStatus.DataSource = frmModify.Adodc1
frmModify.cmbStatus.DataField = "Status"
Set frmModify.cmbReferredBy.DataSource = frmModify.Adodc1
frmModify.cmbReferredBy.DataField = "Company"
Set frmModify.txtFileNumber.DataSource = frmModify.Adodc1
frmModify.txtFileNumber.DataField = "FileNo"
Set frmModify.txtDOL.DataSource = frmModify.Adodc1
frmModify.txtDOL.DataField = "DateofLoss"
Set frmModify.txtFirstName.DataSource = frmModify.Adodc1
frmModify.txtFirstName.DataField = "ClaimantsFirstName"
Set frmModify.txtLastName.DataSource = frmModify.Adodc1
frmModify.txtLastName.DataField = "ClaimantsLastName"
Set frmModify.txtAddress1.DataSource = frmModify.Adodc1
frmModify.txtAddress1.DataField = "ClaimantsAddress1"
Set frmModify.txtAddress2.DataSource = frmModify.Adodc1
frmModify.txtAddress2.DataField = "ClaimantsAddress2"
Set frmModify.txtCity.DataSource = frmModify.Adodc1
frmModify.txtCity.DataField = "City"
Set frmModify.txtState.DataSource = frmModify.Adodc1
frmModify.txtState.DataField = "State"
Set frmModify.txtZipCode.DataSource = frmModify.Adodc1
frmModify.txtZipCode.DataField = "ZipCode"
Set frmModify.txtPhone.DataSource = frmModify.Adodc1
frmModify.txtPhone.DataField = "Phone"
Set frmModify.txtBirth.DataSource = frmModify.Adodc1
frmModify.txtBirth.DataField = "DateofBirth"
Set frmModify.txtSocial.DataSource = frmModify.Adodc1
frmModify.txtSocial.DataField = "SocialSecurity"
Set frmModify.txtProgress1.DataSource = frmModify.Adodc1
frmModify.txtProgress1.DataField = "ProgressReport1"
Set frmModify.txtProgress2.DataSource = frmModify.Adodc1
frmModify.txtProgress2.DataField = "ProgressReport2"
Set frmModify.txtProgress3.DataSource = frmModify.Adodc1
frmModify.txtProgress3.DataField = "ProgressReport3"
Set frmModify.txtProgress4.DataSource = frmModify.Adodc1
frmModify.txtProgress4.DataField = "ProgressReport4"
Set frmModify.txtProgress5.DataSource = frmModify.Adodc1
frmModify.txtProgress5.DataField = "ProgressReport5"
Set frmModify.txtProgress6.DataSource = frmModify.Adodc1
frmModify.txtProgress6.DataField = "ProgressReport6"


frmModify.adoInvoice.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.adoInvoice.CommandType = adCmdText
frmModify.adoInvoice.RecordSource = "select invoiceid, invoicedate, totalamount from Invoice, claim where invoice.directreviewclaimno=claim.directreviewclaimno and invoice.directreviewclaimno = '" & frmModify.txtClaimNumber.Text & "'" & " order by invoicedate desc"
frmModify.adoInvoice.Refresh

frmModify.adoInvoice.Refresh
Set adoInvoice = Nothing
Set rsInvoice = Nothing
Set adoCmdInvoice = Nothing
Unload Me
frmModify.WindowState = vbMaximized
frmModify.Show
End Sub

Private Sub cmdDelete_Click()
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
adoInvoice.Open strConn
rsInvoice.ActiveConnection = adoInvoice
If MsgBox("Are you sure you want to delete this claim?", vbOKCancel, "Modifying Claim") = vbCancel Then
adoInvoice.Close
Exit Sub
Else
strSQL = "DELETE from Invoice where InvoiceID='" & txtInvoiceID.Text & "'"

    adoCmdInvoice.CommandText = strSQL
    adoCmdInvoice.CommandType = adCmdText
    adoCmdInvoice.ActiveConnection = adoInvoice
    adoCmdInvoice.Execute
   

adoInvoice.Close
Set adoInvoice = Nothing
Set rsInvoice = Nothing
Set adoCmdInvoice = Nothing
frmModify.Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.Adodc1.CommandType = adCmdText
frmModify.Adodc1.RecordSource = "Select DirectReviewClaimNo, DateofReferral, referredby.Company, adjuster, FileNo, assignedto.Nurse, DateOfLoss, Type, SpecialInstructions, ClaimantsFirstName, ClaimantsLastName, ClaimantsAddress1, ClaimantsAddress2, City, State, ZipCode, Phone, Status, DateofBirth, SocialSecurity, ProgressReport1, ProgressReport2, ProgressReport3, ProgressReport4, ProgressReport5, ProgressReport6 from Claim, Type, ReferredBy, AssignedTo where claim.typeid=type.typeid and claim.assignedtoid=assignedto.assignedtoid and claim.referredbyid=referredby.referredbyid and directreviewclaimno='" & txtDirectReviewClaimNo.Text & "'"

Set frmModify.txtClaimNumber.DataSource = frmModify.Adodc1
frmModify.txtClaimNumber.DataField = "DirectReviewClaimNo"
Set frmModify.txtReferralDate.DataSource = frmModify.Adodc1
frmModify.txtReferralDate.DataField = "DateofReferral"
Set frmModify.txtAdjuster.DataSource = frmModify.Adodc1
frmModify.txtAdjuster.DataField = "Adjuster"
Set frmModify.cmbAssignedTo.DataSource = frmModify.Adodc1
frmModify.cmbAssignedTo.DataField = "Nurse"
Set frmModify.cmbType.DataSource = frmModify.Adodc1
frmModify.cmbType.DataField = "Type"
Set frmModify.txtSpecial.DataSource = frmModify.Adodc1
frmModify.txtSpecial.DataField = "SpecialInstructions"
Set frmModify.cmbStatus.DataSource = frmModify.Adodc1
frmModify.cmbStatus.DataField = "Status"
Set frmModify.cmbReferredBy.DataSource = frmModify.Adodc1
frmModify.cmbReferredBy.DataField = "Company"
Set frmModify.txtFileNumber.DataSource = frmModify.Adodc1
frmModify.txtFileNumber.DataField = "FileNo"
Set frmModify.txtDOL.DataSource = frmModify.Adodc1
frmModify.txtDOL.DataField = "DateofLoss"
Set frmModify.txtFirstName.DataSource = frmModify.Adodc1
frmModify.txtFirstName.DataField = "ClaimantsFirstName"
Set frmModify.txtLastName.DataSource = frmModify.Adodc1
frmModify.txtLastName.DataField = "ClaimantsLastName"
Set frmModify.txtAddress1.DataSource = frmModify.Adodc1
frmModify.txtAddress1.DataField = "ClaimantsAddress1"
Set frmModify.txtAddress2.DataSource = frmModify.Adodc1
frmModify.txtAddress2.DataField = "ClaimantsAddress2"
Set frmModify.txtCity.DataSource = frmModify.Adodc1
frmModify.txtCity.DataField = "City"
Set frmModify.txtState.DataSource = frmModify.Adodc1
frmModify.txtState.DataField = "State"
Set frmModify.txtZipCode.DataSource = frmModify.Adodc1
frmModify.txtZipCode.DataField = "ZipCode"
Set frmModify.txtPhone.DataSource = frmModify.Adodc1
frmModify.txtPhone.DataField = "Phone"
Set frmModify.txtBirth.DataSource = frmModify.Adodc1
frmModify.txtBirth.DataField = "DateofBirth"
Set frmModify.txtSocial.DataSource = frmModify.Adodc1
frmModify.txtSocial.DataField = "SocialSecurity"
Set frmModify.txtProgress1.DataSource = frmModify.Adodc1
frmModify.txtProgress1.DataField = "ProgressReport1"
Set frmModify.txtProgress2.DataSource = frmModify.Adodc1
frmModify.txtProgress2.DataField = "ProgressReport2"
Set frmModify.txtProgress3.DataSource = frmModify.Adodc1
frmModify.txtProgress3.DataField = "ProgressReport3"
Set frmModify.txtProgress4.DataSource = frmModify.Adodc1
frmModify.txtProgress4.DataField = "ProgressReport4"
Set frmModify.txtProgress5.DataSource = frmModify.Adodc1
frmModify.txtProgress5.DataField = "ProgressReport5"
Set frmModify.txtProgress6.DataSource = frmModify.Adodc1
frmModify.txtProgress6.DataField = "ProgressReport6"


frmModify.adoInvoice.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.adoInvoice.CommandType = adCmdText
frmModify.adoInvoice.RecordSource = "select invoiceid, invoicedate, totalamount from Invoice, claim where invoice.directreviewclaimno=claim.directreviewclaimno and invoice.directreviewclaimno = '" & frmModify.txtClaimNumber.Text & "'" & " order by invoicedate desc"
frmModify.adoInvoice.Refresh

frmModify.adoInvoice.Refresh
Set adoInvoice = Nothing
Set rsInvoice = Nothing
Set adoCmdInvoice = Nothing
Unload Me
frmModify.WindowState = vbMaximized
frmModify.Show
End If
End Sub

Private Sub cmdMakeChanges_Click()
cmdPrint.Enabled = False
cmdDelete.Enabled = False
cmdClose.Enabled = False
cmdMakeChanges.Enabled = False
lblRed.Visible = True
cmdSubmit.Visible = True
cmdCancel.Visible = True
cmbExpensesDetail.Locked = False
txtExpenses.Locked = False
txtStarting.Locked = False
txtStarting.BorderStyle = vbFixedSingle
txtEnding.Locked = False
txtEnding.BorderStyle = vbFixedSingle
txtHours.Locked = False
txtHours.BorderStyle = vbFixedSingle
txtExpenses.BorderStyle = vbFixedSingle
txtStarting.TabStop = True
txtEnding.TabStop = True
cmbExpensesDetail.TabStop = True
txtExpenses.TabStop = True
txtHours.TabStop = True
End Sub

Private Sub cmdPrint_Click()
cmdPrint.Visible = False
cmdMakeChanges.Visible = False
cmdDelete.Visible = False
cmdClose.Visible = False
txtExpensesDetail.Text = cmbExpensesDetail.Text
cmbExpensesDetail.Visible = False
txtExpensesDetail.Visible = True
txtExpensesDetail.Left = 4655
txtExpensesDetail.Top = 6915
Me.PrintForm
cmdPrint.Visible = True
cmdMakeChanges.Visible = True
cmdDelete.Visible = True
cmdClose.Visible = True
cmbExpensesDetail.Visible = True
txtExpensesDetail.Visible = False
End Sub

Private Sub cmdSubmit_Click()
Dim admin As Single
Dim subtotal As Single
Dim total As Single
Dim hours As Integer
Dim rates As Integer
Dim expenses As Single
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
adoInvoice.Open strConn
rsInvoice.ActiveConnection = adoInvoice

If txtCompany.Text = "JUA/MTF @ Riverside" & vbCrLf & "P.O. Box 7002" & vbCrLf & "Riverside, NJ 08075" Then
lblAdmin.Visible = True
txtAdmin.Visible = True
lblequals.Visible = True
admin = 10
txtAdmin.Text = admin
End If

txtRateTwo.Text = txtRate.Text
If txtHours.Text = "" Then
txtHours.Text = 0
End If
subtotal = txtHours.Text * txtRate.Text
expenses = txtExpenses.Text
txtSubTotal.Text = subtotal
total = subtotal + expenses + admin
txtTotal.Text = total
txtSubTotal.Text = Format(txtSubTotal.Text, "Currency")
txtExpenses.Text = Format(txtExpenses.Text, "Currency")
txtAdmin.Text = Format(txtAdmin.Text, "Currency")
txtTotal.Text = Format(txtTotal.Text, "Currency")

If MsgBox("Are you sure you want to make these changes to the invoice?", vbOKCancel, "Modifying Claim") = vbOK Then
strSQL = "UPDATE Invoice SET BillingPeriodStart='" & txtStarting.Text & "',"
strSQL = strSQL & "BillingPeriodEnd= '" & txtEnding.Text & "',"
strSQL = strSQL & "TotalHours = '" & txtHours.Text & "',"
strSQL = strSQL & "ExpensesDetail = '" & cmbExpensesDetail.Text & "',"
strSQL = strSQL & "TotalExpenses = '" & txtExpenses.Text & "',"
strSQL = strSQL & "TotalAmount = '" & txtTotal.Text & "'"
strSQL = strSQL & "WHERE InvoiceID='" & txtInvoiceID.Text & "'"

    adoCmdInvoice.CommandText = strSQL
    adoCmdInvoice.CommandType = adCmdText
    adoCmdInvoice.ActiveConnection = adoInvoice
    adoCmdInvoice.Execute
    End If
cmdPrint.Enabled = True
cmdDelete.Enabled = True
cmdClose.Enabled = True
cmdCancel.Visible = False
lblRed.Visible = False
cmdSubmit.Visible = False
txtAdmin.Visible = False
lblequals.Visible = False
cmdMakeChanges.Enabled = True

txtStarting.Locked = True
txtStarting.BorderStyle = vbBSNone
txtEnding.BorderStyle = vbBSNone
txtHours.BorderStyle = vbBSNone
txtEnding.Locked = True
txtHours.Locked = True
txtExpenses.Locked = True
txtExpenses.BorderStyle = vbBSNone
txtStarting.TabStop = False
txtEnding.TabStop = False
cmbExpensesDetail.TabStop = False
txtExpenses.TabStop = False
txtHours.TabStop = False
adoInvoice.Close
Set adoInvoice = Nothing
Set rsInvoice = Nothing
Set adoCmdInvoice = Nothing
End Sub

Private Sub Form_Load()
cmbExpensesDetail.AddItem "Phone/Fax/Correspondence"
cmbExpensesDetail.AddItem "Phone/Fax/Correspondence/Travel"
txtHours.MaxLength = 3
txtExpenses.MaxLength = 6
End Sub

Private Sub txtExpenses_KeyPress(KeyAscii As Integer)
Dim subtotal As Single
Dim total As Single
Dim admin As Single
Dim expenses As Single
If KeyAscii <> 46 And KeyAscii <> 13 And (KeyAscii < Asc("0") Or KeyAscii > Asc("9")) Then
KeyAscii = 0
Else
If KeyAscii = 13 Then
    If txtExpenses.Text = "" Then
    txtExpenses.Text = 0
    End If
expenses = txtExpenses.Text
If txtCompany.Text = "JUA/MTF @ Riverside" & vbCrLf & "P.O. Box 7002" & vbCrLf & "Riverside, NJ 08075" Then
lblAdmin.Visible = True
txtAdmin.Visible = True
admin = 10
txtAdmin.Text = admin
End If
txtRateTwo.Text = txtRate.Text
If txtHours.Text = "" Then
txtHours.Text = 0
End If
subtotal = txtHours.Text * txtRate.Text
txtSubTotal.Text = subtotal
total = expenses + subtotal + admin
txtTotal.Text = total
txtSubTotal.Text = Format(txtSubTotal.Text, "Currency")
txtExpenses.Text = Format(txtExpenses.Text, "Currency")
txtAdmin.Text = Format(txtAdmin.Text, "Currency")
txtTotal.Text = Format(txtTotal.Text, "Currency")
End If
End If
End Sub

Private Sub txtHours_KeyPress(KeyAscii As Integer)
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then KeyAscii = 0
End Sub
0
Comment
Question by:jerseyguy29
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8045511
0
 
LVL 1

Accepted Solution

by:
sanya2 earned 360 total points
ID: 8046043
You can do like this:
1. Create temporary table for Invoice (tempInvoice)
2. Create Crystal Report file for tempInvoice
3. Run query to insert the Invoice record that you want to print into tempInvoice
4. Using code like this:
    Crpt.ReportFileName = "Your Report FileName"
' use this statement if you want sort your record
    Crpt.SortFields(0) = "+{tempInvoice.sortfield}
'Choice you print destination
    If ToScreen Then
       Crpt.WindowTop = 0
       Crpt.WindowLeft = 0
       Crpt.WindowWidth = Me.Width - 2000
       Crpt.WindowHeight = Me.Height - 2000
       Crpt.Destination = 0

    ElseIf ToPrinter Then
       Crpt.Destination = 1
'You can see all print filetype in crystal report
'help reference
    ElseIf ToAppl then
            Crpt.Destination = 2
            Crpt.PrintFileType = crptExcel50
            Crpt.PrintFileName = "your filename destination"
    End If
    Crpt.Action = 1
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

765 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