Form Continues of Crossed Reference.....

  • Thread starter Thread starter Frank Dulk
  • Start date Start date
F

Frank Dulk

I am using the structure of a code, that is to name the origin of the
controls and labels in time of execution....

It follows the code


Dim rs As Recordset, I As Integer

Set rs = Me.RecordsetClone ' Define o recordset.
With rs

For I = 0 To .Fields.Count - 1
Me("uc" & I).ControlSource = .Fields(I).Name


Select Case I
Case 0, 1
Me("u" & I).Caption = .Fields(I).Name
Case 2
Me("u" & I).Caption = .Fields(I).Name
Case 3
Me("u" & I).Caption = .Fields(I).Name
Case 4
Me("u" & I).Caption = .Fields(I).Name
Case 5
Me("u" & I).Caption = .Fields(I).Name
Case 6
Me("u" & I).Caption = .Fields(I).Name
Case 7
Me("u" & I).Caption = .Fields(I).Name
Case 8
Me("u" & I).Caption = .Fields(I).Name
Case 9
Me("u" & I).Caption = .Fields(I).Name
Case 10
Me("u" & I).Caption = .Fields(I).Name
Case 11
Me("u" & I).Caption = .Fields(I).Name
Case 12
Me("u" & I).Caption = .Fields(I).Name
Case 13
Me("u" & I).Caption = .Fields(I).Name
Case 14
Me("u" & I).Caption = .Fields(I).Name
Case 15
Me("u" & I).Caption = .Fields(I).Name
Case 16
Me("u" & I).Caption = .Fields(I).Name
Case Else
MsgBox "Entre em contato"
End Select

Me("uc" & I).Visible = True
Me("u" & I).Visible = True
Next I


End With

rs.Close
Set rs = Nothing




however I am trying to embed in this code, a way to name in the baseboard of
the form them origin of the totals of each column...



The fields of the totals have numbering similar empty space from 0 to 16 and
nominated with t....

how to do to catch the columns in the redordset, to create her formulates...
=Soma([colunarespectiva]) and it updates that in the controls t0 ...t16


if somebody has an idea....
 
Hi Frank,

I'm taking a bit of a chance here - I'm not totally confident in my
understanding of your English. Which is not a criticism, since my (fill in
the non-English blanks) is virtually non-existent.

However, I think you are trying to compute totals for a 16-column summary,
where the names of the fields corresponding to each column are only known at
run-time.

I guess your code sample is intended to be in the Open event of the report.
If the report is to be based on a dynamic recordset, then you will need some
way to declare the SQL string, query, table. While a report does not support
the RecordsetClone property (only a form does this - at least on any Access
that I have), you can get the field list from the recordset info that the
report is to be based on. If you have anything later than Access 2000, you
can pass the recordsource details through to the report using OpenArgs. For
Access 2000 or earlier, you would need to either pick up the contents of a
control in a known-to-be-open form, or call a public function/sub that has
access to the global variable in which the recordsource details are held.

My amended version is below... who knows - it might even work.

Good luck
CD

' as ever, completely untested ...
Private Sub Report_Open(Cancel As Integer)

Const max_controls = 16 ' maximum number of columns in report

Dim rs As DAO.Recordset, fld As DAO.Field
Dim i%, sql$, allowTotal As Boolean
sql$ = Me.OpenArgs
' or sql$ = Forms![Recordset Selector form]![Recordset specification]
' or sql$ = Function_that_delivers_the Required_Specification
Set rs = CurrentDb.OpenRecordset(sql$, dbOpenDynaset)
For Each fld In rs.Fields
i = i + 1: If i > max_controls Then Exit For
allowTotal = False
Select Case fld.Type
Case dbText, dbMemo
Case dbDate, dbTime, dbTimeStamp
Case dbVarBinary, dbLongBinary, dbGUID
Case Else
allowTotal = True
End Select
Me("uc" & CStr(i)).ControlSource = fld.Name
Me("u" & CStr(i)).Caption = fld.Name
Me("uc" & CStr(i)).Visible = True ' this implies that all columns
are initially invisible ??
Me("u" & CStr(i)).Visible = True
If allowTotal Then
Me("t" & CStr(i)).ControlSource = "=Sum([" & fld.Name & "])"
Me("t" & CStr(i)).Visible = True
End If
Next
rs.Close
Set rs = Nothing
Me.RecordSource = sql$
End Sub



Frank Dulk said:
I am using the structure of a code, that is to name the origin of the
controls and labels in time of execution....

It follows the code


Dim rs As Recordset, I As Integer

Set rs = Me.RecordsetClone ' Define o recordset.
With rs

For I = 0 To .Fields.Count - 1
Me("uc" & I).ControlSource = .Fields(I).Name


Select Case I
Case 0, 1
Me("u" & I).Caption = .Fields(I).Name
Case 2
Me("u" & I).Caption = .Fields(I).Name
Case 3
Me("u" & I).Caption = .Fields(I).Name
Case 4
Me("u" & I).Caption = .Fields(I).Name
Case 5
Me("u" & I).Caption = .Fields(I).Name
Case 6
Me("u" & I).Caption = .Fields(I).Name
Case 7
Me("u" & I).Caption = .Fields(I).Name
Case 8
Me("u" & I).Caption = .Fields(I).Name
Case 9
Me("u" & I).Caption = .Fields(I).Name
Case 10
Me("u" & I).Caption = .Fields(I).Name
Case 11
Me("u" & I).Caption = .Fields(I).Name
Case 12
Me("u" & I).Caption = .Fields(I).Name
Case 13
Me("u" & I).Caption = .Fields(I).Name
Case 14
Me("u" & I).Caption = .Fields(I).Name
Case 15
Me("u" & I).Caption = .Fields(I).Name
Case 16
Me("u" & I).Caption = .Fields(I).Name
Case Else
MsgBox "Entre em contato"
End Select

Me("uc" & I).Visible = True
Me("u" & I).Visible = True
Next I


End With

rs.Close
Set rs = Nothing




however I am trying to embed in this code, a way to name in the baseboard of
the form them origin of the totals of each column...



The fields of the totals have numbering similar empty space from 0 to 16 and
nominated with t....

how to do to catch the columns in the redordset, to create her formulates...
=Soma([colunarespectiva]) and it updates that in the controls t0 ...t16


if somebody has an idea....
Click to expand...
 
From where did I get the idea you were building a report?

Don't know.

Anyway, a form is not that different, just a bit simpler.
Back to "plan A" and use the RecordsetClone - but only after you have done
the assign of the RecordSource.
Or stick with plan B; not much difference.

All of the other considerations remain.
 
Thank you, I am really doing for a form, you would not have an example.

Thank you very much.
 
Frank,

As I hinted, the coding for a form is almost identical.

Try working with the sample below as a start point.

CD

' as ever, completely untested ...
Private Sub Form_Open(Cancel As Integer)

Const max_controls = 16 ' maximum number of columns in form

' make sure that the RecordSource is as you want it
' before going on to the section below ...
' ... it might be that you have a cunning query that delivers dynamic
' columns or a SQL string passed as OpenArgs or whatever

Dim fld As DAO.Field
Dim i%, allowTotal As Boolean
For Each fld In Me.RecordsetClone.Fields
i = i + 1: If i > max_controls Then Exit For
allowTotal = False
Select Case fld.Type
Case dbText, dbMemo
Case dbDate, dbTime, dbTimeStamp
Case dbVarBinary, dbLongBinary, dbGUID
Case Else
allowTotal = True
End Select
Me("uc" & CStr(i)).ControlSource = fld.Name
Me("u" & CStr(i)).Caption = fld.Name
Me("uc" & CStr(i)).Visible = True ' this implies that all columns
' are initially
invisible ??
Me("u" & CStr(i)).Visible = True
If allowTotal Then
Me("t" & CStr(i)).ControlSource = "=Sum([" & fld.Name & "])"
Me("t" & CStr(i)).Visible = True
End If
Next
End Sub
 
Back
Top