Expression Builder syntax - for Control Source

liamfitz

Registered User.
Local time
Today, 11:18
Joined
May 17, 2012
Messages
240
I'm not familiar with the syntax for the Expression Builder for a control's Source ( other than very basic). I want an output, based on another field's value on teh same subform ( Like a SELECT .. WHERE Clause in SQL. In fact, here's the equivalent SQL
Code:
"SELECT tblStaff.CalcStaff_Name FROM tblStaff WHERE tblStaff_ID=" & Me.Recordset!Staff_ID & ";"

Any suggestions gratefully received.:confused:
 
Not knowing your form design, but I'd try

"SELECT tblStaff.CalcStaff_Name FROM tblStaff WHERE tblStaff_ID=" & Me.Staff_ID & ";"
 
I didn't realise you could put 'unamended' SQL into this property. I'll try it now. Thanks jdraw.
 
I tried that ( with an = sign in front ) but didn't work.
 
Now I'm confused. I thought you were trying to run some SQL from vba and were having trouble with the form control reference.
I noticed the Recordset!, and thought you should be working with the form control directly Me.Staff_Id.
 
Don't worry. Here's the issue ( solved now ). If you select data from MORE THAN ONE table ( in a query, SQL ) for a form, you cannot edit/update that data, either directly through the form's controls, or in VBA dynamically. I had about 12 fields on a subform showing in datasheet view, ALL of which except ONE, came from the same table. So, I have a choice.. Either, use Conditional Formatting ( if possible ) to select 'independently' this one field, based on another field in the subform, or ( as I've opted for to ensure the subform/recordset is update-able ), add that problematic field to the original table - thus no need for a cross-table, and therefore non-Dynaset type recordset. Now the subform's data ALL comes from the same table ( and when I've finished my design changes ), makes it a form/recordset I can add and make changes to. It's useful to remember this, as it is something which caught me out before, and is a fundamental concept to Access design.
 
If you have a form/subform with tables in a 1 to Many relationship, and you Link the master and child fields you should be OK.

Not sure of your subform/recordset comment.

Anyway, since you say it's solved, good luck with your project.
 
I did have, and do have, a Form/Subform linked in this way. In my case, unique client records on Main Form and their referrals ( one or more ) on subform linked by Master/Child Client_ID ( unique P.K. number ) I initially used a cross-table query for the main form, which was read-only ( it took me a while to work out why it would not allow additions ). So I created a routine for, On_Load and On_ Current events, to create and load some fields dynamically, to make it fully editable. It looks as though I have had to do likewise for my subform, (although they are linked on Maser/Child field, which changes related records in sync. perfectly ) I'd be very interested, if you have been able to create any type of form, based on a query from more than one table, which allows additions and/or edits. Thanks.
 

Users who are viewing this thread

Back
Top Bottom