Nice Automation sample

Hi Expert,
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