help with condition in a public sub i want to create

[angelpr23]

hello everybody,
i have a private sub in my program displays data of a table in an mshflexgrid calling it flxResults. Well, in many forms the flxResults displays data of different tables for example in frmOrdDtlsAllRcs in the flxResults i display the OrderDetails table's records, in frmCustSrchResults i display the customers search results according to user's criteria etc. In column headers of flexResults, i don't display the field names of the associated table, i don't like it and i display with greek names the column headers and this for appearance reasons.
Well, i want to create a public sub ColHeadrs in my module, i don't know exactly how will be and that's i need your help, i want something like
public sub ColHeadears (arguments if nessacary)
if condition1 then
displaying the column headers with greek names for OrderDetails table
elseif condition2 then
displaying the column headers with greek names for customers table
elseif condition3 then
etc..
end if
end sub
I am looking for the condition to do this because
i can't think something about this, i know how to display the column headers with greek names. Any ideas please?

For additional help, just an example of displayData looks.
This sub displays the data in the flxResults grid
concretely the orderDetails records for the order displayed.

Public Sub displayData()
sum = 0
sql = "select * from orderDetails " & _
"where (orderID='" & AdoOrd.Recordset!OrderID & "');"

Set rsOrdDet = New ADODB.Recordset
rsOrdDet.CursorLocation = adUseClient
rsOrdDet.Open sql, conn, adOpenStatic, adLockOptimistic

' Make room for column widths.
num_cols = rsOrdDet.Fields.Count - 1
ReDim col_wid(0 To num_cols - 1)

' Set column headers.

flxResults.Rows = 2
flxResults.cols = num_cols
flxResults.FixedCols = 0
flxResults.FixedRows = 1

'here i want to call the ColHeadrs sub to display
'the properly headers for the table i display the data

r = 0
' Display the data.
Do Until rsOrdDet.EOF
r = r + 1
flxResults.Rows = r + 1
For c = 1 To rsOrdDet.Fields.Count - 1
flxResults.TextMatrix(r, c - 1) = rsOrdDet.Fields(c).Value

' See if we need to enlarge the column.
new_wid = TextWidth(rsOrdDet.Fields(c - 1).Value)
If col_wid(c - 1) < new_wid Then col_wid(c - 1) = _
new_wid
Next c
rsOrdDet.MoveNext
Loop

' Set the grid's column widths.
For c = 0 To num_cols - 1
flxResults.ColWidth(c) = col_wid(c) + 1500
Next

'calculates the sumprice of products by order
rsOrdDet.MoveFirst
Do Until rsOrdDet.EOF
sum = sum + rsOrdDet!ProdSumPrice
rsOrdDet.MoveNext
Loop
SumPriceTxt.Text = sum
rsOrdDet.Close
End Sub

Any help with be much appreciated
Thank you in advanced.

 

[PeterWallace]

With TheGRIDNAME

.Redraw = False
.FixedCols = 1
' modify column's headers
' set grid's column widths
I = 0
.TextMatrix(0, I) = "TENANT": .ColWidth(I) = 4100: Tnamecol = I: I = I + 1 '0
.TextMatrix(0, I) = "ID": .ColWidth(I) = 0: IDCol = I: I = I + 1 '1
.TextMatrix(0, I) = "START DATE": .ColWidth(I) = 1600: SdateCol = I: I = I + 1 '2
.TextMatrix(0, I) = "END DATE": .ColWidth(I) = 1600: EdateCol = I: I = I + 1 '3
.TextMatrix(0, I) = "RENT": .ColWidth(I) = 1300: RentCol = I: I = I + 1
.TextMatrix(0, I) = "PERIOD": .ColWidth(I) = 1300: PeriodCol = I: I = I + 1 '3
.TextMatrix(0, I) = "INTEREST": .ColWidth(I) = 1200: InterestCol = I: I = I + 1 '3
.TextMatrix(0, I) = "Skey": .ColWidth(I) = 0: Skeycol = I


' set grid's column Alignment


For I = 0 To 6
Select Case I
Case 0 To 4, 6
.ColAlignment(I) = flexAlignRightCenter

Case Else
.ColAlignment(I) = flexAlignCenterCenter
End Select
Next
For I = 1 To .Rows - 1
For j = 0 To .Cols - 1
Select Case j
Case PeriodCol
mstr = .TextMatrix(I, PeriodCol)
Select Case mstr
Case "M"
mstr = "MONTH"
Case "4"
mstr = "4 WEEKS"
Case "F"
mstr = "FORTNIGHT"
Case Else
mstr = "WEEK"
End Select
.TextMatrix(I, PeriodCol) = mstr

Case SdateCol, EdateCol
' .TextMatrix(i, j) = FixMatrixDate(.TextMatrix(i, j))
Case RentCol, InterestCol
aDouble = Val(.TextMatrix(I, j))
.TextMatrix(I, j) = Format(Adouble,"###,###,##0.00"
End Select
Next
Next
' set grid's style
.AllowBigSelection = True
.FillStyle = flexFillRepeat

' make header bold
.Row = 0
.Col = 0
.RowSel = .FixedRows - 1
.ColSel = .Cols - 1
.CellFontBold = True

' grey every other column
' For I = .FixedCols To .Cols() - 1 'Step 2
'For I = 0 To .Rows() - 1 'Step 2
' .Col = I
' .Row = 0
' .RowSel = .Row
' If I Mod 2 = 0 Then
' .CellBackColor = &HC0C0C0 ' light grey
'Else
' .CellBackColor = vbWhite
' End If

' Next I

.AllowBigSelection = False
.FillStyle = flexFillSingle
.Redraw = True

End With

 

[angelpr23]

Thank you much PeterWallace for your help,
but this is not what exactly i want to do.
Well, to explain it better, i have a db created in msaccess, the db includes tables and the tables include fields. As a conclusion, the fields differentiate from table to table.
So, in my program i have many forms, one form displays in a mshflexgrid customers, other form in a mshflexgrid too products, other suppliers etc.

public sub ColHeadears (arguments if nessacary)
if condition1 then
' for example customers table
frmAllCust.flxResults.TextMatrix(0, 0) = "Code"
frmAllCust.flxResults.TextMatrix(0, 1) = "name"
frmAllCust.flxResults.TextMatrix(0, 2) = "address"
etc...
elseif condition2 then
'for example products table
frmAllProd.flxResults.TextMatrix(0, 0) = "Product ID"
frmAllProd.flxResults.TextMatrix(0, 1) = "Product Name"
frmAllProd.flxResults.TextMatrix(0, 2) = "Product Price"
etc...
elseif condition3 then
etc..
end if
end sub

I am looking for the condition of if statement in my public sub nothing else. May anyone help me please??

Thank you all
very much.

 

[Andrzejek]

Why don't you pass an ADODB recordset to your Sub?

Public Sub displayData(recMyRst As ADODB.Recordser)

But do it this way:
For column/header names use aliases:

Select abc [blue]As ColumnName1[/blue], xyz [blue]as ColumnName2[/blue], ..., ghj [blue]As ColumnName17[/blue] from orderDetails etc.

and in your Sub use:
recMyRst.Fields(1).Name
recMyRst.Fields(2).Name
recMyRst.Fields(3).Name
...
recMyRst.Fields(17).Name as column header

recMyRst.recordCount gives you number of rows in your flexgrid.

Have fun.

---- Andy

 

[ChewDoggie]

Yes, slightly modified from what Andr... said:

Call the public Function DisplayData:

MyColHeaders = displayData(rsMyRecordset)
arrColHeaders = Split(MyColHeaders, ",")

Public Function displayData(recMyRst As ADODB.Recordset) as String
Dim i as integer
Dim ReturnString as String

ReturnString = ""
For i = 0 to recMyRst.Fields.Count - 1
Select Case trim(recMyRst.Fields(i).Name)
Case "CustName"
ReturnString = ReturnString & "Customer Name"
Case "CustAddr"
ReturnString = ReturnString & "Customer Address"
End Select
If i <> (recMyRst.Fields.Count - 1) Then
ReturnString = ReturnString & ","
End if
Next i
displayData = ReturnString
End Function

OR, instead of returning a string, you could set Global Variables that hold each Column Header

HTH




AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Andrzejek]

I see no responce from angelpr23 so I assume he/she is not happy with answers here....

Let me fallow up on my idea:

Public Sub PutDataInGrid(recMyRec As ADODB.Recordset)
Dim c As Integer
Dim r As Integer

recMyRec.Open

With MSHFlexGrid1
    .Clear
    .AllowUserResizing = flexResizeColumns
    .Redraw = False
    .Cols = recMyRec.Fields.Count
    .Rows = recMyRec.RecordCount + 1
    
    [green]'Put Fields' Names as Grid's columns headers[/green]
    For c = 0 To recMyRec.Fields.Count - 1
        .TextMatrix(0, c) = recMyRec.Fields(c).Name
    Next c
    
    [green]'Pud data in Grid[/green]
    For r = 1 To recMyRec.RecordCount
        For c = 0 To recMyRec.Fields.Count - 1
            .TextMatrix(r, c) = (recMyRec.Fields(c).Value & "")
        Next c
        recMyRec.MoveNext
    Next r
    
    .Redraw = True
End With

recMyRec.Close

End Sub

You can use it by passing an ADODB recordset to it:

strSQL = "SELECT * FROM MyTable ORDER BY abcd"

recOne.Open strSQL, Cn

recOne.Close

Call PutDataInGrid(recOne)

But this way you will get the names of Fields in your table as your grid's column header.

You may modify your SQL with aliases to get the names for your columns in the Grid;

strSQL = "SELECT ProdId [blue]AS ProductID[/blue], " _
& " ProdN [blue]As ProductName[/blue], PPrice [blue]As PructPrice[/blue] " _
& " FROM MyTable ORDER BY abcd"

recOne.Open strSQL, Cn

recOne.Close

Call PutDataInGrid(recOne)

This way you will get the headers in your grid :
ProductID
ProductName
PructPrice

You may investigate how to make your aliases:
Product ID
Product Name
Pruct Price

It may involve using [] in your SQL


Have fun.

---- Andy

 

[angelpr23]

Thank you very much both of you.
But that's all weren't really helpful for me.
Maybe, i havent explained my problem very good, i don't speek well english but i will try one more time.
First of all, i don't want to use Rs.Fields(i).Name because i don't want the name of the fields of tables.
I want to representate the name of every field with explanation in greek.
For example,
if a name of a field is CustID (customerID) the word in greek is "Kodikos pelati",
CustFName the word in greek is "Onoma pelati"
CustLName the word in greek is "Eponimo pelati"
Well, i want the column headers of mshflexgrid for customers table to be "Kodikos pelati" (first column), "Onoma pelati" (second column) etc.. with greek characters. I know how to manage this and the what i am going to use about these you wrote is to pass the rs in displaydata as argument. I don't want to create 8 differnt sub rootines one for every table to do this. I want to do it in one.

I have thought to break the sql query. For example if
sql="Select * from customers;" I am thinking to declare a string variable tbl.
So i will say something like tbl="customers"
sql="Select * from " & tbl

Well, maybe the routine will sheems like this
public sub ColHeadears (string tbl)
'I AM LOOKING FOR THE CONDITION NOTHING ELSE
if tbl="customers" then
' for example customers table
'but in my code with greek characters
frmAllCust.flxResults.TextMatrix(0, 0) = "Kodikos pelati"
frmAllCust.flxResults.TextMatrix(0, 1) = "Onoma pelati"
frmAllCust.flxResults.TextMatrix(0, 2) = "Eponimo pelati"
etc...
elseif tbl="products" then
'for example products table
frmAllProd.flxResults.TextMatrix(0, 0) = "Kodikos proiodos"
frmAllProd.flxResults.TextMatrix(0, 1) = "Onoma proiodos"
frmAllProd.flxResults.TextMatrix(0, 2) = "Timi Proiodos"
etc...
elseif tbl="something else" then
etc..
end if
end sub

I don't want if its a good idea. I want your opinion.

Thank you
much all

 

[Andrzejek]

Giasas, ti kanis?

Your idea should work just fine for columne headres in grid if you pass a table name:

Public Sub ColHeadears (tbl AS String)

Select Case tbl
   Case "customers"
     frmAllCust.flxResults.FormatString = "<Kodikos pelati |<Onoma pelati |<Eponimo pelati"
   Case "products"
     frmAllCust.flxResults.FormatString = "<Kodikos proiodos |<Onoma proiodos |<Timi Proiodos"
   Case Else
     frmAllCust.flxResults.FormatString = "<Ena |<Duo |<Tria"
End Select

End Sub

But it may not work for the rest of data in the grid, because of names of Fields in your tables.

"Select * from " & tbl

is fine, but what about the:
"WHERE ..... " part of your SELECT?


Have fun.

---- Andy

 

[MakeItSo]

Why don't you create a lookup table?
One that will hold all field names in one column, and their Greek counterpart in the other column?

This way you could always pull the correct expression with a simple SELECT query.
Example:
Table [Greek]
[ID] [FldNam] [Headr]
1 CustID Kodikos pelati
2 CustFname Onoma pelati
3 CustLName Eponimo pelati
4 ProdID Kodikos proiodos
...

public sub ColHeadears (string tbl)
'Let's assume, the data with which you fill the grid is held in a recordset named rs, with an open ADODB connection named "con"
Dim i as integer, tmp 
for i = 0 to rs.fields.count
   tmp=con.execute("SELECT Headr FROM Greek WHERE FldNam=" & rs.fields(i).name
   frmAllCust.flxResults.TextMatrix(0, i)=tmp.fields("Headr")
next i

end sub

Does this solve your problem?


Cheerio,
Andy

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[JerryKlmns]

It 's nice to read from greece

You could always make an alias of the field in your SQL statement!

SELECT mYuGlYfieLD_nAMe1 As ELLADARA,
mYuGlYfieLD_nAMe2 As OLE
FROM ENAN_PINAKA_KALE;

 

[angelpr23]

Thank you very much all of you, i liked the solution of Makeitso i think is the best way to do this. I will try it and i will tell you my impressions. About where statement i am thinking to check the tbl name with if statement and hold it in a string variable too, named WhereClause so i think that it will works.

Thank you
very much all.

 

[angelpr23]

Also, i dont know how to use alias in my program..

 

[Andrzejek]

Alias is your way to name a Field, or a table in an SQL

To use your name of a Field (Aliases are in [blue]BLUE[/blue]):

SELECT CustID [blue]As Kodikos_pelati[/blue], CustFName [blue]As Onoma_pelati[/blue], CustLName [blue]As Eponimo_pelati[/blue] FROM customers WHERE....

Aliases for a Tables names are easy:

SELECT CustID As Kodikos_pelati, CustFName As Onoma_pelati, CustLName As Eponimo_pelati FROM customers [blue]MyNameOfTable [/blue]WHERE....

Have fun.

---- Andy

 

[angelpr23]

Thank all of you so much, i finally followed MakeItSo instructions and it now works fine. I think that was the best solution for me than the others.