jerseyguy29
asked on
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.Connectio nString = "Provider=Microsoft.Jet.OL EDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.Adodc1.CommandTy pe = adCmdText
frmModify.Adodc1.RecordSou rce = "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=assigne dto.assign edtoid and claim.referredbyid=referre dby.referr edbyid and directreviewclaimno='" & txtDirectReviewClaimNo.Tex t & "'"
Set frmModify.txtClaimNumber.D ataSource = frmModify.Adodc1
frmModify.txtClaimNumber.D ataField = "DirectReviewClaimNo"
Set frmModify.txtReferralDate. DataSource = frmModify.Adodc1
frmModify.txtReferralDate. DataField = "DateofReferral"
Set frmModify.txtAdjuster.Data Source = frmModify.Adodc1
frmModify.txtAdjuster.Data Field = "Adjuster"
Set frmModify.cmbAssignedTo.Da taSource = frmModify.Adodc1
frmModify.cmbAssignedTo.Da taField = "Nurse"
Set frmModify.cmbType.DataSour ce = frmModify.Adodc1
frmModify.cmbType.DataFiel d = "Type"
Set frmModify.txtSpecial.DataS ource = frmModify.Adodc1
frmModify.txtSpecial.DataF ield = "SpecialInstructions"
Set frmModify.cmbStatus.DataSo urce = frmModify.Adodc1
frmModify.cmbStatus.DataFi eld = "Status"
Set frmModify.cmbReferredBy.Da taSource = frmModify.Adodc1
frmModify.cmbReferredBy.Da taField = "Company"
Set frmModify.txtFileNumber.Da taSource = frmModify.Adodc1
frmModify.txtFileNumber.Da taField = "FileNo"
Set frmModify.txtDOL.DataSourc e = frmModify.Adodc1
frmModify.txtDOL.DataField = "DateofLoss"
Set frmModify.txtFirstName.Dat aSource = frmModify.Adodc1
frmModify.txtFirstName.Dat aField = "ClaimantsFirstName"
Set frmModify.txtLastName.Data Source = frmModify.Adodc1
frmModify.txtLastName.Data Field = "ClaimantsLastName"
Set frmModify.txtAddress1.Data Source = frmModify.Adodc1
frmModify.txtAddress1.Data Field = "ClaimantsAddress1"
Set frmModify.txtAddress2.Data Source = frmModify.Adodc1
frmModify.txtAddress2.Data Field = "ClaimantsAddress2"
Set frmModify.txtCity.DataSour ce = frmModify.Adodc1
frmModify.txtCity.DataFiel d = "City"
Set frmModify.txtState.DataSou rce = frmModify.Adodc1
frmModify.txtState.DataFie ld = "State"
Set frmModify.txtZipCode.DataS ource = frmModify.Adodc1
frmModify.txtZipCode.DataF ield = "ZipCode"
Set frmModify.txtPhone.DataSou rce = frmModify.Adodc1
frmModify.txtPhone.DataFie ld = "Phone"
Set frmModify.txtBirth.DataSou rce = frmModify.Adodc1
frmModify.txtBirth.DataFie ld = "DateofBirth"
Set frmModify.txtSocial.DataSo urce = frmModify.Adodc1
frmModify.txtSocial.DataFi eld = "SocialSecurity"
Set frmModify.txtProgress1.Dat aSource = frmModify.Adodc1
frmModify.txtProgress1.Dat aField = "ProgressReport1"
Set frmModify.txtProgress2.Dat aSource = frmModify.Adodc1
frmModify.txtProgress2.Dat aField = "ProgressReport2"
Set frmModify.txtProgress3.Dat aSource = frmModify.Adodc1
frmModify.txtProgress3.Dat aField = "ProgressReport3"
Set frmModify.txtProgress4.Dat aSource = frmModify.Adodc1
frmModify.txtProgress4.Dat aField = "ProgressReport4"
Set frmModify.txtProgress5.Dat aSource = frmModify.Adodc1
frmModify.txtProgress5.Dat aField = "ProgressReport5"
Set frmModify.txtProgress6.Dat aSource = frmModify.Adodc1
frmModify.txtProgress6.Dat aField = "ProgressReport6"
frmModify.adoInvoice.Conne ctionStrin g = "Provider=Microsoft.Jet.OL EDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.adoInvoice.Comma ndType = adCmdText
frmModify.adoInvoice.Recor dSource = "select invoiceid, invoicedate, totalamount from Invoice, claim where invoice.directreviewclaimn o=claim.di rectreview claimno and invoice.directreviewclaimn o = '" & frmModify.txtClaimNumber.T ext & "'" & " order by invoicedate desc"
frmModify.adoInvoice.Refre sh
frmModify.adoInvoice.Refre sh
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.OL EDB.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.ActiveConnec tion = adoInvoice
adoCmdInvoice.Execute
adoInvoice.Close
Set adoInvoice = Nothing
Set rsInvoice = Nothing
Set adoCmdInvoice = Nothing
frmModify.Adodc1.Connectio nString = "Provider=Microsoft.Jet.OL EDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.Adodc1.CommandTy pe = adCmdText
frmModify.Adodc1.RecordSou rce = "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=assigne dto.assign edtoid and claim.referredbyid=referre dby.referr edbyid and directreviewclaimno='" & txtDirectReviewClaimNo.Tex t & "'"
Set frmModify.txtClaimNumber.D ataSource = frmModify.Adodc1
frmModify.txtClaimNumber.D ataField = "DirectReviewClaimNo"
Set frmModify.txtReferralDate. DataSource = frmModify.Adodc1
frmModify.txtReferralDate. DataField = "DateofReferral"
Set frmModify.txtAdjuster.Data Source = frmModify.Adodc1
frmModify.txtAdjuster.Data Field = "Adjuster"
Set frmModify.cmbAssignedTo.Da taSource = frmModify.Adodc1
frmModify.cmbAssignedTo.Da taField = "Nurse"
Set frmModify.cmbType.DataSour ce = frmModify.Adodc1
frmModify.cmbType.DataFiel d = "Type"
Set frmModify.txtSpecial.DataS ource = frmModify.Adodc1
frmModify.txtSpecial.DataF ield = "SpecialInstructions"
Set frmModify.cmbStatus.DataSo urce = frmModify.Adodc1
frmModify.cmbStatus.DataFi eld = "Status"
Set frmModify.cmbReferredBy.Da taSource = frmModify.Adodc1
frmModify.cmbReferredBy.Da taField = "Company"
Set frmModify.txtFileNumber.Da taSource = frmModify.Adodc1
frmModify.txtFileNumber.Da taField = "FileNo"
Set frmModify.txtDOL.DataSourc e = frmModify.Adodc1
frmModify.txtDOL.DataField = "DateofLoss"
Set frmModify.txtFirstName.Dat aSource = frmModify.Adodc1
frmModify.txtFirstName.Dat aField = "ClaimantsFirstName"
Set frmModify.txtLastName.Data Source = frmModify.Adodc1
frmModify.txtLastName.Data Field = "ClaimantsLastName"
Set frmModify.txtAddress1.Data Source = frmModify.Adodc1
frmModify.txtAddress1.Data Field = "ClaimantsAddress1"
Set frmModify.txtAddress2.Data Source = frmModify.Adodc1
frmModify.txtAddress2.Data Field = "ClaimantsAddress2"
Set frmModify.txtCity.DataSour ce = frmModify.Adodc1
frmModify.txtCity.DataFiel d = "City"
Set frmModify.txtState.DataSou rce = frmModify.Adodc1
frmModify.txtState.DataFie ld = "State"
Set frmModify.txtZipCode.DataS ource = frmModify.Adodc1
frmModify.txtZipCode.DataF ield = "ZipCode"
Set frmModify.txtPhone.DataSou rce = frmModify.Adodc1
frmModify.txtPhone.DataFie ld = "Phone"
Set frmModify.txtBirth.DataSou rce = frmModify.Adodc1
frmModify.txtBirth.DataFie ld = "DateofBirth"
Set frmModify.txtSocial.DataSo urce = frmModify.Adodc1
frmModify.txtSocial.DataFi eld = "SocialSecurity"
Set frmModify.txtProgress1.Dat aSource = frmModify.Adodc1
frmModify.txtProgress1.Dat aField = "ProgressReport1"
Set frmModify.txtProgress2.Dat aSource = frmModify.Adodc1
frmModify.txtProgress2.Dat aField = "ProgressReport2"
Set frmModify.txtProgress3.Dat aSource = frmModify.Adodc1
frmModify.txtProgress3.Dat aField = "ProgressReport3"
Set frmModify.txtProgress4.Dat aSource = frmModify.Adodc1
frmModify.txtProgress4.Dat aField = "ProgressReport4"
Set frmModify.txtProgress5.Dat aSource = frmModify.Adodc1
frmModify.txtProgress5.Dat aField = "ProgressReport5"
Set frmModify.txtProgress6.Dat aSource = frmModify.Adodc1
frmModify.txtProgress6.Dat aField = "ProgressReport6"
frmModify.adoInvoice.Conne ctionStrin g = "Provider=Microsoft.Jet.OL EDB.4.0; Data Source=c:\VISUAL BASIC\Direct Review Project\DirectReview.mdb"
frmModify.adoInvoice.Comma ndType = adCmdText
frmModify.adoInvoice.Recor dSource = "select invoiceid, invoicedate, totalamount from Invoice, claim where invoice.directreviewclaimn o=claim.di rectreview claimno and invoice.directreviewclaimn o = '" & frmModify.txtClaimNumber.T ext & "'" & " order by invoicedate desc"
frmModify.adoInvoice.Refre sh
frmModify.adoInvoice.Refre sh
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.OL EDB.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.ActiveConnec tion = 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(KeyAs cii 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
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.Connectio
frmModify.Adodc1.CommandTy
frmModify.Adodc1.RecordSou
Set frmModify.txtClaimNumber.D
frmModify.txtClaimNumber.D
Set frmModify.txtReferralDate.
frmModify.txtReferralDate.
Set frmModify.txtAdjuster.Data
frmModify.txtAdjuster.Data
Set frmModify.cmbAssignedTo.Da
frmModify.cmbAssignedTo.Da
Set frmModify.cmbType.DataSour
frmModify.cmbType.DataFiel
Set frmModify.txtSpecial.DataS
frmModify.txtSpecial.DataF
Set frmModify.cmbStatus.DataSo
frmModify.cmbStatus.DataFi
Set frmModify.cmbReferredBy.Da
frmModify.cmbReferredBy.Da
Set frmModify.txtFileNumber.Da
frmModify.txtFileNumber.Da
Set frmModify.txtDOL.DataSourc
frmModify.txtDOL.DataField
Set frmModify.txtFirstName.Dat
frmModify.txtFirstName.Dat
Set frmModify.txtLastName.Data
frmModify.txtLastName.Data
Set frmModify.txtAddress1.Data
frmModify.txtAddress1.Data
Set frmModify.txtAddress2.Data
frmModify.txtAddress2.Data
Set frmModify.txtCity.DataSour
frmModify.txtCity.DataFiel
Set frmModify.txtState.DataSou
frmModify.txtState.DataFie
Set frmModify.txtZipCode.DataS
frmModify.txtZipCode.DataF
Set frmModify.txtPhone.DataSou
frmModify.txtPhone.DataFie
Set frmModify.txtBirth.DataSou
frmModify.txtBirth.DataFie
Set frmModify.txtSocial.DataSo
frmModify.txtSocial.DataFi
Set frmModify.txtProgress1.Dat
frmModify.txtProgress1.Dat
Set frmModify.txtProgress2.Dat
frmModify.txtProgress2.Dat
Set frmModify.txtProgress3.Dat
frmModify.txtProgress3.Dat
Set frmModify.txtProgress4.Dat
frmModify.txtProgress4.Dat
Set frmModify.txtProgress5.Dat
frmModify.txtProgress5.Dat
Set frmModify.txtProgress6.Dat
frmModify.txtProgress6.Dat
frmModify.adoInvoice.Conne
frmModify.adoInvoice.Comma
frmModify.adoInvoice.Recor
frmModify.adoInvoice.Refre
frmModify.adoInvoice.Refre
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.OL
adoInvoice.Open strConn
rsInvoice.ActiveConnection
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.ActiveConnec
adoCmdInvoice.Execute
adoInvoice.Close
Set adoInvoice = Nothing
Set rsInvoice = Nothing
Set adoCmdInvoice = Nothing
frmModify.Adodc1.Connectio
frmModify.Adodc1.CommandTy
frmModify.Adodc1.RecordSou
Set frmModify.txtClaimNumber.D
frmModify.txtClaimNumber.D
Set frmModify.txtReferralDate.
frmModify.txtReferralDate.
Set frmModify.txtAdjuster.Data
frmModify.txtAdjuster.Data
Set frmModify.cmbAssignedTo.Da
frmModify.cmbAssignedTo.Da
Set frmModify.cmbType.DataSour
frmModify.cmbType.DataFiel
Set frmModify.txtSpecial.DataS
frmModify.txtSpecial.DataF
Set frmModify.cmbStatus.DataSo
frmModify.cmbStatus.DataFi
Set frmModify.cmbReferredBy.Da
frmModify.cmbReferredBy.Da
Set frmModify.txtFileNumber.Da
frmModify.txtFileNumber.Da
Set frmModify.txtDOL.DataSourc
frmModify.txtDOL.DataField
Set frmModify.txtFirstName.Dat
frmModify.txtFirstName.Dat
Set frmModify.txtLastName.Data
frmModify.txtLastName.Data
Set frmModify.txtAddress1.Data
frmModify.txtAddress1.Data
Set frmModify.txtAddress2.Data
frmModify.txtAddress2.Data
Set frmModify.txtCity.DataSour
frmModify.txtCity.DataFiel
Set frmModify.txtState.DataSou
frmModify.txtState.DataFie
Set frmModify.txtZipCode.DataS
frmModify.txtZipCode.DataF
Set frmModify.txtPhone.DataSou
frmModify.txtPhone.DataFie
Set frmModify.txtBirth.DataSou
frmModify.txtBirth.DataFie
Set frmModify.txtSocial.DataSo
frmModify.txtSocial.DataFi
Set frmModify.txtProgress1.Dat
frmModify.txtProgress1.Dat
Set frmModify.txtProgress2.Dat
frmModify.txtProgress2.Dat
Set frmModify.txtProgress3.Dat
frmModify.txtProgress3.Dat
Set frmModify.txtProgress4.Dat
frmModify.txtProgress4.Dat
Set frmModify.txtProgress5.Dat
frmModify.txtProgress5.Dat
Set frmModify.txtProgress6.Dat
frmModify.txtProgress6.Dat
frmModify.adoInvoice.Conne
frmModify.adoInvoice.Comma
frmModify.adoInvoice.Recor
frmModify.adoInvoice.Refre
frmModify.adoInvoice.Refre
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.OL
adoInvoice.Open strConn
rsInvoice.ActiveConnection
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.ActiveConnec
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/
txtHours.MaxLength = 3
txtExpenses.MaxLength = 6
End Sub
Private Sub txtExpenses_KeyPress(KeyAs
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
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then KeyAscii = 0
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/questions/20513250/Can-someone-convert-these-3-stored-procedures-into-3-plain-old-SQL-statements.html
Anthony