Master/Detail Tables: Inserting rows for Detail Tbl programmatically

[deyken]

Hi Guys!

I have created a Master/Detail relationship between 2 tables in SQL Server and need to fill out the Master table data into specific fields on an Excel Sheet, then (using existing formatting) need to insert rows for each Detail record (perhaps with a For..Next statement) while retaining the existing formatting (borders, shades, fonts etc.).

How do I accomplish the row insertion and formatting with VBA?

Here is my Code attempt:
____________________________________________________________
PLDetail.Open "SELECT * FROM tblPACKING_LISTS_d WHERE tblPACKING_LISTS_d.PACKINGLIST_NO = " & PackingList, BSS_PL, adOpenDynamic, adLockReadOnly
If PLDetail.RecordCount <> 0 Then
For i = 0 To PLDetail.RecordCount - 1
Rows("8:8").Select
Selection.Insert Shift:=xlDown
Range("A8:G8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next i
Else
Sheet2.Cells(1, "A") = "No items for " & PackingList
End If
____________________________________________________________
PS: Sheet1 is my main formated document, but I am only testing code on Sheet2 so do not worry about mixed references of these two sheets...

[stanl]

This probably wont help you, but when I have faced situations like that I found it easiest to:

1. Perform SELECT statement
2. Use Getstring() to create TAB-delimted field list
3. Copy to Clipboard then paste in to Excel
4. Reformat the usedRange

When dealing with thousands of rows, this saves considerable time looping through a recordset.

.02 Stan

[mdmackillop]

I don't disagree with Stan, but consider

With Range("A8:G" & 8 + (PLDetail.RecordCount - 1)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With



Also, rather than inserting rows (if this is filling in data), can you work from the bottom up?

For i = PLDetail.RecordCount - 1 To 0 Step -1

[stanl]

Don't disagree with Stan,

Are you telling the OP not to disagree with Stan because Stan is going to get angry... or you personally don't disagree with Stan... :dunno :dunno :dunno :bug: :beerchug:

[mdmackillop]

Duly corrected.
Thanks Stan :hi: