Main Form Text Box Can't reference Sub Form

[Roger Reinsch]

Based on earlier suggestions against Split Forms, I've converted to using a Subform. I still can't get data from the subform into the main form.

The simplified main form has 3 text boxes and a Requery button (with macro).
Two text boxes are used as criteria in a Query.
The criteria for the first text box is Like simMainForm.criteriaOne OR simMainForm.criteriaOne is null. The other one is similar.
This allows wild cards in the criteria text boxes if desired. (E.G. searching for Roger or Rodger is accomplished with Ro*ger).
The third text box is to receive a field from a row in the dynaset created in the Sub Form window.

The query has a Form built over it (simplest automatically generated form) that was dragged on to the Main form.

In Form view, the access part of the operation performs as expected - Fill in one or both of the criteria and click the requery button. The sub form works fine. I can select a row other than the first and it gets highlighted.

I having two sorts of trouble with the Third text box that should get one of the columns values from the selected row.

First, I can't get it to contain anything that resembles the column from the sub form dynaset. It's not blank but it just picked some random value from the correct column (it is a date) but it doesn't match the date of any of the records.

Second, assuming we get the syntax for that reference squared away, how do I get the value from the currently selected row?

This is probably a syntax thing - but maybe not. The text box seems to only accept one dot level in the datasource property. Longer things get chopped off.

I'd like to do this with just levels of dots of qualifications to get the right result returned. This should not have to be procedural. If code is required, I'd like to use the macro facilities of Access 2010 and not get into VBA. The naming conventions for collections of collections of collections ... makes it really easy to leave out an important level.

Thanks in advance for your help.

 

[John Big Booty]

When referring to control across form/subform sets you need to use the correct syntax for that reference.

 

[Roger Reinsch]

When referring to control across form/subform sets you need to use the Correct Syntax [unlinked] for that reference.

After trying most of the combinations and permutations of the . and ! parts of the syntax, I still got no where. But I did find a solution.

This may be new for Access 2010 and if so, it really helps.
With the Main Form text box selected, I went to the Data tab and clicked the ... on the Control Source property.

In the expression elements, I expanded the Main Form element to expose the subform inside.
In the categories box, I selected the column name for the birth date (populated with a query that takes parameters from the Main Form).
In the Values box I double clicked <Value> and then OK and it was done.

What it came up with is =[Test1Sub].[Form]![date_of_birth]
After it was built I can see that the = takes the place of Me! in the chart.

Saving and going to Form View now populates the Main Form text boxes with the data from the selected record in the subform.

The Expression Builder really did the job. I wish I had poked the ... button BEFORE I tried to do it the hard way.
Thanks JBB

PS. I know you guys are headquartered in the UK so I know your default paper size is A4. In the US, the default is 8.5 x 11 inches. When I downloaded the Doc Version, it was locked and I couldn't change the margins to get it to fit on our shorter paper. So I couldn' use the .doc.