When all records in a subform combo box are a certain value, flag.

[Guest]

Hello,

I have a form named EMAIL and on it is a subform named PURCHASE ORDERS. Each
EMAIL has several PURCHASE ORDERS. On the PURCHASE ORDER subform, there is a
drop down box that indicates Pending or Completed. I want to program this so
that when all PURCHASE ORDERS are set to "Completed" that it will flag a
checkbox on the EMAIL main form. Does anyone have any suggestions on how to
go about this? Thanks!

 

[Stuart McCall]

Hello,

I have a form named EMAIL and on it is a subform named PURCHASE ORDERS.
Each
EMAIL has several PURCHASE ORDERS. On the PURCHASE ORDER subform, there is
a
drop down box that indicates Pending or Completed. I want to program this
so
that when all PURCHASE ORDERS are set to "Completed" that it will flag a
checkbox on the EMAIL main form. Does anyone have any suggestions on how
to
go about this? Thanks!

Use code like this in your main form's Current event:

Dim CompletedCount As Long
Dim SubformCount As Long

CompletedCount = Dcount("*","PURCHASE ORDERS","ComboField = 'Completed'")
SubformCount = Me.SubformControlName.Form.Recordcount
Me.CheckboxName.Value = (CompletedCount = SubformCount)

(alter the control names accordingly, of course)

 

[Guest]

Hi Stuart,

I just realized that EMAIL is also a subform. They are linked together and
EMAIL is the parent form and PURCHASE ORDERS is the child form. I still want
it to do the same thing, but I'm not writing the code right that you
provided. Could you help me with how to reference that subform? Thanks.

 

[Stuart McCall]

Hi Stuart,

I just realized that EMAIL is also a subform. They are linked together and
EMAIL is the parent form and PURCHASE ORDERS is the child form. I still
want
it to do the same thing, but I'm not writing the code right that you
provided. Could you help me with how to reference that subform? Thanks.

Ok, if I understand you correctly, getting the SubformCount ought to go
something like this:

SubformCount =
Me.EMAILSubformControlName.Form.PURCHASEORDERSSubformControlName.Form.Recordcount

(that's one line of code)

 

[Guest]

Great thanks Stuart.

Ok, if I understand you correctly, getting the SubformCount ought to go
something like this:

SubformCount =
Me.EMAILSubformControlName.Form.PURCHASEORDERSSubformControlName.Form.Recordcount

(that's one line of code)