subform textbox contro lsource string?

[Guest]

I put the following in the control source of a text box in a subform's footer
on a tab in my main form: ="W:\OGCIT\Issue Files\" & [Forms]![Issues].[ID] &
[Forms]![Issues]![Requesting Party].[column](2)

All on one line.

It works sometimes. It works when some other code runs based on a DCount of
the records that display in the subform, and the DCount is > 0

I've looked for any requery code that might run due to the case of DCount>0.
There is none. All I'm trying to do is create a path based on a fixed root
with the last folder being a named based on information in my main form.

It seemed like such a good idea when I started....

CW

 

[Guest]

Hi CW,

You will probably find that it doesn't work when either the ID or Requesting
Party fields are blank (null). Try checking for these before using them in a
formula, eg:

=iif(isnull([Forms]![Issues].[ID]) or isnull([Forms]![Issues]![Requesting
Party].[column](2)), "", ="W:\OGCIT\Issue Files\" & [Forms]![Issues].[ID] &
[Forms]![Issues]![Requesting Party].[column](2))

Hope this helps.

Damian.

 

[Guest]

Thanks for your reply, Damian.

The [ID] is the PK and the requesting party is required, so all records have
both.

It has something to do with these text fields I added to the tabs to
indicate when tabs had recvords.

One tab has a "notes" subform (it's the second of three tabs). When you're
on the main/front tab, if either of the second or third tabs have records,
there's a text box that becomes visible and a number shows up in it
indicating the number of records. The boxes appear just below the tabs, so
you know how many of each type of record is present in each tab. When you're
on one of the second or third tabs, a similar box shows for the other other
(if you're on the second tab, the box shows number of records in the third
and vice versa). I figured if you were on a tab you probably didn't need teh
box.

Anyhow, I think maybe making those boxes become visible somehow requeries
the tab or subform or something, but I really don't know. All I know is it's
consistently the case that if the tab with the subform I'm working on has
records, the path displays in the text box like it should. If there isn't
any records in that subform, the text box is blank.

Thanks again for the help. Hoping the further explanation might trigger
some ideas. I spent hours today on it and still can't figure it out.

CW

Hi CW,

You will probably find that it doesn't work when either the ID or Requesting
Party fields are blank (null). Try checking for these before using them in a
formula, eg:

=iif(isnull([Forms]![Issues].[ID]) or isnull([Forms]![Issues]![Requesting
Party].[column](2)), "", ="W:\OGCIT\Issue Files\" & [Forms]![Issues].[ID] &
[Forms]![Issues]![Requesting Party].[column](2))

Hope this helps.

Damian.

I put the following in the control source of a text box in a subform's footer
on a tab in my main form: ="W:\OGCIT\Issue Files\" & [Forms]![Issues].[ID] &
[Forms]![Issues]![Requesting Party].[column](2)

All on one line.

It works sometimes. It works when some other code runs based on a DCount of
the records that display in the subform, and the DCount is > 0

I've looked for any requery code that might run due to the case of DCount>0.
There is none. All I'm trying to do is create a path based on a fixed root
with the last folder being a named based on information in my main form.

It seemed like such a good idea when I started....

CW

 

[Guest]

I finally figured it out. I was putting code in the "on_current" event of
the subform. When I moved it to the on_current event of the main form, it
works fine.

Seems like an obvious thing to try, in retrospect. Oh well.
CW

Thanks for your reply, Damian.

The [ID] is the PK and the requesting party is required, so all records have
both.

It has something to do with these text fields I added to the tabs to
indicate when tabs had recvords.

One tab has a "notes" subform (it's the second of three tabs). When you're
on the main/front tab, if either of the second or third tabs have records,
there's a text box that becomes visible and a number shows up in it
indicating the number of records. The boxes appear just below the tabs, so
you know how many of each type of record is present in each tab. When you're
on one of the second or third tabs, a similar box shows for the other other
(if you're on the second tab, the box shows number of records in the third
and vice versa). I figured if you were on a tab you probably didn't need teh
box.

Anyhow, I think maybe making those boxes become visible somehow requeries
the tab or subform or something, but I really don't know. All I know is it's
consistently the case that if the tab with the subform I'm working on has
records, the path displays in the text box like it should. If there isn't
any records in that subform, the text box is blank.

Thanks again for the help. Hoping the further explanation might trigger
some ideas. I spent hours today on it and still can't figure it out.

CW

Hi CW,

You will probably find that it doesn't work when either the ID or Requesting
Party fields are blank (null). Try checking for these before using them in a
formula, eg:

=iif(isnull([Forms]![Issues].[ID]) or isnull([Forms]![Issues]![Requesting
Party].[column](2)), "", ="W:\OGCIT\Issue Files\" & [Forms]![Issues].[ID] &
[Forms]![Issues]![Requesting Party].[column](2))

Hope this helps.

Damian.

I put the following in the control source of a text box in a subform's footer
on a tab in my main form: ="W:\OGCIT\Issue Files\" & [Forms]![Issues].[ID] &
[Forms]![Issues]![Requesting Party].[column](2)

All on one line.

It works sometimes. It works when some other code runs based on a DCount of
the records that display in the subform, and the DCount is > 0

I've looked for any requery code that might run due to the case of DCount>0.
There is none. All I'm trying to do is create a path based on a fixed root
with the last folder being a named based on information in my main form.

It seemed like such a good idea when I started....

CW