Sub Form produces #Name? error

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

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
 
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.
 
Ken,

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

Yes this is part of the same project.
Is the subform linked to the main form in some way?
Click to expand...

Yes it is linked by the ID of the member.
How are you adding the first new record -- by navigation button?
Click to expand...

By using the new record button in the navigation bar.
what code runs in the subform?
Click to expand...

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
 
Do I understand correctly that it's now working satisfactorily?

--

Ken Snell
<MS ACCESS MVP>

Robin Chapple said:
Ken,

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

Yes this is part of the same project.
Is the subform linked to the main form in some way?
Click to expand...

Yes it is linked by the ID of the member.
How are you adding the first new record -- by navigation button?
Click to expand...

By using the new record button in the navigation bar.
what code runs in the subform?
Click to expand...

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
Click to expand...
 
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
 
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.
 
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
 
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
--

Ken Snell
<MS ACCESS MVP>

Robin Chapple said:
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.

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.
Click to expand...
Click to expand...
 
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
 
Back
Top