How to populate a textbox in a report with a macro command button ?

[Telesphore]

Function EquivalenceDateObtentionDipl(Command)
Select Case Command
Case "PL"
EquivalenceDateObtentionDipl = DatePLObtention
Case "MDiv"
EquivalenceDateObtentionDipl = DateMDivObtention
etc...

Trying this Function, I am wondering if and how I can use a Command Button:
"Command" to populate a textbox in a report?

In English now: using a Command Button named "PL"in a frmStudentChoice that
commands a macro "macFrmChoixÉtudiant.cmdPL_Click" which
lauches the report etBulletinNotesGO with a textbox will reads the date of
obtention of a diploma in PL.

Thanks in advance

 

[Ken Snell]

You cannot write data to a textbox on a report. A report will only show data
that are in the query/table that is the report's RecordSource, or that can
be "calculated" by an expression in the textbox's ControlSource property.
It's possible to have a report "look up" a value from an open form, so if
you had a textbox on a form named EquivalenceDateObtentionDipl and that form
is open when the report is open, you could use an expression similar to this
to get the value from that form's textbox:

=Forms!NameOfYourOpenForm!EquivalenceDateObtentionDipl

 

[Telesphore]

Thank you

I am wondering now how to do what I want.
I'll explain it better:

In a table tblDiplomas we have these fields:
DateBACumulCertifObtention
DateBAMajPhMinCPFObtention
DateBAMajPhMinThObtention
DateBAMajThMinPhObtention
DateBPhObtention
DateBThIFTMObtention
DateBThObtention
DateGOGénéral
DateMAJCPHObtention
DateMAJCTHObtention
DateMDivObtention
DateObtentionCertif1
DateObtentionCertif2
DateObtentionCertif3
DatePFObtention
DatePFSPObtention
DatePFSPTHObtention
DatePHSPPFObtention
DatePLObtention
DatePLTHObtention
DateSPObtention
DateTHPFPHObtention
DateTHSPObtention
DateTHSPPFPLObtention

indicating the day when the diploma was obtained.

In the report rptBulletinNotesGO for each student, we have a textbox in
which we want to read the day when the diploma was obtained.

The report rptBulletinNotesGO is launched through a form frmChoiceStudent
with a serie of macro command button Macros!macFrmChoiceStudent... according
to the different type of diploma.

For example, if we want the DatePLObtention to appear in the textbox, then
we would launch the button Macros!macFrmChoiceStudent!cmdPL_Click.

I hope I made it clear!!!!

 

[Ken Snell]

From the list of fields, I have no idea which one (or ones?) contains the
date that you seek? Are you saying that you have a different macro for each
type of diploma? Do all those macros open the same report?

Your table structure is nonnormalized. What your tblDiplomas table should
contain is these fields:

tblDiplomas
DiplomaID
DiplomaType
DiplomaDate
StudentID (assuming that each Diploma is given to a student or some
other person)

This will greatly simplify your table and data structure, and make it very
easy to find the data you want. But, as I noted above, it's not clear at all
how you are deciding which date value you want?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

 

[Telesphore]

"Ken Snell" message:

From the list of fields, I have no idea which one (or ones?) contains the
date that you seek?

Each one of these fields, DateBPhObtention, DateBThIFTMObtention,
DateBThObtention, etc... may contain a different date according to a
different diploma, date I am seeking for the report.

Are you saying that you have a different macro for each type of diploma?

I am trying to have a different macro, because these different macros used
to open a respective different report according to the different dates. The
reason is I wanted to reduce the number of the macros.

Do all those macros open the same report?

Yes the same rptBulletinNotesGO report, but with a different Condition Where
in each macro.

Your table structure is nonnormalized. What your tblDiplomas table should
contain is these fields:

tblDiplomas
DiplomaID
DiplomaType
DiplomaDate
StudentID (assuming that each Diploma is given to a student or
some other person)

This will greatly simplify your table and data structure, and make it very
easy to find the data you want. But, as I noted above, it's not clear at
all how you are deciding which date value you want?

Somebody created this table using trying to unify the fields which were in
the tblStudents.

It seems then that it is impossible to associate a Macro with a Fied data in
VBA.
If it is, then I'll have to normalize the tblDiplomas as your are saying.

 

[Ken Snell]

I didn't say it's impossible to associate a macro with a field. You can do
this via a DLookup expression in the Where argument of the OpenReport macro
action. As an example:

=DLookup("NameOfField", "NameOfTable", "PrimaryKey = " &
[NameOfIDControlOnForm])

It's just not clear to me what you want to accomplish. Perhaps if you give
specific examples of data, actions, and form layout?

Normalizing the table will be a good thing to do, regardless.

 

[Telesphore]

Maybe I should have explain it in the first place.

In a textbox of the report "rptBulletinNotesGO", which is a summary of the
marks that a student has received at the end of his studies, we want to
inscribe the date of the obtention of the diploma "Date...Obtention".

For example, in the bulletin of the notes of Paul Smith (rptBulletinNotesGO)
we will see the date of his MDiv studies diploma (DateMDivObtention)

I didn't say it's impossible to associate a macro with a field. You can do
this via a DLookup expression in the Where argument of the OpenReport macro
action. As an example:

=DLookup("NameOfField", "NameOfTable", "PrimaryKey = " &
[NameOfIDControlOnForm])

It's just not clear to me what you want to accomplish. Perhaps if you give
specific examples of data, actions, and form layout?

Normalizing the table will be a good thing to do, regardless.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

"Ken Snell" message:
Each one of these fields, DateBPhObtention, DateBThIFTMObtention,
DateBThObtention, etc... may contain a different date according to a
different diploma, date I am seeking for the report.

I am trying to have a different macro, because these different macros
used to open a respective different report according to the different
dates. The reason is I wanted to reduce the number of the macros.

Yes the same rptBulletinNotesGO report, but with a different Condition
Where in each macro.


Somebody created this table using trying to unify the fields which were
in the tblStudents.

It seems then that it is impossible to associate a Macro with a Fied data
in VBA.
If it is, then I'll have to normalize the tblDiplomas as your are saying.

 

[Ken Snell]

You can use the Case block statement in the Format event procedure for the
section of the report (probably the Detail section) that contains the
textbox that will display the desired date when the diploma was attained.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Maybe I should have explain it in the first place.

In a textbox of the report "rptBulletinNotesGO", which is a summary of the
marks that a student has received at the end of his studies, we want to
inscribe the date of the obtention of the diploma "Date...Obtention".

For example, in the bulletin of the notes of Paul Smith
(rptBulletinNotesGO) we will see the date of his MDiv studies diploma
(DateMDivObtention)

I didn't say it's impossible to associate a macro with a field. You can do
this via a DLookup expression in the Where argument of the OpenReport
macro action. As an example:

=DLookup("NameOfField", "NameOfTable", "PrimaryKey = " &
[NameOfIDControlOnForm])

It's just not clear to me what you want to accomplish. Perhaps if you
give specific examples of data, actions, and form layout?

Normalizing the table will be a good thing to do, regardless.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

"Ken Snell" message:
From the list of fields, I have no idea which one (or ones?) contains
the date that you seek?
Each one of these fields, DateBPhObtention, DateBThIFTMObtention,
DateBThObtention, etc... may contain a different date according to a
different diploma, date I am seeking for the report.

Are you saying that you have a different macro for each type of diploma?
I am trying to have a different macro, because these different macros
used to open a respective different report according to the different
dates. The reason is I wanted to reduce the number of the macros.

Do all those macros open the same report?
Yes the same rptBulletinNotesGO report, but with a different Condition
Where in each macro.

Your table structure is nonnormalized. What your tblDiplomas table
should contain is these fields:

tblDiplomas
DiplomaID
DiplomaType
DiplomaDate
StudentID (assuming that each Diploma is given to a student or
some other person)

This will greatly simplify your table and data structure, and make it
very easy to find the data you want. But, as I noted above, it's not
clear at all how you are deciding which date value you want?

Somebody created this table using trying to unify the fields which were
in the tblStudents.

It seems then that it is impossible to associate a Macro with a Fied
data in VBA.
If it is, then I'll have to normalize the tblDiplomas as your are
saying.


-- > Ken Snell
http://www.accessmvp.com/KDSnell/





Thank you

I am wondering now how to do what I want.
I'll explain it better:

In a table tblDiplomas we have these fields:
...
DateBPhObtention
DateBThIFTMObtention
DateBThObtention
DateGOGénéral
DateMAJCPHObtention
DateMAJCTHObtention
DateMDivObtention
DateObtentionCertif1
DateObtentionCertif2
DateObtentionCertif3
DatePFObtention
DatePFSPObtention
DatePFSPTHObtention
DatePHSPPFObtention
DatePLObtention
DatePLTHObtention
DateSPObtention
DateTHPFPHObtention
DateTHSPObtention
DateTHSPPFPLObtention

indicating the day when the diploma was obtained.

In the report rptBulletinNotesGO for each student, we have a textbox
in which we want to read the day when the diploma was obtained.

The report rptBulletinNotesGO is launched through a form
frmChoiceStudent with a serie of macro command button
Macros!macFrmChoiceStudent... according to the different type of
diploma.

For example, if we want the DatePLObtention to appear in the textbox,
then we would launch the button
Macros!macFrmChoiceStudent!cmdPL_Click.

I hope I made it clear!!!!



"Ken Snell" <[email protected]> a écrit dans le message
de You cannot write data to a textbox on a report. A report will only
show data that are in the query/table that is the report's
RecordSource, or that can be "calculated" by an expression in the
textbox's ControlSource property. It's possible to have a report
"look up" a value from an open form, so if you had a textbox on a
form named EquivalenceDateObtentionDipl and that form is open when
the report is open, you could use an expression similar to this to
get the value from that form's textbox:

=Forms!NameOfYourOpenForm!EquivalenceDateObtentionDipl

--

Ken Snell
http://www.accessmvp.com/KDSnell/




Function EquivalenceDateObtentionDipl(Command)
Select Case Command
Case "PL"
EquivalenceDateObtentionDipl = DatePLObtention
Case "MDiv"
EquivalenceDateObtentionDipl = DateMDivObtention
etc...

Trying this Function, I am wondering if and how I can use a Command
Button: "Command" to populate a textbox in a report?

In English now: using a Command Button named "PL"in a
frmStudentChoice that commands a macro
"macFrmChoixÉtudiant.cmdPL_Click" which
lauches the report etBulletinNotesGO with a textbox will reads the
date of obtention of a diploma in PL.

Thanks in advance