Grid Control

[Ward Horsfall]

Hi,

Is there anyway I can use a grid control on an VBA form I am creating...

Thanks,

Ward.

 

[Woody]

Sure, it works like a champ but under the following condition; cant
attah to ado control, must use ado code instead. so no early binding.

i have tons of code that does that i vba.

Woody
I am not responsible for anything you may see with my name attached to
it, i think.

 

[Ward Horsfall]

Woody,

Thanks is there a particular sample you could point me to..

I don't mind if it is not data bound as I can code around it..

Ward.

 

[woody]

Public Sub Change_Query(SqlCommand As String)

On Error Resume Next

Dim conLetters As Connection
Dim recLetters As Recordset

Dim bolLoad As Boolean
Dim lngLooper As Long
Dim intLooper As Integer
Dim intFields As Integer
Dim StrSQL As String

With frmEmbassyLetters

' clear screen grid
..grdLetters.ClearStructure
..grdLetters.Clear
..grdLetters.Rows = 1
..grdLetters.Visible = True

..optAllAge.Enabled = True
..optYoung.Enabled = True
..optOld.Enabled = True

..optInitial.Enabled = True
..OptReschedule.Enabled = True

..cmbGender.Enabled = True
..cmbMarried.Enabled = True

..grdLetters.Visible = True

Set conLetters = New Connection
conLetters.CursorLocation = adUseClient
conLetters.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source=" & ActiveDocument.Path & "\H2b_Applicants.mdb;"

StrSQL = Trim(SqlCommand) & " " & SORT_SQL

Set recLetters = New Recordset
recLetters.Open StrSQL, conLetters, adOpenForwardOnly,
adLockReadOnly

lngLooper = 0
intFields = recLetters.Fields.Count

..grdLetters.Cols = intFields
..grdLetters.Rows = recLetters.RecordCount

' get record set
recLetters.MoveFirst

Do While recLetters.EOF = False

lngLooper = lngLooper + 1
' put into first column of row x of screen
.grdLetters.AddItem recLetters.Fields(0).Value,
lngLooper
' load rest of values to screen
For intLooper = 1 To intFields Step 1

If IsNull(recLetters.Fields(intLooper).Value)
= False Then
.grdLetters.TextMatrix(lngLooper,
intLooper) = recLetters.Fields(intLooper).Value
Else
.grdLetters.TextMatrix(lngLooper,
intLooper) = " "
End If
Next intLooper
' format column 4 as date
.grdLetters.TextMatrix(lngLooper, 4) =
FormatDateTime(recLetters.Fields(4).Value, vbShortDate)

' get next record
recLetters.MoveNext
' continue looping till done
Loop

' set widths of nondisplayed columns to 0(dont want to
display)
For intLooper = 6 To intFields Step 1
.grdLetters.ColWidth(intLooper) = 0
Next intLooper

..grdLetters.Rows = lngLooper + 1

..grdLetters.FixedRows = 1
..grdLetters.FontFixed.Bold = True
..grdLetters.TextStyleFixed = flexTextRaised
..grdLetters.GridLinesFixed = flexGridInset

..grdLetters.GridColorFixed = &H8000000F
..grdLetters.BackColorFixed = &H8000000F

..grdLetters.ColWidth(0) = 0
..grdLetters.ColWidth(1) = 1900
..grdLetters.ColWidth(2) = 750
..grdLetters.ColWidth(3) = 930
..grdLetters.ColWidth(4) = 900
..grdLetters.ColWidth(5) = 3000

' set column headers
..grdLetters.TextMatrix(0, 1) = "Applicant Name"
..grdLetters.TextMatrix(0, 2) = "Gender"
..grdLetters.TextMatrix(0, 3) = "Mar.Status"
..grdLetters.TextMatrix(0, 4) = "Birth Date"
..grdLetters.TextMatrix(0, 5) = "Place of Birth"

..grdLetters.Rows = .grdLetters.Rows - 1

' shows status message on screen
If .grdLetters.Rows - 1 > 0 Then
Display_Message "Selection is " & Trim(Str
(.grdLetters.Rows - 1)) & " Applicants", "w"
Else
Display_Message "None Selected", "w"
End If

End With

recLetters.Close
Set recLetters = Nothing

conLetters.Close
Set conLetters = Nothing

End Sub