Update a field in form from a subform

[andydawson]

Hi Guys,

Wonder if anyone could help me out.

I've got a form [FrmQuoteHeader] with a record source link to [TblQuoteHeader]. The form has a subform embedded. The link between the two is a job number [QuoteNumber]. I am trying (and failing) to update a 'Yes/No' [ShowInJobs] field on the form/table when a 'Yes/No' [Job] field is updated in the subform.

So, basically, when any one of the subform records has the field [Job] set to YES, it updates the [ShowInJobs] field in the table [TblQuoteHeader].

Hope that makes sense!?! :-S

Any help would be great.

[FishVal]

Hello, Andy.

I suggest you the following:

• modify [FrmQuoteHeader].RowSource so it will return field which will be True when any record in subform's linked table (lets call it [tbl]) has [Job] field set to True
  

  SELECT [TblQuoteHeader].*, -Sgn(DCount("*","tbl","[QuoteNumber]=" & [TblQuoteHeader].[QuoteNumber] & " AND [Job]=True")) AS blnShowInJobs
  FROM [TblQuoteHeader];
   

  * using DLookUp instead of DCount supposed to be more effective as it prevent the whole table scanning
  

  ... -Sgn(Nz(DCount("[QuoteNumber]","tbl","[QuoteNumber]=" & [TblQuoteHeader].[QuoteNumber] & " AND [Job]=True"),0)) AS blnShowInJobs
   

• set [ShowInJobs].ControlSource property to blnShowInJobs
• for the subform [Job] control write the following AfterUpdate event handler
  

  Private Sub bln_Job()
      Me.Parent.Refresh
  End Sub
   

Regards,
Fish

[andydawson]

Hi Fish,

Thanks for the reply.

Just a question, when you say update the RowSource in your first point, do you mean the RecordSource? or am I not understanding something?

I'm just trying to understand the whole thing rather than just 'copy' 'paste'.



Thanks,

Andy.

[FishVal]

Sure, RecordSource.