query a cycled child recordtable

[Guest]

Hi

I have a mainform indexed on autonumber I
it has a subform that has an underlying child table indexed on an autonumber ID file als
they are linked as cycled - current record onl
on the main form I have a command button that opens the child subform and another command button that hides it
I want to create a YES/NO field in the mainform_TABLE that indicates whether there are any child records in that subform_child_TABLE....the default value set to NO

So functionally, if a user opens the subform and creates a child record, the value of the mainform_table_YES/NO_field will be changed to YES.......I will display this field next to the command buttons I mentioned so that the user will be able to tell if there are any child records previously created.

TEXTBOX YESNO COMMANDBUTTON OPENSUBFORM COMMANDBUTTON CLOSESUBFOR

How do I change the value of that YESNO field
I was thinking that when the user clicks the COMMANDBUTTON CLOSESUBFOR
some visual basic code could run that

Private sub CLSOESUBFORM_CLICK(
If CHILDTABLE AUTONUMBER ID that equals the currentrecord ID of the current mainform is not nul
THEN mainform!YESNO_filed = "true
close subfor

ELSE close for

----------- But I am not sure of the syntax of the code, nor if this is the best way to handle this.

 

[tina]

i wouldn't put a checkbox in the main table, too many situations where it
might not be updated appropriately. suggest instead you use a calculated
control on the main form to check whether there are records on the subform
and display result. one method: add unbound textbox to main form, set
ControlSource as

=DCount("PrimaryKeyField","SubformTable","ForeignKeyField = " &
Forms!MainForm!PrimaryKeyControlName)

PrimaryKeyField is the field in the subform table, and ForeignKeyField is
the field in the subform table that is linked to the mainform table's
primary key. the above should display zero (0) or the number of subform
records. if you want to stick with Yes/No, amend the calculation as follows

=IIf(DCount("PrimaryKeyField","SubformTable","ForeignKeyField = " &
Forms!MainForm!PrimaryKeyControlName) > 0, "Yes", "No")

in either case, you can requery the unbound textbox on the subform control's
OnExit event, to update the count.

hth

Hi,

I have a mainform indexed on autonumber ID
it has a subform that has an underlying child table indexed on an autonumber ID file also
they are linked as cycled - current record only
on the main form I have a command button that opens the child subform and

another command button that hides it.

I want to create a YES/NO field in the mainform_TABLE that indicates

whether there are any child records in that subform_child_TABLE....the
default value set to NO.

So functionally, if a user opens the subform and creates a child record,

the value of the mainform_table_YES/NO_field will be changed to YES.......I
will display this field next to the command buttons I mentioned so that the
user will be able to tell if there are any child records previously created.

TEXTBOX YESNO COMMANDBUTTON OPENSUBFORM COMMANDBUTTON CLOSESUBFORM


How do I change the value of that YESNO field?
I was thinking that when the user clicks the COMMANDBUTTON CLOSESUBFORM
some visual basic code could run that:


Private sub CLSOESUBFORM_CLICK()
If CHILDTABLE AUTONUMBER ID that equals the currentrecord ID of the current mainform is not null
THEN mainform!YESNO_filed = "true"
close subform

ELSE close form

----------- But I am not sure of the syntax of the code, nor if this is

the best way to handle this.

 

[Guest]

COOL THANK

follow up question

I USED THE IIF(........>0,"CLICK TO VIEW","NONE") AND i DISPLAY THOSE PHRASES
Here's my gliche
THE USER clicks the OPENSUBFORM1 and creates a child recor
The user clicks CLOSESUBFORM1 and that text box should immediately show "click to view" but it doesnot update until I close and reopen the form. SO I PUT Me!textboxname.requery into the CLOSESUBFORM1 VB code but it did not do the trick
What really confuses me is that in another textbox2 I used IIF(ISNULL(otherfield_date),"none","click to view") and for some reason that textbox2 does update immediately after closing its respective subform which updates the date in otherfield_date

Any suggestions on how to update that textbox as soon as the user closes that subform1 ?

 

[tina]

did you put a break on the CLOSESUBFORM1 code and step thru it to see if the
requery line is executing?
if it is, then it *should* work....i hate when that happens! <g>
the only other thing i can suggest is to move out of the subform back into
the main form (just click into another control) *but don't click the
CLOSESUBFORM1 button*. then open your subform's underlying table directly,
from the database window. look for the record you just entered in the
subform. is it there?

COOL THANKS


follow up question:


I USED THE IIF(........>0,"CLICK TO VIEW","NONE") AND i DISPLAY THOSE PHRASES.
Here's my gliche:
THE USER clicks the OPENSUBFORM1 and creates a child record
The user clicks CLOSESUBFORM1 and that text box should immediately show

"click to view" but it doesnot update until I close and reopen the form. SO
I PUT Me!textboxname.requery into the CLOSESUBFORM1 VB code but it did not
do the trick.

What really confuses me is that in another textbox2 I used

IIF(ISNULL(otherfield_date),"none","click to view") and for some reason that
textbox2 does update immediately after closing its respective subform which
updates the date in otherfield_date.

Any suggestions on how to update that textbox as soon as the user closes

that subform1 ?