By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Update a field in form from a subform

P: 4
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.
Jan 25 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 2,653
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
    Expand|Select|Wrap|Line Numbers
    1. SELECT [TblQuoteHeader].*, -Sgn(DCount("*","tbl","[QuoteNumber]=" & [TblQuoteHeader].[QuoteNumber] & " AND [Job]=True")) AS blnShowInJobs
    2. FROM [TblQuoteHeader];
    * using DLookUp instead of DCount supposed to be more effective as it prevent the whole table scanning
    Expand|Select|Wrap|Line Numbers
    1. ... -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
    Expand|Select|Wrap|Line Numbers
    1. Private Sub bln_Job()
    2.     Me.Parent.Refresh
    3. End Sub

Jan 25 '08 #2

P: 4
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'.


Jan 28 '08 #3

Expert 2.5K+
P: 2,653
Sure, RecordSource.
Jan 28 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.