Subform variable reference in Query not working

[Dennis]

Hi,

I'm using Access in Office XP Pro on XP Pro w sp3.

I'm trying to access a variable on a subform but I am having a problem

My form name is frmTenant
My sub-form name is frmTenant_sfLease

I have tried the following expressions in the Immediate Window while I'm in
the sub-form:

?[Forms]![frmTenant]![txtFirstName] return the first name.

But when I try:

?[Forms]![frmTenant]![frmTenant_sfLease]![cboPropNo] I receive the error
message "can't find the field 'frmTenant_sfLease' reference in your
expression".

I have even gone so far copy the Source Object field (frmTenant_sfLease)
from the subform properties window and paste the text into my expression.
That did not work. I also know that

What do I need to do to reference the field?

 

[Dirk Goldgar]

Hi,

I'm using Access in Office XP Pro on XP Pro w sp3.

I'm trying to access a variable on a subform but I am having a problem

My form name is frmTenant
My sub-form name is frmTenant_sfLease

I have tried the following expressions in the Immediate Window while I'm
in
the sub-form:

?[Forms]![frmTenant]![txtFirstName] return the first name.

But when I try:

?[Forms]![frmTenant]![frmTenant_sfLease]![cboPropNo] I receive the error
message "can't find the field 'frmTenant_sfLease' reference in your
expression".

I have even gone so far copy the Source Object field (frmTenant_sfLease)
from the subform properties window and paste the text into my expression.
That did not work. I also know that

What do I need to do to reference the field?

Please check to see whether "frmTenant_sfLease" is actually the name of the
subform control on frmTenant. You have made it clear that it is the name of
the subform control's Source Object, but it may not be the name of the
subform control itself. Your expression must be the name of the subform
*control*, not its Source Object.

 

[Dennis]

Dirk,

That was the issue. My sub-form control was sf_Lease and the form name was
frmTeneant_sfLease. That solved that issue. Now, I have a different one.

My subform's Default View in the Form's property is continuous. This
subform display each lease agreement that a tenant has signed. They sign a
new lease agreement each year, so there could be multiple lease agreements
being displayed in this sub-form. It is also possible that each agreement
could be for a different apartment in a different property. Therefore, each
line could contain a different property and a different apartment number.
That was background for the following question.

I have two controls. The first one is cboPropNo. I use this control to
select the property or apartment building. The second control is cboAptNo.
I'm trying to limit the apartments that appear in the combo box to just the
apartments for the property on the current line.

However, the combo box shows the apartments for the last property. That is
if I have three lines, when I choose the combo box on the first line, it
shows the apartments for the last line. If I change cboPropNo to do
Me.cboAptNo.Requery, then the value of ALL of the cboAprNo on all of the
lines change.

Can I do what I’m trying to do using the continuous form?

Thank you for your assistance.




Dennis

 

[Dirk Goldgar]

Dirk,

That was the issue. My sub-form control was sf_Lease and the form name
was
frmTeneant_sfLease. That solved that issue. Now, I have a different one.

My subform's Default View in the Form's property is continuous. This
subform display each lease agreement that a tenant has signed. They sign
a
new lease agreement each year, so there could be multiple lease agreements
being displayed in this sub-form. It is also possible that each agreement
could be for a different apartment in a different property. Therefore,
each
line could contain a different property and a different apartment number.
That was background for the following question.

I have two controls. The first one is cboPropNo. I use this control to
select the property or apartment building. The second control is
cboAptNo.
I'm trying to limit the apartments that appear in the combo box to just
the
apartments for the property on the current line.

However, the combo box shows the apartments for the last property. That
is
if I have three lines, when I choose the combo box on the first line, it
shows the apartments for the last line. If I change cboPropNo to do
Me.cboAptNo.Requery, then the value of ALL of the cboAprNo on all of the
lines change.

Can I do what I’m trying to do using the continuous form?

Dependent combo boxes on continuous forms are always tricky, because each
control on a continuous form, even though repeated for each record, is
really only one control with one set of properties. So if you change the
RowSource for the combo in the current record, you change it for all the
records. If your combo box's LimitToList property is set to Yes/True, then
that can cause all the other rows' copies of the combo box to go blank
(since their values are no longer in the list).

Your description of what is going on with your form seems a bit wonky to me,
so I'm not sure if you are describing it correctly or if I'm interpreting it
correctly. However, I would expect from your description that your subform
would be based on a table that would include fields identifying the tenant,
the property, and the apartement. The Link Master/Child fields of the
subform control would be the tenant ID (or whatever key field identifies the
tenant). Your controls cboPropNo and cboAptNo would be bound to the
PropertyNo and AptNo fields, respectively.

I gather that your rowsource query for cboAptNo uses cboPropNo as a
criterion, along these lines:

SELECT AptNo FROM Apartments
WHERE PropertyNo = [Forms]![frmTenant]![sfLease][cboPropNo]

Then you would naturally requery the cboAptNo combo box in the subform's
Current event and the AfterUpdate event of cboPropNo. Note that you should
requery it in both of these events, not just one of them.

The only trick to making this work as simply as that is that you must set
the LimitToList property of cboAptNo to No/False, in the design view of the
form. If you set LimitToList to No, the combo box will display the value
even if it isn't in the list, and so the other (non-current) rows of the
subform will still show the proper values.

Unfortunately, you can't do this if your combo box's first visible column is
not also the bound column. Access won't let you set LimitToList = No if the
displayed column and the bound column are different. I am hoping that, in
your case, there isn't anything to show for the AptNo but, well, the AptNo,
and so the bound column and visible column can be the same.

 

[Dennis]

Dirk,

Sorry I was not clear. But your assumptions were correct.

My combo-box’s LimitToList property is set to Yes/True and when I change one
value, the other’s go blank. That is what I was trying to describe, but I
did not go a good job of it.

Every thing you assumed was correct. Here is the actual rowsource query for
cboAptNo:

SELECT qrytblUnit.UnitNo, qrytblUnit.AptNo, qrytblUnit.PropNo
FROM qrytblUnit WHERE
(((qrytblUnit.PropNo)=Forms!frmTenant!sf_Lease!cboPropNo));

I did not know about the LimitToList property being set to No. Good trick
to know. However the first visible column is NOT the bound column. The
first field is the autonumber foreign key to the Apartment table and is
invisible, the second field and the first display field is the actual
Apartment number. Unfortunately, I have quite a few apartment # 1 and they
have to be associated with a property. So at best, I would still have a two
part key which would not make sense to anyone such as 12-1 or 12-A1 where 12
is the property number and 1 or A1 is the apartment number.

If I change my continuous input form into a continuous display, could I get
control of the current row as the sub-form is loading them (putting code in
the sub-form’s On Current event) and manually look up the property name and
apartment number and display that information in a text box.

I would then allow the user to double click on the row in the continuous
display form. This double click would bring up pop-up window and allow them
do change or delete information on that form. I would provide a command
button to add new records.

Any other thought?

Dennis

 

[Dennis]

Dirk,

I put the following code in the Form_Current event:

Debug.Print "frmTenant-sfLease Form_Current - Lease No", Me.txtLeaseNo,
Me.cboPropNo.Column(1), Me.cboAptNo.Column(1)

The only information which was printed was the data from the first row of
the continuous form. How do I get access to each record as before it is
displayed on the continuous form?

 

[Dirk Goldgar]

Dirk,

Sorry I was not clear. But your assumptions were correct.

My combo-box’s LimitToList property is set to Yes/True and when I change
one
value, the other’s go blank. That is what I was trying to describe, but I
did not go a good job of it.

Every thing you assumed was correct. Here is the actual rowsource query
for
cboAptNo:

SELECT qrytblUnit.UnitNo, qrytblUnit.AptNo, qrytblUnit.PropNo
FROM qrytblUnit WHERE
(((qrytblUnit.PropNo)=Forms!frmTenant!sf_Lease!cboPropNo));

I did not know about the LimitToList property being set to No. Good trick
to know. However the first visible column is NOT the bound column. The
first field is the autonumber foreign key to the Apartment table and is
invisible, the second field and the first display field is the actual
Apartment number. Unfortunately, I have quite a few apartment # 1 and
they
have to be associated with a property. So at best, I would still have a
two
part key which would not make sense to anyone such as 12-1 or 12-A1 where
12
is the property number and 1 or A1 is the apartment number.

So UnitNo is your Apartment table's primary key? I take it that you are
storing UnitNo in your Leases table (or whatever it is called -- I mean the
table on which your subform sfLease is based).

It is possible to make your form work with this table structure, through a
little trickery involving a text box overlaid on the combo box. However, my
first reaction would be to change your table design so that the Apartments
table doesn't use an autonumber key. Instead, it would use a compound
primary key of PropertyNo + AptNo. That assumes that no property will have
two apartments with the same AptNo. Different properties could have
apartments with the same number, but that doesn't matter.

The Lease table would then record the PropertyNo and ApartmentNo, thus
clearly identifying which apartment in which property this lease applies to.
The combo box cboAptNo for choosing the apartment on the subform would then
have a rowsource like this:

SELECT AptNo FROM qrytblUnit
WHERE PropNo=Forms!frmTenant!sf_Lease!cboPropNo

And then you could have its LimitToList property set to No so that the
correct apartment number is shown for all apartments on the continuous form.

If I change my continuous input form into a continuous display, could I
get
control of the current row as the sub-form is loading them (putting code
in
the sub-form’s On Current event) and manually look up the property name
and
apartment number and display that information in a text box.

I don't understand what you mean by this. What do you mean by "a continuous
display"?

I would then allow the user to double click on the row in the continuous
display form. This double click would bring up pop-up window and allow
them
do change or delete information on that form. I would provide a command
button to add new records.

If you mean that you could set up a subform that uses a query to join the
Apartments table to the Leases table and thus can display the AptNo in a
text box, using a separate popup form to enter or edit the apartment, then
yes, that could work. But I don't think I would put the user to the trouble
of opening a separate popup form. If restructuring your Apartments table as
I described above won't work for you, you can use the trick I mentioned
earlier to overlay a text box on the combo box. I'll describe it in more
detail if you let me know you want to follow that approach.

 

[Dennis]

Dirk,

Once again you have come to my rescue. Thanks! You answered one thing I
have been trying to resolve for quite a while an that is a two part primary
key. Very nice.

Concerning your comment :

“you can use the trick I mentioned earlier to overlay a text box on the
combo box. I'll describe it in more detail if you let me know you want to
follow that approach.â€

I would like to know about this approach because I would like to use it in
another form on which I’m working.

I wanted to make the key to the Apartment table be the Prop No and the Apt
No, but did not know how. Now I do. Here is my current table layout:

Property Table
Prop No (auto number) is the primary key

Apartment Table
Prop No is now 1st part of primary key and foreign key (fk) to Property
Table.
Apt No is now 2nd part of primary key
- no property will have two apartment with the same number.

Tenant table has
Tenant No (auto number) is the primary key.
Tenant name and to other info

Lease table has
Lease No (auto number) and is the primary key.
Tenant No – fk to Tenant table.
Prop No – 1st part of fk to Property Table
Apt No – 2nd part of fk to Property Table


With respect to your comment:

I don't understand what you mean by this. What do you mean by "a continuous
display"?

This was a trick I learned from this forum. You build a continuous form
with the desired field on the line. Then you put an invisible button over
the entire row so that this button covers every field. When the user thinks
they are clicking on field or the row, they are actually clicking on the
invisible button and not on the actual data controls / fields on the form. I
then launch a pop up window that allows them to enter the data.

I’m using this process as a front end to home document management module. I
have a form where you select the file folder name (Utility Bill, Mortgage,
Warranty Info) from a combo box, then select the sub-section of the file
folder from another combo box. The form then displays a list of all of the
documents in the file folder’s sub-section in a continuous sub-form. When
you click on a row, the image is displayed on the screen.

 

[Dirk Goldgar]

Concerning your comment :

“you can use the trick I mentioned earlier to overlay a text box on the
combo box. I'll describe it in more detail if you let me know you want to
follow that approach.â€

I would like to know about this approach because I would like to use it in
another form on which I’m working.

To use this technique, you base the form on a query that returns both the
foreign key field you want to store, and the text field you want to display.
Let's call them "FKField" and "DisplayField". FKField is stored in the
table that the form is primarily concerned with, while DisplayField comes
from different table, a lookup table. In the lookup table, the key field
that corresponds to FKField is usually the primary key field, so I'll call
that field "PKField". In real life, that field may well have the same name
as the foreign key field in the other table. (In your original table
arrangement, "UnitNo" would be the foreign key, and "AptNo" would be the
display field -- you wanted to store UnitNo in the Leases table, but display
AptNo on the form.)

On the form, you have a combo box bound to FKField, but drawing its
rowsource from the lookup table, including both PKField and DisplayField,
filtered according to the current record on the form. So it has properties
like these:

Control Source: FKField
Row Source:
SELECT PKField, DisplayField FROM LookupTable
WHERE SomeOtherField = Forms!YourForm!SomeOtherField;
Bound Column: 1
Column Widths: 0; 1 (or whatever width is required)
Limit To List: Yes

Because Limit To List is set to Yes/True, this combo box will be blank
whenever its (requeried) rowsource doesn't include the PKField matching the
form's FKField.

Also on the form, you have a text box bound to DisplayField (from the form's
recordsource query). So this text box will always display the text
corresponding to that record's FKField, regardless of what the combo box
shows or doesn't show. Set these properties of the text box:

Locked: Yes
Tab Stop: No

That's so that the DisplayField value in the lookup table can't accidentally
be changed by the user, and the user can't reach the text box by tabbing
through the form.

Now, here's where the trick comes in. You size the text box so that it
exactly matches the text area of the combo box -- all but the drop-down
arrow. Then you position the text box so that it exactly covers up the text
area of the combo box. Use the Format -> Bring To Front menu item to ensure
that the text box is in front of the combo box.

Finally, create two event procedures: one for the text box's GotFocus event
(to send the focus to the combo box) and one for the combo box's GotFocus
event (to requery the combo box). The event procedures might look like
this:

'------ start of example code ------
Private Sub txtDisplayField_GotFocus()

Me.cboFKField.SetFocus

End Sub

Private Sub cboFKField_GotFocus()

Me.cboFKField.Requery

End Sub
'------ end of example code ------

In the above, "txtDisplayField" is the name of the text box, and
"cboFKField" is the name of the combo box.

Unless I've overlooked something (which is possible, since I'm writing this
out freehand), that should do it. All records on the continuous form should
*appear* to show a combo box containing the correct DisplayField text, but
what they are really showing, except when the combo box has the focus, is
the text box instead. But any attempt to enter data in the combo box will
cause the combo box to get the focus and be requeried to show just the
appropriate items in its list.