Sub Form produces #Name? error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin Chapple - 10 Dec 2004 00:58 GMT

After much help from this newsgroup I have a sub form which looks up a
value and fills a second field after update.

Now when I make a new record I get a zero in the ID field and #Name?
in the next field. If I attempt to enter a valid ID another new record
is prepared which will take the valid ID, enter the data from the
combo box and update the second and third fields.

I cannot delete the field with the #Name? entry.

I am even more out of my depth.

Robin Chapple

Reply to this Message

Ken Snell [MVP] - 10 Dec 2004 01:11 GMT

Robin -

Is the subform trying to use code similar to what you and I worked out on an
earlier situation?

Is the subform linked to the main form in some way? (See the
LinkMasterFields and LinkChildFields properties of the subform control [the
main form's control that actually holds the subform] and identify what
values may be there.)

How are you adding the first new record -- by navigation button? by making
some selection or action on the main form? what code runs in the subform?

We'll need some more details.

Signature

Ken Snell
<MS ACCESS MVP>

> After much help from this newsgroup I have a sub form which looks up a
> value and fills a second field after update.
[quoted text clipped - 9 lines]
>
> Robin Chapple

Reply to this Message

Robin Chapple - 10 Dec 2004 02:42 GMT

Ken,

>Is the subform trying to use code similar to what you and I worked out on an
>earlier situation?

Yes this is part of the same project.

>Is the subform linked to the main form in some way?

Yes it is linked by the ID of the member.

>How are you adding the first new record -- by navigation button?

By using the new record button in the navigation bar.

> what code runs in the subform?

Private Sub CboOffice_AfterUpdate()

Me.Order.Value = Me![CboOffice].Column(2)

OfficeYear = YEAR(Date) + (Date < DateSerial(YEAR(Date), 7,
1))

End Sub

I had named the original combo box wrongly.

Thanks,

Robin Chapple

Reply to this Message

Ken Snell [MVP] - 10 Dec 2004 02:51 GMT

Do I understand correctly that it's now working satisfactorily?

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
>
[quoted text clipped - 27 lines]
>
> Robin Chapple

Reply to this Message

Robin Chapple - 10 Dec 2004 05:42 GMT

No Ken,

I get an error message in the last line of the form when it is
displayed and then if I attempt to type an ID number into the ID field
another new record is offered as shown in the image.

Thanks,

Robin Chapple

>Do I understand correctly that it's now working satisfactorily?

Reply to this Message

Ken Snell [MVP] - 10 Dec 2004 16:02 GMT

I've looked at the second version of the database.

The problem is this expression in the Default Value property for the OFFICE
YEAR textbox in the subform:
[Forms]![tblRotaryYear]![RotaryYear]

There is no such form named tblRotaryYear in the database (and therefore
there is no such form open at the time the subform tries to get the value
from that form), and as such the error that you see is occurring. I don't
know what this default value should be, but the expression needs to be
deleted or else modified to one that will return a valid value.

Signature

Ken Snell
<MS ACCESS MVP>

> No Ken,
>
[quoted text clipped - 7 lines]
>
> >Do I understand correctly that it's now working satisfactorily?

Reply to this Message

Robin Chapple - 10 Dec 2004 19:31 GMT

Thanks Ken,

Very sloppy work by me! That was "Plan "A"" to get the year entered
until you have me VBA which was better. I have now removed it and all
is well.

Now to give the user confidence I am trying to get the year entered
when the Office is selected:

Private Sub CboOffice_AfterUpdate()

Me.Order.Value = Me![CboOffice].Column(2)

OfficeYear = YEAR(Date) + (Date < DateSerial(YEAR(Date), 7,
1))

End Sub

Private Sub CboOffice_Click()

OfficeYear = YEAR(Date) + (Date < DateSerial(YEAR(Date), 7,
1))

End Sub

It does not work. If and when I get it working I will remove the line
from After Update.

Many thanks,

Robin

>I've looked at the second version of the database.
>
[quoted text clipped - 7 lines]
>know what this default value should be, but the expression needs to be
>deleted or else modified to one that will return a valid value.

Reply to this Message

Ken Snell [MVP] - 10 Dec 2004 19:35 GMT

Try this:

Private Sub CboOffice_AfterUpdate()

Me.Order.Value = Me![CboOffice].Column(2)

Me.[Office Year].Value = YEAR(Date) + (Date < DateSerial(YEAR(Date),
7, 1))

End Sub

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks Ken,
>
[quoted text clipped - 39 lines]
> >know what this default value should be, but the expression needs to be
> >deleted or else modified to one that will return a valid value.

Reply to this Message

Robin Chapple - 10 Dec 2004 20:19 GMT

Thanks Ken,

That is just what the doctor ordered.

I am now doing the same with a "District Committees " sub form.
Standby for more problems.

Cheers,

Robin Chapple

>Private Sub CboOffice_AfterUpdate()
>
[quoted text clipped - 4 lines]
>
>End Sub

Reply to this Message