Hide Subforms until Date Range in Main Form established

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

There is a form with about 10 sub forms in it.
The fields in the sub forms are calculations of days <DateDiff(“d†etc.
etc.> in a previously given date range, like From 04/01/06 Till 04/11/06
The form has an unbound date field “From:†and one “Till:â€
When opening the form and before the user filled in the unbound date fields,
all the calculation fields in the sub form show #Error (naturally; no date
range has been given).
How could I hide the sub forms until the date range is established?

Or

When using a unbound form with unbound date fields, how I then call the Form
“Days for Repair used†after the date range has been established by entering
it in this unbound fields?

Thank you for any help.
Bernhard
 
Sorry, an other “ORâ€

Or

How do I get rid of this #Error business before the date range is established?

Thanks
Bernhard
 
Are the subform fields bound? Is it the Default Value that is causing the
problem? It is unclear to me what you are doing. In any case, you might try
changing the calculation for your subforms to a function that will test the
unbound fields in your main form:

Function ChkValidDates() As Variant
If IsNull(Forms!MainForm!txtFromDate) or
IsNull(Forms!MainForm!txtToDate) _
Then
ChkValidDates = Null
Else
ChkValidDates = 'Do your calculation here
End If
End Function
 
Thank you for answerimg.

Yes, the fields are bound; bound to a table and the SQL statement, fort the
parent form and consequently the sub forms also is the fllwg:

strSql = "SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged, NZ(DateDiff('d',[Admission Date],[Discharged])+1,0) AS
DaysDischarged, (DateDiff('d',[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff('d',[Admission Date],Date())+1) AS
DaysAdmTillToday, NZ([DaysDischargedTillToday]+[DaysDischarged],0) AS
Substract, [AD Absent Sick].Clinic FROM [AD Absent Sick]" & "where ([AD
Absent Sick].[Admission Date] Between " & Format(Me.StartDate, strcJetDate) &
"And" & Format(Me.EndDate, strcJetDate) & ")"

In addition for the sub forms:

Me.[sbfGFW].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='GFW');"
Me.[sbfHOH].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='HOH');"
Me.[sbfILL].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='ILL');"
Etc. etc.

Hope this makes sens to you.
Bernard
 
Start by making the visible properties of the sub forms false.
Then check in the After Update event of both date controls to be sure both
are valid dates. If they are, make the sub forms visible; otherwise, display
an appropriate error message.

Bernard said:
Thank you for answerimg.

Yes, the fields are bound; bound to a table and the SQL statement, fort the
parent form and consequently the sub forms also is the fllwg:

strSql = "SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged, NZ(DateDiff('d',[Admission Date],[Discharged])+1,0) AS
DaysDischarged, (DateDiff('d',[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff('d',[Admission Date],Date())+1) AS
DaysAdmTillToday, NZ([DaysDischargedTillToday]+[DaysDischarged],0) AS
Substract, [AD Absent Sick].Clinic FROM [AD Absent Sick]" & "where ([AD
Absent Sick].[Admission Date] Between " & Format(Me.StartDate, strcJetDate) &
"And" & Format(Me.EndDate, strcJetDate) & ")"

In addition for the sub forms:

Me.[sbfGFW].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='GFW');"
Me.[sbfHOH].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='HOH');"
Me.[sbfILL].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='ILL');"
Etc. etc.

Hope this makes sens to you.
Bernard


Klatuu said:
Are the subform fields bound? Is it the Default Value that is causing the
problem? It is unclear to me what you are doing. In any case, you might try
changing the calculation for your subforms to a function that will test the
unbound fields in your main form:

Function ChkValidDates() As Variant
If IsNull(Forms!MainForm!txtFromDate) or
IsNull(Forms!MainForm!txtToDate) _
Then
ChkValidDates = Null
Else
ChkValidDates = 'Do your calculation here
End If
End Function
Click to expand...
Click to expand...
 
Thank you,

First of all: my apology for not answering earlier. However, while your
answer came in, I was transiting from my volunteer work at the American Red
Cross back to my home.
However I saw immediately, I am still lost.
On those unbound date control only the 2nd one, the “Till†control field
(EndDate in the SQL Statement), has an After Update Event. Since after
entering the Till date, the information on the form should act according to
the date range. The After Update Event is in regard to the SQL Statement
where it says:

"where ([AD Absent Sick].[Admission Date] Between " & Format(Me.StartDate,
strcJetDate) & "And" & Format(Me.EndDate, strcJetDate) & ")"

This last statement comes after constituting, what Discharge, Admission till
today etc. should mean. (First line of my posted SQL statement).

The same date range then applies to the sub forms also.

I certainly followed your advice and did set the visible property of the sub
forms to NO. I then called the form. But what then?
The After Update Event of the “From†Date control showed nothing and the
After Update Event of the “From†Event showed the SQL statements as described
in my former posting.

I then filled in a “From†and “Till†date.
Where should the date then have sown up, as the After Update Events did not
change?

Sorry, to be a nuisance but it is hard to understand what is going on
without very much knowledge of coding. Up to now nearly every data base has
been done by me with some wizards. Now, for the first, time I have to relay
to programming. So far it went well, but I am stuck now.

Actually, what I want is, to get rid of this darn #Error statements when the
form goes on focus and the requested date range has to be stated in the From
and Till Date fields after opening the form.

Thank you for coping with me
Bernard


Klatuu said:
Start by making the visible properties of the sub forms false.
Then check in the After Update event of both date controls to be sure both
are valid dates. If they are, make the sub forms visible; otherwise, display
an appropriate error message.

Bernard said:
Thank you for answerimg.

Yes, the fields are bound; bound to a table and the SQL statement, fort the
parent form and consequently the sub forms also is the fllwg:

strSql = "SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged, NZ(DateDiff('d',[Admission Date],[Discharged])+1,0) AS
DaysDischarged, (DateDiff('d',[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff('d',[Admission Date],Date())+1) AS
DaysAdmTillToday, NZ([DaysDischargedTillToday]+[DaysDischarged],0) AS
Substract, [AD Absent Sick].Clinic FROM [AD Absent Sick]" & "where ([AD
Absent Sick].[Admission Date] Between " & Format(Me.StartDate, strcJetDate) &
"And" & Format(Me.EndDate, strcJetDate) & ")"

In addition for the sub forms:

Me.[sbfGFW].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='GFW');"
Me.[sbfHOH].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='HOH');"
Me.[sbfILL].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='ILL');"
Etc. etc.

Hope this makes sens to you.
Bernard


Klatuu said:
Are the subform fields bound? Is it the Default Value that is causing the
problem? It is unclear to me what you are doing. In any case, you might try
changing the calculation for your subforms to a function that will test the
unbound fields in your main form:

Function ChkValidDates() As Variant
If IsNull(Forms!MainForm!txtFromDate) or
IsNull(Forms!MainForm!txtToDate) _
Then
ChkValidDates = Null
Else
ChkValidDates = 'Do your calculation here
End If
End Function
:

Hi,

There is a form with about 10 sub forms in it.
The fields in the sub forms are calculations of days <DateDiff(“d†etc.
etc.> in a previously given date range, like From 04/01/06 Till 04/11/06
The form has an unbound date field “From:†and one “Till:â€
When opening the form and before the user filled in the unbound date fields,
all the calculation fields in the sub form show #Error (naturally; no date
range has been given).
How could I hide the sub forms until the date range is established?

Or

When using a unbound form with unbound date fields, how I then call the Form
“Days for Repair used†after the date range has been established by entering
it in this unbound fields?

Thank you for any help.
Bernhard
Click to expand...
Click to expand...
Click to expand...
 
I think it may be necessary to requery the subform after the dates are
entered, but before you make it visible. Try putting this in the code just
before you set the subform property to visible:
Me.MySubFormName.Requery

Bernard said:
Thank you,

First of all: my apology for not answering earlier. However, while your
answer came in, I was transiting from my volunteer work at the American Red
Cross back to my home.
However I saw immediately, I am still lost.
On those unbound date control only the 2nd one, the “Till†control field
(EndDate in the SQL Statement), has an After Update Event. Since after
entering the Till date, the information on the form should act according to
the date range. The After Update Event is in regard to the SQL Statement
where it says:

"where ([AD Absent Sick].[Admission Date] Between " & Format(Me.StartDate,
strcJetDate) & "And" & Format(Me.EndDate, strcJetDate) & ")"

This last statement comes after constituting, what Discharge, Admission till
today etc. should mean. (First line of my posted SQL statement).

The same date range then applies to the sub forms also.

I certainly followed your advice and did set the visible property of the sub
forms to NO. I then called the form. But what then?
The After Update Event of the “From†Date control showed nothing and the
After Update Event of the “From†Event showed the SQL statements as described
in my former posting.

I then filled in a “From†and “Till†date.
Where should the date then have sown up, as the After Update Events did not
change?

Sorry, to be a nuisance but it is hard to understand what is going on
without very much knowledge of coding. Up to now nearly every data base has
been done by me with some wizards. Now, for the first, time I have to relay
to programming. So far it went well, but I am stuck now.

Actually, what I want is, to get rid of this darn #Error statements when the
form goes on focus and the requested date range has to be stated in the From
and Till Date fields after opening the form.

Thank you for coping with me
Bernard


Klatuu said:
Start by making the visible properties of the sub forms false.
Then check in the After Update event of both date controls to be sure both
are valid dates. If they are, make the sub forms visible; otherwise, display
an appropriate error message.

Bernard said:
Thank you for answerimg.

Yes, the fields are bound; bound to a table and the SQL statement, fort the
parent form and consequently the sub forms also is the fllwg:

strSql = "SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged, NZ(DateDiff('d',[Admission Date],[Discharged])+1,0) AS
DaysDischarged, (DateDiff('d',[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff('d',[Admission Date],Date())+1) AS
DaysAdmTillToday, NZ([DaysDischargedTillToday]+[DaysDischarged],0) AS
Substract, [AD Absent Sick].Clinic FROM [AD Absent Sick]" & "where ([AD
Absent Sick].[Admission Date] Between " & Format(Me.StartDate, strcJetDate) &
"And" & Format(Me.EndDate, strcJetDate) & ")"

In addition for the sub forms:

Me.[sbfGFW].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='GFW');"
Me.[sbfHOH].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='HOH');"
Me.[sbfILL].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='ILL');"
Etc. etc.

Hope this makes sens to you.
Bernard


:

Are the subform fields bound? Is it the Default Value that is causing the
problem? It is unclear to me what you are doing. In any case, you might try
changing the calculation for your subforms to a function that will test the
unbound fields in your main form:

Function ChkValidDates() As Variant
If IsNull(Forms!MainForm!txtFromDate) or
IsNull(Forms!MainForm!txtToDate) _
Then
ChkValidDates = Null
Else
ChkValidDates = 'Do your calculation here
End If
End Function
:

Hi,

There is a form with about 10 sub forms in it.
The fields in the sub forms are calculations of days <DateDiff(“d†etc.
etc.> in a previously given date range, like From 04/01/06 Till 04/11/06
The form has an unbound date field “From:†and one “Till:â€
When opening the form and before the user filled in the unbound date fields,
all the calculation fields in the sub form show #Error (naturally; no date
range has been given).
How could I hide the sub forms until the date range is established?

Or

When using a unbound form with unbound date fields, how I then call the Form
“Days for Repair used†after the date range has been established by entering
it in this unbound fields?

Thank you for any help.
Bernhard
Click to expand...
Click to expand...
Click to expand...
 
Klatuu,

I sincerely thank you for trying to get me in line. However I certainly am
too inexperienced to understand, what you are suggesting.
It is not your business to tutor a guy who should get his information from
some Access developing computer classes.
Up to now, I understood nearly all the necessary steps to get a program
running, but this time I am just lost.
I am checking the Discussion Group again and perhaps some other answer on a
similar question gets me an idea, what is going on.
Your time and knowledge and that of all other persons trying to help out is
too precious as to spend it on someone who should first start learning and
then asking.
Thank you again; your involvement is highly appreciated.
Regards
Bernard.
P.S. in German I would say, "ich streiche die Segel" meaning rafting the
sails; I am giving up.

Klatuu said:
I think it may be necessary to requery the subform after the dates are
entered, but before you make it visible. Try putting this in the code just
before you set the subform property to visible:
Me.MySubFormName.Requery

Bernard said:
Thank you,

First of all: my apology for not answering earlier. However, while your
answer came in, I was transiting from my volunteer work at the American Red
Cross back to my home.
However I saw immediately, I am still lost.
On those unbound date control only the 2nd one, the “Till†control field
(EndDate in the SQL Statement), has an After Update Event. Since after
entering the Till date, the information on the form should act according to
the date range. The After Update Event is in regard to the SQL Statement
where it says:

"where ([AD Absent Sick].[Admission Date] Between " & Format(Me.StartDate,
strcJetDate) & "And" & Format(Me.EndDate, strcJetDate) & ")"

This last statement comes after constituting, what Discharge, Admission till
today etc. should mean. (First line of my posted SQL statement).

The same date range then applies to the sub forms also.

I certainly followed your advice and did set the visible property of the sub
forms to NO. I then called the form. But what then?
The After Update Event of the “From†Date control showed nothing and the
After Update Event of the “From†Event showed the SQL statements as described
in my former posting.

I then filled in a “From†and “Till†date.
Where should the date then have sown up, as the After Update Events did not
change?

Sorry, to be a nuisance but it is hard to understand what is going on
without very much knowledge of coding. Up to now nearly every data base has
been done by me with some wizards. Now, for the first, time I have to relay
to programming. So far it went well, but I am stuck now.

Actually, what I want is, to get rid of this darn #Error statements when the
form goes on focus and the requested date range has to be stated in the From
and Till Date fields after opening the form.

Thank you for coping with me
Bernard


Klatuu said:
Start by making the visible properties of the sub forms false.
Then check in the After Update event of both date controls to be sure both
are valid dates. If they are, make the sub forms visible; otherwise, display
an appropriate error message.

:

Thank you for answerimg.

Yes, the fields are bound; bound to a table and the SQL statement, fort the
parent form and consequently the sub forms also is the fllwg:

strSql = "SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged, NZ(DateDiff('d',[Admission Date],[Discharged])+1,0) AS
DaysDischarged, (DateDiff('d',[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff('d',[Admission Date],Date())+1) AS
DaysAdmTillToday, NZ([DaysDischargedTillToday]+[DaysDischarged],0) AS
Substract, [AD Absent Sick].Clinic FROM [AD Absent Sick]" & "where ([AD
Absent Sick].[Admission Date] Between " & Format(Me.StartDate, strcJetDate) &
"And" & Format(Me.EndDate, strcJetDate) & ")"

In addition for the sub forms:

Me.[sbfGFW].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='GFW');"
Me.[sbfHOH].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='HOH');"
Me.[sbfILL].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='ILL');"
Etc. etc.

Hope this makes sens to you.
Bernard


:

Are the subform fields bound? Is it the Default Value that is causing the
problem? It is unclear to me what you are doing. In any case, you might try
changing the calculation for your subforms to a function that will test the
unbound fields in your main form:

Function ChkValidDates() As Variant
If IsNull(Forms!MainForm!txtFromDate) or
IsNull(Forms!MainForm!txtToDate) _
Then
ChkValidDates = Null
Else
ChkValidDates = 'Do your calculation here
End If
End Function
:

Hi,

There is a form with about 10 sub forms in it.
The fields in the sub forms are calculations of days <DateDiff(“d†etc.
etc.> in a previously given date range, like From 04/01/06 Till 04/11/06
The form has an unbound date field “From:†and one “Till:â€
When opening the form and before the user filled in the unbound date fields,
all the calculation fields in the sub form show #Error (naturally; no date
range has been given).
How could I hide the sub forms until the date range is established?

Or

When using a unbound form with unbound date fields, how I then call the Form
“Days for Repair used†after the date range has been established by entering
it in this unbound fields?

Thank you for any help.
Bernhard
Click to expand...
Click to expand...
Click to expand...
 
I don't mind helping at all. This news group if for the purpose of helping
everyone regardless of experience or knowledge. Do not be afraid to ask any
question. I have probably been helped by others more than I have helped.

Don't give up.

Bernard said:
Klatuu,

I sincerely thank you for trying to get me in line. However I certainly am
too inexperienced to understand, what you are suggesting.
It is not your business to tutor a guy who should get his information from
some Access developing computer classes.
Up to now, I understood nearly all the necessary steps to get a program
running, but this time I am just lost.
I am checking the Discussion Group again and perhaps some other answer on a
similar question gets me an idea, what is going on.
Your time and knowledge and that of all other persons trying to help out is
too precious as to spend it on someone who should first start learning and
then asking.
Thank you again; your involvement is highly appreciated.
Regards
Bernard.
P.S. in German I would say, "ich streiche die Segel" meaning rafting the
sails; I am giving up.

Klatuu said:
I think it may be necessary to requery the subform after the dates are
entered, but before you make it visible. Try putting this in the code just
before you set the subform property to visible:
Me.MySubFormName.Requery

Bernard said:
Thank you,

First of all: my apology for not answering earlier. However, while your
answer came in, I was transiting from my volunteer work at the American Red
Cross back to my home.
However I saw immediately, I am still lost.
On those unbound date control only the 2nd one, the “Till†control field
(EndDate in the SQL Statement), has an After Update Event. Since after
entering the Till date, the information on the form should act according to
the date range. The After Update Event is in regard to the SQL Statement
where it says:

"where ([AD Absent Sick].[Admission Date] Between " & Format(Me.StartDate,
strcJetDate) & "And" & Format(Me.EndDate, strcJetDate) & ")"

This last statement comes after constituting, what Discharge, Admission till
today etc. should mean. (First line of my posted SQL statement).

The same date range then applies to the sub forms also.

I certainly followed your advice and did set the visible property of the sub
forms to NO. I then called the form. But what then?
The After Update Event of the “From†Date control showed nothing and the
After Update Event of the “From†Event showed the SQL statements as described
in my former posting.

I then filled in a “From†and “Till†date.
Where should the date then have sown up, as the After Update Events did not
change?

Sorry, to be a nuisance but it is hard to understand what is going on
without very much knowledge of coding. Up to now nearly every data base has
been done by me with some wizards. Now, for the first, time I have to relay
to programming. So far it went well, but I am stuck now.

Actually, what I want is, to get rid of this darn #Error statements when the
form goes on focus and the requested date range has to be stated in the From
and Till Date fields after opening the form.

Thank you for coping with me
Bernard


:

Start by making the visible properties of the sub forms false.
Then check in the After Update event of both date controls to be sure both
are valid dates. If they are, make the sub forms visible; otherwise, display
an appropriate error message.

:

Thank you for answerimg.

Yes, the fields are bound; bound to a table and the SQL statement, fort the
parent form and consequently the sub forms also is the fllwg:

strSql = "SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged, NZ(DateDiff('d',[Admission Date],[Discharged])+1,0) AS
DaysDischarged, (DateDiff('d',[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff('d',[Admission Date],Date())+1) AS
DaysAdmTillToday, NZ([DaysDischargedTillToday]+[DaysDischarged],0) AS
Substract, [AD Absent Sick].Clinic FROM [AD Absent Sick]" & "where ([AD
Absent Sick].[Admission Date] Between " & Format(Me.StartDate, strcJetDate) &
"And" & Format(Me.EndDate, strcJetDate) & ")"

In addition for the sub forms:

Me.[sbfGFW].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='GFW');"
Me.[sbfHOH].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='HOH');"
Me.[sbfILL].Form.RecordSource = strSql & "and ([AD Absent
Sick].Clinic='ILL');"
Etc. etc.

Hope this makes sens to you.
Bernard


:

Are the subform fields bound? Is it the Default Value that is causing the
problem? It is unclear to me what you are doing. In any case, you might try
changing the calculation for your subforms to a function that will test the
unbound fields in your main form:

Function ChkValidDates() As Variant
If IsNull(Forms!MainForm!txtFromDate) or
IsNull(Forms!MainForm!txtToDate) _
Then
ChkValidDates = Null
Else
ChkValidDates = 'Do your calculation here
End If
End Function
:

Hi,

There is a form with about 10 sub forms in it.
The fields in the sub forms are calculations of days <DateDiff(“d†etc.
etc.> in a previously given date range, like From 04/01/06 Till 04/11/06
The form has an unbound date field “From:†and one “Till:â€
When opening the form and before the user filled in the unbound date fields,
all the calculation fields in the sub form show #Error (naturally; no date
range has been given).
How could I hide the sub forms until the date range is established?

Or

When using a unbound form with unbound date fields, how I then call the Form
“Days for Repair used†after the date range has been established by entering
it in this unbound fields?

Thank you for any help.
Bernhard
Click to expand...
Click to expand...
Click to expand...
 
Back
Top