Allen Browne's Function not available

[David]

Hello all,
Not sure how much info to give, I'll be as brief as I can to start with.
A2002, sp3. Database is also 2002.
I use the MDE that I created, no errors.
Another user uses same mde, receives error when attempting to update a
cbo_box

Error is: Function is not available in expression in table-level validation
expression.
User clicks OK on error message and cbo_box is displays updated info.

The cbo_box is on a nested table that has a parent/child relationship. Link
Master field is Forms![frm_PRB_Date].[txt_PRB_Date]. Link Child field is
Attendee_Date
The txt_PRB_Date is populated from Allen Browne's function titled
ajbcalendar that uses the form frmCalendar

Not much info, but any insight you can provide would be greatly appreciated.

 

[Allen Browne]

So you have:
- Main form named "frm_PRB_Date".
- Subform whose RecordSource is a table named "cbo_box" (?).

- Main form's table has a *Date/Time* type field named "txt_PRB_Date".
- Main form has a text box with the same name, populated by the calendar
code.

- Subform has a Date/Time field name Attendee_Date.
- Subform has a text box with the same name?

Suggestions:
1. Change the LinkMasterFields to just: txt_PRB_Date

2. Open the tables that feed the main form and subform in design view, and
remove the Default Value from all fields. Also remove any Default Value from
all controls in the form (at least to test.)

3. While you are in the table, check if there is any validation rule on the
cbo_box date field (lower pane), or on the table (Properties box in table
design view.)

4. Make sure the Name AutoCorrect boxes are unchedked under:
Tools | Options | General
Then compact the database.

5. Verify the problem user has applied SP8 for JET 4. Have them locate
msjet40.dll (typically in windows\system32), right-click, choose Properties,
and look on the Version tab. They should see 4.0.8xxx.0. If they don't see
the 8, download SP8 for JET 4 from:
http://support.microsoft.com/gp/sp

 

[David]

Mr. Browne,

Thank you for the list of suggestions and your ability to turn my ramblings
into a meaningful summary. You are absolutely correct with your
understanding of the situation.

Problem resolved, I had the PRB_Date field default value set to Date() in
the table. That seemed to cause the conflict.

I verified the jet version of users and verified autocorrect turned off.

Another related issue I am having. Once again, works fine for me, but not
the users.

I have different form that uses your calendar function. It is an unbound
form that I use to enter report parameters. I have a command button that
uses VBA code to open the report and the query for the report pulls the
values of the combo boxes and text fields that are populated in short date
format.

When unbound form is opened, I have two text boxes (date format) that are
display as #Name. I do not have any events on the Form, just the command
button to launch the report.

Field names are txtStartDate and txtEndDate
Control Source is blank, Format is Short Date
txtStartDate Default Value is Date()-7
txtEndDate Default Value is Date()

Why does this display correctly for me, but displays as #Name for other
users?

So you have:
- Main form named "frm_PRB_Date".
- Subform whose RecordSource is a table named "cbo_box" (?).

- Main form's table has a *Date/Time* type field named "txt_PRB_Date".
- Main form has a text box with the same name, populated by the calendar
code.

- Subform has a Date/Time field name Attendee_Date.
- Subform has a text box with the same name?

Suggestions:
1. Change the LinkMasterFields to just: txt_PRB_Date

2. Open the tables that feed the main form and subform in design view, and
remove the Default Value from all fields. Also remove any Default Value
from all controls in the form (at least to test.)

3. While you are in the table, check if there is any validation rule on
the cbo_box date field (lower pane), or on the table (Properties box in
table design view.)

4. Make sure the Name AutoCorrect boxes are unchedked under:
Tools | Options | General
Then compact the database.

5. Verify the problem user has applied SP8 for JET 4. Have them locate
msjet40.dll (typically in windows\system32), right-click, choose
Properties, and look on the Version tab. They should see 4.0.8xxx.0. If
they don't see the 8, download SP8 for JET 4 from:
http://support.microsoft.com/gp/sp


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all,
Not sure how much info to give, I'll be as brief as I can to start with.
A2002, sp3. Database is also 2002.
I use the MDE that I created, no errors.
Another user uses same mde, receives error when attempting to update a
cbo_box

Error is: Function is not available in expression in table-level
validation
expression.
User clicks OK on error message and cbo_box is displays updated info.

The cbo_box is on a nested table that has a parent/child relationship.
Link
Master field is Forms![frm_PRB_Date].[txt_PRB_Date]. Link Child field is
Attendee_Date
The txt_PRB_Date is populated from Allen Browne's function titled
ajbcalendar that uses the form frmCalendar

Not much info, but any insight you can provide would be greatly
appreciated.

 

[Allen Browne]

Try adding = to the default date string, e.g.:
=Date()

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mr. Browne,

Thank you for the list of suggestions and your ability to turn my
ramblings into a meaningful summary. You are absolutely correct with your
understanding of the situation.

Problem resolved, I had the PRB_Date field default value set to Date() in
the table. That seemed to cause the conflict.

I verified the jet version of users and verified autocorrect turned off.

Another related issue I am having. Once again, works fine for me, but not
the users.

I have different form that uses your calendar function. It is an unbound
form that I use to enter report parameters. I have a command button that
uses VBA code to open the report and the query for the report pulls the
values of the combo boxes and text fields that are populated in short date
format.

When unbound form is opened, I have two text boxes (date format) that are
display as #Name. I do not have any events on the Form, just the command
button to launch the report.

Field names are txtStartDate and txtEndDate
Control Source is blank, Format is Short Date
txtStartDate Default Value is Date()-7
txtEndDate Default Value is Date()

Why does this display correctly for me, but displays as #Name for other
users?

So you have:
- Main form named "frm_PRB_Date".
- Subform whose RecordSource is a table named "cbo_box" (?).

- Main form's table has a *Date/Time* type field named "txt_PRB_Date".
- Main form has a text box with the same name, populated by the calendar
code.

- Subform has a Date/Time field name Attendee_Date.
- Subform has a text box with the same name?

Suggestions:
1. Change the LinkMasterFields to just: txt_PRB_Date

2. Open the tables that feed the main form and subform in design view,
and remove the Default Value from all fields. Also remove any Default
Value from all controls in the form (at least to test.)

3. While you are in the table, check if there is any validation rule on
the cbo_box date field (lower pane), or on the table (Properties box in
table design view.)

4. Make sure the Name AutoCorrect boxes are unchedked under:
Tools | Options | General
Then compact the database.

5. Verify the problem user has applied SP8 for JET 4. Have them locate
msjet40.dll (typically in windows\system32), right-click, choose
Properties, and look on the Version tab. They should see 4.0.8xxx.0. If
they don't see the 8, download SP8 for JET 4 from:
http://support.microsoft.com/gp/sp

Hello all,
Not sure how much info to give, I'll be as brief as I can to start with.
A2002, sp3. Database is also 2002.
I use the MDE that I created, no errors.
Another user uses same mde, receives error when attempting to update a
cbo_box

Error is: Function is not available in expression in table-level
validation
expression.
User clicks OK on error message and cbo_box is displays updated info.

The cbo_box is on a nested table that has a parent/child relationship.
Link
Master field is Forms![frm_PRB_Date].[txt_PRB_Date]. Link Child field
is
Attendee_Date
The txt_PRB_Date is populated from Allen Browne's function titled
ajbcalendar that uses the form frmCalendar

Not much info, but any insight you can provide would be greatly
appreciated.