Memo datatype / access / MSFlexGrid

mojgan
04-16-2004, 05:50 AM
'Notes' is one of the field from my access table. It is definted as 'memo'. When I Add a new record or edit it, the text is written to the access table, but it is not displayed in MSFlexGrid. It does not produce any error either. It just shows blank cells. (Only the 'Notes' column is blank, all other columns are displayed.)

But if I change its type from memo to text. It works fine and the data is displayed in Notes column of the MSFlexGrid.

Any idea what the problem is?

Thanks for your attention.

MKoslof
04-16-2004, 08:07 AM
Can we see your code? Are you simply using the Set DataSource method to populate the Grid? Or are you using the unbound MSFlexGrid control? IF you are using the unbound control can we see your code? I think the FlexGrid might not like memo fields, but we may find a way around it. Can I see how you populate the Grid. And, is it really a problem to use a text field instead? Because if your field value is greater than 250 characters, how do you plan on showing it neatly within a FlexGrid anyway?

mojgan
04-16-2004, 08:49 AM
Here's the sub that populates the MSFlexGrid:

Public Sub populate_MSFlexGrid(ByVal table_name As String)
Dim codeValue As String
Set rs = New ADODB.Recordset

On Error GoTo data_error


' define codeValue to be used for sql expression to retrieve the appropriate
' dataset
If (table_name = "Births") Then
codeValue = "BTH%"
ElseIf (table_name = "Cancer") Then
codeValue = "CAN%"
ElseIf (table_name = "Congmal") Then
codeValue = "CON%"
ElseIf (table_name = "Deaths") Then
codeValue = "DTH%"
ElseIf (table_name = "Downs") Then
codeValue = "DOW%"
ElseIf (table_name = "HES") Then
codeValue = "HES%"
ElseIf (table_name = "HSE") Then
codeValue = "HSE%"
ElseIf (table_name = "Misc") Then
codeValue = "MSC%"
End If


' Define number of records in this table
Set rs = Conn_access.Execute("select COUNT(*) from " & sahsu_data_table & " where Code like '" & codeValue & "'")
NoOfRecords = rs(0)

'Define the SQL command
Set rs = Conn_access.Execute("select * from " & sahsu_data_table & " where Code like '" & codeValue & "' order by Code")

'initialize rows value
frmFlexGrid.MSFlexGrid1.Rows = 1

For i = 1 To NoOfRecords
'increase number of rows
frmFlexGrid.MSFlexGrid1.Rows = frmFlexGrid.MSFlexGrid1.Rows + 1

'set row to current row being filled
frmFlexGrid.MSFlexGrid1.Row = i

'set 1st column to Values from 'Code' column
frmFlexGrid.MSFlexGrid1.Col = 0
' Without checking for NULL, it will give the following error:
' "Invalid use of null". To overcome this, if the value is null,
' I assign "" to the MSFlexGrid cell
If IsNull(rs.Fields("Code")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Code"))
End If

'set 2nd column to values from 'Title' column
frmFlexGrid.MSFlexGrid1.Col = 1
If IsNull(rs.Fields("Title")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Title"))
End If

'set 3rd column to values from 'Volume' column
frmFlexGrid.MSFlexGrid1.Col = 2
If IsNull(rs.Fields("Volume")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Volume"))
End If

'set 4th column to values from 'Disk Serial' column
frmFlexGrid.MSFlexGrid1.Col = 3
If IsNull(rs.Fields("Disk Serial")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Disk Serial"))
End If


'set 5th column to values from 'Files Contained' column
frmFlexGrid.MSFlexGrid1.Col = 4
If IsNull(rs.Fields("Files Contained")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
' without "format", it was giving this error:
' "Error 94, Invalid use of null"
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Files Contained"))
End If


'set 6th column to values from 'Received' column
frmFlexGrid.MSFlexGrid1.Col = 5
If IsNull(rs.Fields("Received")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Received"))
End If

'set 7th column to values from 'Received by' column
frmFlexGrid.MSFlexGrid1.Col = 6
If IsNull(rs.Fields("Received by")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Received by"))
End If

'set 8th column to values from 'Received from' column
frmFlexGrid.MSFlexGrid1.Col = 7
If IsNull(rs.Fields("Received from")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Received from"))
End If


'set 9th column to values from 'Verified' column
frmFlexGrid.MSFlexGrid1.Col = 8
If IsNull(rs.Fields("Verified")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Verified"))
End If

'set 10th column to values from 'Verified by' column
frmFlexGrid.MSFlexGrid1.Col = 9
If IsNull(rs.Fields("Verified by")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Verified by"))
End If


'set 11th column to values from 'Data contained' column
frmFlexGrid.MSFlexGrid1.Col = 10
If IsNull(rs.Fields("Data contained")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Data contained"))
End If


'set 12th column to values from 'Notes' column
frmFlexGrid.MSFlexGrid1.Col = 11
If IsNull(rs.Fields("Notes")) Then
frmFlexGrid.MSFlexGrid1.Text = ""
Else
frmFlexGrid.MSFlexGrid1.Text = Format(rs.Fields("Notes"))
End If


rs.MoveNext ' move to the next record
Next i

frmFlexGrid.MSFlexGrid1.Refresh

If (rs.State = 1) Then
rs.Close
End If
Set rs = Nothing

Exit Sub

data_error:
' write errors into "errorLog.txt" file
logError Err.Number, Err.Description, "database.bas", "populate_MSFlexGrid", _
Erl(), Err.Source, "Please contact the System Administrator...."

End Sub

And, is it really a problem to use a text field instead? Because if your field value is greater than 250 characters, how do you plan on showing it neatly within a FlexGrid anyway?

Yes, I need more space to store information. If the stored text is longer that what can be displayed in the MSFlexGrid cell, the user can double click on the cell. A popup window (scrollable) then appears which shows the full information.

Flyguy
04-16-2004, 09:16 AM
Some time ago I read something about retrieving memo fields from recordsets.
It had something to do with first assigning the value to a string variable before using it.
So try something like this:

Dim sMemo As String

sMemo = "" & rs.Fields("Notes") ' The "" is to skip the check for IsNull
frmFlexGrid.MSFlexGrid1.TextMatrix(i, 11) = sMemo

mojgan
04-16-2004, 09:42 AM
Some time ago I read something about retrieving memo fields from recordsets.
It had something to do with first assigning the value to a string variable before using it.
So try something like this:

Dim sMemo As String

sMemo = "" & rs.Fields("Notes") ' The "" is to skip the check for IsNull
frmFlexGrid.MSFlexGrid1.TextMatrix(i, 11) = sMemo


Thank you Flyguy !!!!

It's amazing! It's actually "" (ie The "" is to skip the check for IsNull) that got rid of the problem !!! I didn't know that, it also makes my code much smaller !

You helped me a lot! Thanks ! :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum