Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

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

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
jerseyguy29
Asked:
jerseyguy29
1 Solution
 
Anthony PerkinsCommented:
0
 
sanya2Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now