Unable to set ControlSource of a textbox on an unbound sub form.

[Andy]

Hi,

I am having trouble setting the ControlSource of a textbox on an
unbound subform. The textbox displays #Name?.

Environment: MS Access Project (2002, SP3).

Background: The purpose of my application is to display the hierchical
structure of an organization. An initial form uses a treeview control
to display countries, cities and offices. When a user highlights a
node representing a city and clicks a 'View' button the "City Details"
form is opened. The "City Details" form is the main form in the
context of this problem and contains a subform ("subOffices") that
should display details on offices within the chosen city.

Both the main and subforms are unbound. The mainform's record source
is set during the Form_Open event and is based on the results of a
stored procedure. Within the same procedure I call a subroutine
(located in the main form's code module) to generate an ado recordset
(the results of a stored procedure returning details about offices
within the city) and bind the subform controls to fields in the
recordset.

The line of code I've been using (and fails) is:

Me.subOffices.Form.[txtCountryName].ControlSource =
rs.Fields("Country_Name").Name

Please note:

- subOffices is the name of the subform and also the name of the
subform control on the main form.
- I deliberately named the textbox differently to the associated field
name to avoid ambiguity.
- The "Country_Name" field does exist and the recordset contains at
least one record.
- I can read the tag value of the textbox correctly with:

Debug.Print Me.subOffices.Form.Controls("txtCountryName").Tag

Can anyone please suggest what I'm doing wrong?

[Ideally I would have liked to generate the recordset for the subform
in its Open_Form event (so it works in the same way as the main form)
but that doesn't seem possible.]

Many thanks in advance,

Andy

 

[Stefan Hoffmann]

hi Andy,

Me.subOffices.Form.[txtCountryName].ControlSource =
rs.Fields("Country_Name").Name

This is not a valid control source. Use .Value instead of .ControlSource.


mfG
--> stefan <--

 

[Andy]

Hi Stefan,

Many thanks for your suggestion. I should have mentioned that the
textbox is in the Detail section and that the subform uses the
Datasheet view. In my situation there can be more than one office in a
city i.e. there are multiple records.

When I was testing I was able to assign the first record's data to the
textbox value, but this gets repeated for each record so was not
helpful.

Thanks again,

Andy

hi Andy,

Me.subOffices.Form.[txtCountryName].ControlSource =
rs.Fields("Country_Name").Name

This is not a valid control source. Use .Value instead of .ControlSource.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Andy,

Many thanks for your suggestion. I should have mentioned that the
textbox is in the Detail section and that the subform uses the
Datasheet view. In my situation there can be more than one office in a
city i.e. there are multiple records.

Is your subform really unbound?

When I was testing I was able to assign the first record's data to the
textbox value, but this gets repeated for each record so was not
helpful.

If it is bound, then use

Me![Country_Name] = rs.Fields("Country_Name").Name


mfG
--> stefan <--

 

[Andy]

Hi Stefan,

Initially the subform was bound to the stored procedure, I then dragged
fields onto the Detail section of the form.

When I opened the main form, input boxes were displayed requesting the
two parameters required by the stored procedure used by the subform. I
could not find a way of providing the parameters in a simple automated
way so I unbound the form and also the individual textbox.

Thanks,

Andy

hi Andy,

Many thanks for your suggestion. I should have mentioned that the
textbox is in the Detail section and that the subform uses the
Datasheet view. In my situation there can be more than one office in a
city i.e. there are multiple records.

Is your subform really unbound?

When I was testing I was able to assign the first record's data to the
textbox value, but this gets repeated for each record so was not
helpful.

If it is bound, then use

Me![Country_Name] = rs.Fields("Country_Name").Name


mfG
--> stefan <--