Can I fill a flexgrid control written in VB6 from an Access Table?

Yes. Want a less vague answer? Ask a more specific question.

Sorry for being so vague. Here is what I am looking to do. I have a
FlexGrid that consist of 32 Cols and 14 Rows. There is 1 Fixed Row.
Column 1 is for Names. Columns 2 through 32 are for attendance codes.
The data for the flexgrid would be from an Access Database named
"PayrollDatabase" and a table named "Attendance". The fields that would
be used are named "Name" and "AttendCode". The AttendCode data would be
1 character in length: "A", "X", "H","V" representing whether a person
was present or absent or on vacation, etc. each day of a particular
month.
The fixed row is for labeling the columns: "Name" and the numbers 1
through 31 for the days. The remaining 13 rows are for the 13 people I
am tracking.
Basically, I'm looking to fill the FlexGrid with Names and Attendance
codes from this database. Thinking about this as I type, perhaps I
should use a query to fill the flexgrid if that's possible?
I hope this is enough info.

Show quote Hide quote I'm not a database programmer, so I can't help you out with that
part of the interfacing of your Access data to the MSFlexGrid.
However, there is property of the MSFlexGrid (named Clip) which
allows you to make a selection of cells and fill them from a
String in which the column data are separated by TAB characters
and the row data are separated by CARRIAGE RETURNS. So, if you can
get your table data in that format, you can do the assignment in
one line (once the selection has been set, of course). Here is
some sample code to demonstrate how the Clip property can be used.

    Dim X As Long, Y As Long
    Dim RowData As String
    Dim TableData As String
    Dim Names() As String
    Dim Codes() As String
    '  Create meaningless sample data using TAB delimited
    '  data items with CARRIAGE RETURN delimited lines.
    Names = Split("Rick,Joe,Bill,Bob,Sue,Ann,Phil," & _
                  "Bo,Nick,Rob,Mary,John,Val", ",")
    Codes = Split("A,X,H,V", ",")
    For X = 1 To 14
      RowData = Names(X - 1)
      For Y = 2 To 32
        RowData = RowData & vbTab & _
                  Codes(Int(Rnd * (UBound(Codes) + 1)))
      Next
      TableData = TableData & RowData & vbCr
    Next
    '  At this point, all of our data is in a single string
    '  variable (TableData) where the column data is separated
    '  by TAB characters and each row is separated be a
    '  CARRIAGE RETURN. Now, assign that data to the grid
    With MSFlexGrid1
      '  Select the area to fill
      .Row = .FixedRows
      .Col = .FixedCols
      .RowSel = .Rows - 1
      .ColSel = .Cols - 1
      '  Perform the actual filling of data
      .Clip = TableData
      '  Select some grid cell so as to cancel the selection
      .Row = .FixedRows
    End With

Here I have assumed the grid set up as you described (32 columns,
14 rows) but I didn't place any labels in the header. Hopefully
this is of some use to you.

Rick

Here is what I have for code so far. To fill the FlexGrid I am using a
TextBox (txtOut) just to test the FlexGrid.

Rick.. Your suggestion is a nice idea, but I think it would add extra
steps. I'm hoping to click a button control that would get the data and
fill the grid.

This is my code so far:

Private Sub AttendGrid_Click()
    On Error GoTo errorHandler
    Dim iCol As Integer
    Dim iRow As Integer
    Dim i As Integer

    With AttendGrid
    .TextMatrix(0, 0) = "Name"
        .ColWidth(0) = 1800
        For i = 1 To 31
        .ColWidth(i) = 310
            .TextMatrix(0, i) = i
            Next i
      For i = 1 To 13
        Next i
        End With

    iCol = 1
    iRow = 1
    flxBin.TextMatrix(iRow, iCol) = (txtOut)
    If iCol = 31 Then
    iRow = iRow + 1
    Else: iCol = iCol + 1

    End If
    Exit Sub
   errorHandler:
   Debug.Print Err.Number & "  " & Err.Description

  End Sub

=============================================
  Private Sub txtOut_Change()
  Dim iLen
  Dim i As Integer
  Dim c As Integer
  Dim p As Integer
  Dim r As Integer

  iLen = Len(txtOut)

  AttendGrid.Col = c
  AttendGrid.Row = r
  r = 1
  p = 0

  For i = 1 To 403

    c = c + 1
    p = p + 1

    AttendGrid.TextMatrix(r, c) = UCase(Mid(txtOut, p, 1))

    If c = 31 Then
    c = 0
    r = r + 1

    End If
    Next i
    End Sub

Rick, I thought I would play around with your script to see how it
worked. I am getting an "Error 9 Subscript out of range" that seems to
be tied to linecode: RowData = Names(X - 1). If I comment it out, it
loads all the attendance code, but no names. Any idea what is causing
this?

Mike

Oops! My mistake... change the limit on the For statement to 13
(from the 14 it is now). There are 14 rows, but they are numbered
0 to 13 and the first row (the zeroeth one) is reserved for the
Header text, so we want to fill the names into rows 1 through 13.
Sorry for any confusion.

By the way, I'm guessing you can output your data as standard
comma delimited text which would probably use a normal newline
(vbCrLf) sequence between rows of data. Since the Clip property
uses only the Carriage Return (vbCr) to separate lines, you should
be able to use the Replace function to replace the vbCrLf with
vbCr to get around this problem.

Rick

Thanks Rick! That did the trick. If I were able to output the data into
a comma delimited text file, how would I code the flexgrid to load it?
I'm adding your code with my changes for you to check.

Mike

Private Sub MSFlexGrid1_Click()
    On Error GoTo errorHandler
    Dim X As Long, Y As Long
    Dim RowData As String
    Dim TableData As String
    Dim Names() As String
    Dim Codes() As String
    Dim i As Integer

    '  Create meaningless sample data using TAB delimited
    '  data items with CARRIAGE RETURN delimited lines.
    Names = Split("Rick,Joe,Bill,Bob,Sue,Ann,Phil," & _
    "Bo,Nick,Rob,Mary,John,Val", ",")
    Codes = Split("A,X,H,V", ",")
    For X = 1 To 13
      RowData = Names(X - 1)
      For Y = 2 To 32
        RowData = RowData & vbTab & Codes(Int(Rnd * (UBound(Codes) +
1)))
      Next

      TableData = TableData & RowData & vbCr
    Next
    '  At this point, all of our data is in a single string
    '  variable (TableData) where the column data is separated
    '  by TAB characters and each row is separated be a
    '  CARRIAGE RETURN. Now, assign that data to the grid
    With MSFlexGrid1
      '  Select the area to fill
      .TextMatrix(0, 0) = "NAME"
      .ColWidth(0) = 1800
      For i = 1 To 31
      .ColWidth(i) = 310
      .TextMatrix(0, i) = i
      Next i

      .Row = .FixedRows
      .Col = .FixedCols
      .RowSel = .Rows - 1
      .ColSel = .Cols - 1
      '  Perform the actual filling of data
      .Clip = TableData
      '  Select some grid cell so as to cancel the selection
      .Row = .FixedRows
    End With


errorHandler:
    Debug.Print Err.Number & "  " & Err.Description

End Sub

I checked your code and it looked fine (the proof being that the
grid loaded fine). The only addition it needed was an Exit Sub
statement (because you housed the code in a Sub) to stop your
non-error code from falling through into your error handler. Now,
below is some code to show you how to read a comma delimited text
file into the grid directly (also see my additional comment
directly after the listing).

Private Sub MSFlexGrid1_Click()
  Dim i As Long
  Dim FileNum As Long
  Dim TableData As String
  Dim TotalFile As String
  On Error GoTo errorHandler
  '  Read the text file in all at once
  FileNum = FreeFile
  Open "c:\temp\GridTest.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  '  Convert the embedded vbCrLf's to vbCr's
  TotalFile = Replace$(TotalFile, vbCrLf, vbCrLf)
  '  At this point, all of our data is in a single string
  '  variable (TotalFile) where the column data is separated
  '  by TAB characters and each row is separated be a
  '  CARRIAGE RETURN. Now, assign that data to the grid
  With MSFlexGrid1
    '  Set up the headers
    .TextMatrix(0, 0) = "NAME"
    .ColWidth(0) = 1800
    For i = 1 To 31
      .ColWidth(i) = 310
      .TextMatrix(0, i) = i
    Next i
    '  Select the area to fill
    .Row = .FixedRows
    .Col = .FixedCols
    .RowSel = .Rows - 1
    .ColSel = .Cols - 1
    '  Perform the actual filling of data
    .Clip = TotalFile
    '  Select some grid cell so as to cancel the selection
    .Row = .FixedRows
  End With
  Exit Sub
ErrorHandler:
  Debug.Print Err.Number & "  " & Err.Description
End Sub

Now, this supposes that the data in the text file is properly
formatted (that is, there are 13 rows of data each containing 31
columns of data. So, how do you create that file directly from the
grid (so that you can import it into your Access database)? Well,
with the Clip function again. Here is the code for that (using the
Click event of an assumed CommandButton named Command1)...

Private Sub Command1_Click()
  Dim FileNum As Long
  Dim GridOfData As String
  With MSFlexGrid1
    '  Select the area to read
    .Row = .FixedRows
    .Col = .FixedCols
    .RowSel = .Rows - 1
    .ColSel = .Cols - 1
    '  Read the data
    GridOfData = .Clip
    '  Select some grid cell so as to cancel the selection
    .Row = .FixedRows
    '  Save the data to a file
    FileNum = FreeFile
    Open "c:\temp\GridTest.txt" For Output As #FileNum
      Print #FileNum, GridOfData
    Close #FileNum
  End With
End Sub

Hopefully, this all is of some use to you.

Rick

Hi Rick,
I am having a problem where all of the data for  each person is being
filled into the first column (Name) and none of the other columns are
getting any data. I suspect that the problem lies in my text file. I
created a report in Access2002 that puts the data for each person on
one line. Then I exported it to a text file. I edited the program code
to open the file. What am I doing wrong??

Mike

Hard to say without having the text file. When you export it, is
it definitely being exported as a TAB delimited file? Perhaps if
you copy the file and paste it into a response here (don't attach,
just copy/paste), maybe we'll be able to see something in the
structure of the file that looks wrong. Make sure you edit the
names into something meaningless (to protect identities).

Rick

I had exported it as a text file, not a TAB delimited file. Access did
not give me that option, so I had to export it to an Excel format and
then to a TAB delimited text file. It is working now, but I discovered
another bug. My database keeps track of the scheduled workdays only. It
doesn't keep track of weekends (non-work days), so the data isn't
lining up with the day labels. I think I have to go back to the drawing
board and figure how to get the data to fill in the correct columns.

Mike

Show quote Hide quote Post the text file (that is, paste it into your response) that
Access outputs (remember to disguise the names) and let's see if
we can work directly with that (it should be simple text
manipulations). If you could add any explanatory notes so that we
can fully understand what we are looking at, that would be
helpful.

Rick

Here is the text file that Access created using the Export to text file
option. I deleted the lastnames and kept the first names.

Sandra   X   X  H   X   X   X   X  X   X   X  X   X   X   X   X  X   X
  X  X   X   X   X

John         X   X  H   X   X   X   X  X   X   X  X   X   X   X   X  X
  X   X  X   X   X   X

Marie         X   X  H   X   X   X   X  X   X   X  X   X   X   U   X
X   X   X  X   X   X   X

Leanne            H   X   X   X   X  U   X      X   X   U   X   X  X
U   X  X   X   X   X

Dwayne       X   X  H   X   X   X   X  X   X   X  X   X   X   X   X  X
  X   X  X   X   X   X

Mary        X   X  H   X   X   X   X  X   X   X  X   X   X   X   X  X
X   X  X   X   X   X

Elden        X   X  H   X   X   X   X  X   X   X  X   X   X   X   X  X
  X   X  X   X   X   X

Janice     X   X  H       X   X   X  X   X   X  X   X   X   X   X  X
X   X  X   X   X   X

Rudolph       X   X  H   U   X   X   X  X   X   X  X   X   X   X   X
X   X   X  X   X   X   X

John       X   X  H   X   X   X   U  X   X   X  X   X   X   X   X  X
X   X  X   X   X   X

Laura   X   X  H   X   X   X   X  X   X   X  X   X   X   X   X  X   X
X  X   X   X   X

JoIda     X   U  H   X   X   U   X  X   X   X  X   X   X   X   X  X
U   X  X   X   X   U

Linda        X   U  H   U   X   X   X  X   X   X  X   X   X   X   U  X
  X   X  X   X   X   X

This data is for the month of September-2005 which had 22 weekdays
which is why there are only 22 entries per person instead of 30.

I figured that out already<g>;  however, I was wondering about the
spacing. It looks like the inter-field spacing was established by
original Tab positioning converted to equivalent space characters.
Are you sure this isn't a tab delimited file?

I know attachments are frowned on in this newsgroup, but there is
hardly any data in your file. Is there any way you can change the
names in the database and then output the file? If so, can you
output only the first two records (that would help keep the size
of the posting down)? That would be enough to see what is going on
in the file. If so, do that and attach the text file that is
produced so I can look at it BEFORE it is touched by a text
editor. I can't be sure if your text editor or VB is converting
the tabs (that I suspect are in the file) away and masking what I
am seeing. Thanks.

Oh, and you could further reduce the size of your posting by
zipping the file before attaching it.

Rick

I don't see an option for attaching a file. I reduced the records to 3
people and removed their names replacing them with their File ID. This
is what it looks like:

01026    X    X    H    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X
01060    X    X    H    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X
01218    X    X    H    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X    X

If you know of a way to attach a file here let me know and I'll do it.
It's only 1k in size.

Mike

Rick.. When I put this database together I added a field [CalDay] that
uses the Day Function to convert the [DateWorked] field to the day of
the month. I was thinking that maybe I could use an IF statement to
assign the data in the proper grid cell. What do you think?

Mike

One more thing, when the data fills the FlexGrid, it alligns the FileID
to the right and the other 2 FileID's to the left. It also places a
bold vertical line before the second and third FileIDs. It looks
something like: I01026. I think they are format codes. How can this be
corrected?

Mike

The problem stems from a mistyping in the last code snippet I
posted. The incorrect line reads like this...

     TotalFile = Replace$(TotalFile, vbCrLf, vbCrLf)

If you think about what this says, it is replacing a
CarriageReturn/LineFeed sequence with itself... not a very useful
thing to do<g>. Anyway, that line should read this way...

     TotalFile = Replace$(TotalFile, vbCrLf, vbCr)

That replaces the CarriageReturn/LineFeed with the simple
CarriageReturn row separator that the Clip property is looking
for.

Rick

Beginner needs help with text files
A Question of VB6 Asynchronicity
Designing an expert system
Editing an already open word document with VB6
ReordCount related
SetFocus!
Substring
cancel button not working properly
Problem loading Form.Picture at run time
Insert an Image