VERY VERY nice automation sample !
No it's not me who code it, I just found it on MSDN forum posted by JS06
so this is how he posted it...
This is setup to work with tables that are related by foreign key
This particular setup has 3 tables that have foreign keys in a chain
table 1 (patient table) - patientID
\
foreign key
\
Table 2 (visit table) - PatientID and - visitID
\
Foreign key
\
Table 3 (charges table) - VisitID
The way this works is to use the automatic filtering in the datagridviews when you click on a patient record
If you have all three tables on a form then they will filter automatically for you
Make sure that you are using the tables that are all under the same parent table in your data sources explorer
They have to be those tables otherwise they will not filter properly
To see this you can expand all the tables in the explorer, then you will see the relations that are setup.
The following code automates the datagridview with the movenext() method and gets the values in the current row
Then it add the values to the current selected row in the spreadsheet
The automation of the spreadsheet is with the offset() method which moves the activecell to the next row
It also selects the entire row to create a current row instance (this is necessary to use the "current row")
Not allowinng the adding of rows in the datagridview in the beginning is to handle the process of getting the total row counts.
If you leave the blank row (to add new records) it will throw off your count.
You can normally handle this by subtracting 1 or 2 from the total rows, however when you don't have any records this gets thrown off.
I was first using a datagridview instead of the spreadsheet, however i realized after a few hours of playing, when i added the rows to it they were added at the top of the list instead of at the end.
You can insert rows at a certain index (which would be the last current one) but i decided to go with the spreadsheet.
I also have code at the end to format the sheet as a nice, professional looking report without gridlines, sheet tabs, and without row and column headers.
It is also formatted to split and freeze the header row so that you can scroll through the report still see the header labels.
Anyway, i hope this helps some of you
And i did purposely leave this one as a question because i was told by a moderator that it will not come up in a search if it is a comment.
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
Form1.Show()
Form1.WorkbookView1.GetLock()
Me.PatientDataGridView.AllowUserToAddRows = False
Me.VisitDataGridView.AllowUserToAddRows = False
Me.PaymentDataGridView.AllowUserToAddRows = False
Dim patrecordcnt As Integer = Me.PatientDataGridView.Rows.Count '- 2
Dim pattracker As Integer = 0
Dim visrecordcnt As Integer = 0 'Me.VisitDataGridView.Rows.Count '- 1
Dim vistracker As Integer = 0
Dim patcellcnt As Integer = 0
Dim viscellcnt As Integer = 0
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
With Form1.WorkbookView1.RangeSelection
.Cells(0, 0).Value = "Patient ID"
.Cells(0, 1).Value = "Last Name"
.Cells(0, 2).Value = "First Name"
.Cells(0, 3).Value = "Visit ID"
.Cells(0, 4).Value = "Visit Date"
.Cells(0, 5).Value = "Visit Description"
End With
Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
Do While pattracker <> patrecordcnt
Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
With Form1.WorkbookView1.RangeSelection
.Cells(0, 0).Value = Me.PatientDataGridView.CurrentRow.Cells(0).Value
.Cells(0, 1).Value = Me.PatientDataGridView.CurrentRow.Cells(1).Value
.Cells(0, 2).Value = Me.PatientDataGridView.CurrentRow.Cells(2).Value
End With
If Me.VisitDataGridView.Rows.Count > 0 Then
visrecordcnt = Me.VisitDataGridView.Rows.Count '- 1
vistracker = 0
Do While visrecordcnt <> vistracker
'outline format #1
'Call this instead of #2 to start the visits on the line after the patient
Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
With Form1.WorkbookView1.RangeSelection
.Cells(0, 3).Value = Me.VisitDataGridView.CurrentRow.Cells(0).Value
End With
'start here for the payments
Me.VisitBindingSource.MoveNext()
vistracker = vistracker + 1
'outline format #2
'Call this instead of #1 to start the visits on the same line as the patient
'Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
'Form1.WorkbookView1.ActiveCell.EntireRow.Select()
Loop
End If
Me.PatientBindingSource.MoveNext()
'visrecordcnt = Me.VisitDataGridView.Rows.Count '- 1
'vistracker = 0
'patcellcnt = viscellcnt
'patcellcnt = patcellcnt + 1
pattracker = pattracker + 1
Loop
With Form1.WorkbookView1.ActiveWorksheet
.UsedRange.Columns.AutoFit()
.UsedRange.Cells.HorizontalAlignment = SpreadsheetGear.HAlign.Center
.UsedRange.Cells(0, 0).Select()
.Workbook.WindowInfo.DisplayWorkbookTabs = False
.WindowInfo.DisplayGridlines = False
.WindowInfo.DisplayHeadings = False
.WindowInfo.SplitRows = 1
.WindowInfo.FreezePanes = True
End With
Form1.WorkbookView1.ReleaseLock()
Me.PatientDataGridView.AllowUserToAddRows = True
Me.VisitDataGridView.AllowUserToAddRows = True
Me.PaymentDataGridView.AllowUserToAddRows = True
Me.PatientBindingSource.MoveFirst()
End Sub
Form1.Show()
Me.PatientBindingSource.MoveFirst()
Form1.WorkbookView1.GetLock()
Me.PatientDataGridView.AllowUserToAddRows = False
Me.VisitDataGridView.AllowUserToAddRows = False
Me.PaymentDataGridView.AllowUserToAddRows = False
Dim patrecordcnt As Integer = Me.PatientDataGridView.Rows.Count '- 2
Dim pattracker As Integer = 0
Dim visrecordcnt As Integer = 0 'Me.VisitDataGridView.Rows.Count '- 1
Dim vistracker As Integer = 0
Dim patcellcnt As Integer = 0
Dim viscellcnt As Integer = 0
Dim payrecordcnt As Integer = 0 'Me.VisitDataGridView.Rows.Count '- 1
Dim paytracker As Integer = 0
Dim ttlvischg As Double = 0.0
Dim paymnt As Double = 0.0
Dim pdttl As Double = 0.0
Dim bal As Double = 0.0
Dim todaysdate As Date = My.Computer.Clock.LocalTime.ToShortDateString
Dim visdate As String = Nothing
Dim daydif As Long = 0
Dim over3060 As String = Nothing
'MsgBox(todaysdate)
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
With Form1.WorkbookView1.RangeSelection
.Cells(0, 0).Value = "Patient ID"
.Cells(0, 1).Value = "Last Name"
.Cells(0, 2).Value = "First Name"
.Cells(0, 3).Value = "Visit ID"
.Cells(0, 4).Value = " Date"
.Cells(0, 5).Value = "Description"
.Cells(0, 6).Value = "Charge"
.Cells(0, 7).Value = "Paid"
.Cells(0, 8).Value = "Balance"
.Cells(0, 9).Value = "Overdue"
End With
Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
Do While pattracker <> patrecordcnt
Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
With Form1.WorkbookView1.RangeSelection
.Cells(0, 0).Value = Me.PatientDataGridView.CurrentRow.Cells(0).Value
.Cells(0, 1).Value = Me.PatientDataGridView.CurrentRow.Cells(1).Value
.Cells(0, 2).Value = Me.PatientDataGridView.CurrentRow.Cells(2).Value
End With
If Me.VisitDataGridView.Rows.Count > 0 Then
visrecordcnt = Me.VisitDataGridView.Rows.Count '- 1
vistracker = 0
Do While visrecordcnt <> vistracker
'outline format #1
'Call this instead of #2 to start the visits on the line after the patient
Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
Form1.WorkbookView1.ActiveCell.EntireRow.Select()
With Form1.WorkbookView1.RangeSelection
.Cells(0, 3).Value = Me.VisitDataGridView.CurrentRow.Cells(0).Value
.Cells(0, 4).Value = Me.VisitDataGridView.CurrentRow.Cells(2).FormattedValue
.Cells(0, 5).Value = Me.VisitDataGridView.CurrentRow.Cells(3).FormattedValue
ttlvischg = Me.VisitDataGridView.CurrentRow.Cells(4).Value + Me.VisitDataGridView.CurrentRow.Cells(5).Value
.Cells(0, 6).Value = ttlvischg.ToString("c")
End With
'start here for the payments
If Me.PaymentDataGridView.Rows.Count > 0 Then
payrecordcnt = Me.PaymentDataGridView.Rows.Count '- 1
paytracker = 0
Do While payrecordcnt <> paytracker
paymnt = Me.PaymentDataGridView.CurrentRow.Cells(5).Value
pdttl = pdttl + paymnt
Me.PaymentBindingSource.MoveNext()
paytracker = paytracker + 1
Loop
Else : pdttl = 0
paymnt = 0
End If
bal = ttlvischg - pdttl
visdate = Me.VisitDataGridView.CurrentRow.Cells(2).FormattedValue
daydif = DateDiff("d", visdate, todaysdate)
If daydif >= 30 And daydif < over3060 = " - Over 30">= 60 And daydif < over3060 = " - Over 60">= 90 Then
over3060 = " - Over 90"
Else : over3060 = ""
End If
With Form1.WorkbookView1.RangeSelection
.Cells(0, 7).Value = pdttl.ToString("c")
.Cells(0, 8).Value = bal.ToString("c")
.Cells(0, 9).Value = daydif & " days since visit" & over3060
End With
pdttl = 0
paymnt = 0
Me.VisitBindingSource.MoveNext()
vistracker = vistracker + 1
'outline format #2
'Call this instead of #1 to start the visits on the same line as the patient
'Form1.WorkbookView1.ActiveCell.Offset(1, 0).Select()
'Form1.WorkbookView1.ActiveCell.EntireRow.Select()
Loop
End If
Me.PatientBindingSource.MoveNext()
'visrecordcnt = Me.VisitDataGridView.Rows.Count '- 1
'vistracker = 0
'patcellcnt = viscellcnt
'patcellcnt = patcellcnt + 1
pattracker = pattracker + 1
Loop
With Form1.WorkbookView1.ActiveWorksheet
.UsedRange.Columns.AutoFit()
.UsedRange.Cells.HorizontalAlignment = SpreadsheetGear.HAlign.Center
.UsedRange.Cells(0, 0).Select()
.Workbook.WindowInfo.DisplayWorkbookTabs = False
.WindowInfo.DisplayGridlines = False
.WindowInfo.DisplayHeadings = False
.WindowInfo.SplitRows = 1
.WindowInfo.FreezePanes = True
End With
Form1.WorkbookView1.ReleaseLock()
Me.PatientDataGridView.AllowUserToAddRows = True
Me.VisitDataGridView.AllowUserToAddRows = True
Me.PaymentDataGridView.AllowUserToAddRows = True
Me.PatientBindingSource.MoveFirst()
End Sub
0 comments:
Post a Comment