473,598 Members | 3,128 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error 3220 - Function Is Not Available In Expression In Table Level Validation Expression

Hi

I have a really weird problem and any assistance would be welcome.

I have developed an app in Access 2002. The app runs perfectly on the
development machine. I have packaged the app
using the Microsoft XP Developer Packaging Wizard (Service Pack 1). The 1st
3 releases of the app ran perfectly on
site.

On Friday the 4th release of the app went out to a customer. The app opened
perfectly but on clicking OK on my login form
I get the error : 3220 - The functiom is not available in expression in
table level validation expression.

I took the 2 main files one mde file containing the application and an mdb
file containing the data which is linked into the
mde file from the support directory on the installation CD and copied them
to a machine which has Office XP Professional
loaded (as I can not simulate the error on the box used for development). I
found that when I went into a table that when
manually entering a record I got the same error, the weird part is neither
the table nor the code populating the table
has changed since the 1st release of the app (In fact it is code I use for a
table and module that I use in many other
applications).

The table layout is as follows :

Key AutoNumber Primary Key
UserName Text(15) Mandatory
No Default Value
SecurityLevel Long Integer Mandatory
Default Value 0
DBChanged Text(20) Mandatory
No Default Value
RecordChanged Text(100) Mandatory
No Default Value
DBAction Text(100) Mandatory
No Default Value
DateChanged Date/Time Mandatory
Default Value Date()
TimeChanged Date/Time Mandatory
Default Value Time()
TillNo Integer
Default Value 0
The code used to populate the table is as follows :

Sub logchg(tbl As String, rec As Variant, action As String)
Dim srec As String
Dim logrs As Recordset

srec = CStr(rec)

Set logrs = db.OpenRecordse t("tblLog")
logrs.AddNew
logrs![UserName] = modGlobals.user
logrs![SecurityLevel] = modGlobals.Secu rityLevel
logrs![DbChanged] = tbl
logrs![RecordChanged] = srec
logrs![DBAction] = action
logrs![TillNo] = modGlobals.Till No
logrs.Update
logrs.Close
End Sub

The call that causes the error is :

Call modSecurity.log chg("N/A", "N/A", "LOGON")

Can anyone explain why I get the error ?

TIA
Steve
Nov 13 '05 #1
18 9026
Steve wrote:
Hi

I have a really weird problem and any assistance would be welcome.

I have developed an app in Access 2002. The app runs perfectly on the
development machine. I have packaged the app
using the Microsoft XP Developer Packaging Wizard (Service Pack 1). The 1st
3 releases of the app ran perfectly on
site.

On Friday the 4th release of the app went out to a customer. The app opened
perfectly but on clicking OK on my login form
I get the error : 3220 - The functiom is not available in expression in
table level validation expression.

I took the 2 main files one mde file containing the application and an mdb
file containing the data which is linked into the
mde file from the support directory on the installation CD and copied them
to a machine which has Office XP Professional
loaded (as I can not simulate the error on the box used for development). I
found that when I went into a table that when
manually entering a record I got the same error, the weird part is neither
the table nor the code populating the table
has changed since the 1st release of the app (In fact it is code I use for a
table and module that I use in many other
applications).

The table layout is as follows :

Key AutoNumber Primary Key
UserName Text(15) Mandatory
No Default Value
SecurityLevel Long Integer Mandatory
Default Value 0
DBChanged Text(20) Mandatory
No Default Value
RecordChanged Text(100) Mandatory
No Default Value
DBAction Text(100) Mandatory
No Default Value
DateChanged Date/Time Mandatory
Default Value Date()
TimeChanged Date/Time Mandatory
Default Value Time()
TillNo Integer
Default Value 0
The code used to populate the table is as follows :

Sub logchg(tbl As String, rec As Variant, action As String)
Dim srec As String
Dim logrs As Recordset

srec = CStr(rec)

Set logrs = db.OpenRecordse t("tblLog")
logrs.AddNew
logrs![UserName] = modGlobals.user
logrs![SecurityLevel] = modGlobals.Secu rityLevel
logrs![DbChanged] = tbl
logrs![RecordChanged] = srec
logrs![DBAction] = action
logrs![TillNo] = modGlobals.Till No
logrs.Update
logrs.Close
End Sub

The call that causes the error is :

Call modSecurity.log chg("N/A", "N/A", "LOGON")

Can anyone explain why I get the error ?

TIA
Steve


I have A97, and error 3220 referes to Paradox and 2426 is Function isn't
available in expressions. Your error has the addendum of
TableLevelValid ation to my 2426.

Open up your table tblLog. Do you have a validation rule on any of the
fields you are updating? Maybe make a copy of the current log table and
then open the log file and remove any validation rules and text of so.

If that doesn't help, is there a way to verify what line in LogChg that
blows up? IOW, step through code and locate which field you are
updating that is the cause of the problem.


Nov 13 '05 #2

"Salad" <oi*@vinegar.co m> wrote in message
news:Ds******** **********@news read1.news.pas. earthlink.net.. .
Steve wrote:
Hi

I have a really weird problem and any assistance would be welcome.

I have developed an app in Access 2002. The app runs perfectly on the
development machine. I have packaged the app
using the Microsoft XP Developer Packaging Wizard (Service Pack 1). The 1st 3 releases of the app ran perfectly on
site.

On Friday the 4th release of the app went out to a customer. The app opened perfectly but on clicking OK on my login form
I get the error : 3220 - The functiom is not available in expression in
table level validation expression.

I took the 2 main files one mde file containing the application and an mdb file containing the data which is linked into the
mde file from the support directory on the installation CD and copied them to a machine which has Office XP Professional
loaded (as I can not simulate the error on the box used for development). I found that when I went into a table that when
manually entering a record I got the same error, the weird part is neither the table nor the code populating the table
has changed since the 1st release of the app (In fact it is code I use for a table and module that I use in many other
applications).

The table layout is as follows :

Key AutoNumber Primary Key
UserName Text(15) Mandatory No Default Value
SecurityLevel Long Integer Mandatory Default Value 0
DBChanged Text(20) Mandatory No Default Value
RecordChanged Text(100) Mandatory
No Default Value
DBAction Text(100) Mandatory No Default Value
DateChanged Date/Time Mandatory Default Value Date()
TimeChanged Date/Time Mandatory Default Value Time()
TillNo Integer
Default Value 0
The code used to populate the table is as follows :

Sub logchg(tbl As String, rec As Variant, action As String)
Dim srec As String
Dim logrs As Recordset

srec = CStr(rec)

Set logrs = db.OpenRecordse t("tblLog")
logrs.AddNew
logrs![UserName] = modGlobals.user
logrs![SecurityLevel] = modGlobals.Secu rityLevel
logrs![DbChanged] = tbl
logrs![RecordChanged] = srec
logrs![DBAction] = action
logrs![TillNo] = modGlobals.Till No
logrs.Update
logrs.Close
End Sub

The call that causes the error is :

Call modSecurity.log chg("N/A", "N/A", "LOGON")

Can anyone explain why I get the error ?

TIA
Steve


I have A97, and error 3220 referes to Paradox and 2426 is Function isn't
available in expressions. Your error has the addendum of
TableLevelValid ation to my 2426.

Open up your table tblLog. Do you have a validation rule on any of the
fields you are updating? Maybe make a copy of the current log table and
then open the log file and remove any validation rules and text of so.

If that doesn't help, is there a way to verify what line in LogChg that
blows up? IOW, step through code and locate which field you are
updating that is the cause of the problem.

Thanks for the response, I have no validation rule set up, and the code
seems to crash on :

logrs.Update

which unfortunately tells me nothing.

Steve
Nov 13 '05 #3
Steve wrote:
I have A97, and error 3220 referes to Paradox and 2426 is Function isn't
available in expressions. Your error has the addendum of
TableLevelVal idation to my 2426.

Open up your table tblLog. Do you have a validation rule on any of the
fields you are updating? Maybe make a copy of the current log table and
then open the log file and remove any validation rules and text of so.

If that doesn't help, is there a way to verify what line in LogChg that
blows up? IOW, step through code and locate which field you are
updating that is the cause of the problem.

Thanks for the response, I have no validation rule set up, and the code
seems to crash on :

logrs.Update

which unfortunately tells me nothing.

Steve


OK. You know the line. It is telling you there is a value it doesn't
like...near as I can tell. I'll assume you know how to step through
code. If not, click on the bar to the left of the AddNew line that puts
a dot on the bar and highlights the line. Now run the process. Check
the values of each item you are updating in the recordset.

ALso, copy the code and put into a module. Instead of plugging in the
values in the program, attempt to add with valid values. Ex:
logrs![UserName] = "Steve"
Do that for all fields. If you can't add a record with
plugged-in-manually and the values are correct for the field, maybe you
have an existing record that conflicts with something.

When are you calling this? I had a main form that called some functions
and queries that used NZ() and otherthings like that. Once the form was
loaded the functions I was using could be used. But on the loading of
the form, the VBA functions were not available.

I doubt that is the case if you have been using this method for a while.
Check your data.

If you can add a record with manual data and the data looks ok when you
step through code, then start commenting out a line where you update the
recordset and keep commenting out an update line until you know which
one goofs up. Ex:
logrs![UserName] = "Steve"
and it blows up. Comment out
'logrs![UserName] = "Steve"
and it doesn't blow up. That means there is something wrong with the
UserName field in the table or else Steve is not a valid value.

Trial and error may be the easies method to find out what is wrong.

Nov 13 '05 #4
If I had to guess I would guess that it is complaining about one of the
function calls like Time().
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 13 '05 #5

"Salad" <oi*@vinegar.co m> wrote in message
news:yU******** ***********@new sread1.news.pas .earthlink.net. ..
Steve wrote:
I have A97, and error 3220 referes to Paradox and 2426 is Function isn't
available in expressions. Your error has the addendum of
TableLevelVal idation to my 2426.

Open up your table tblLog. Do you have a validation rule on any of the
fields you are updating? Maybe make a copy of the current log table and
then open the log file and remove any validation rules and text of so.

If that doesn't help, is there a way to verify what line in LogChg that
blows up? IOW, step through code and locate which field you are
updating that is the cause of the problem.

Thanks for the response, I have no validation rule set up, and the code
seems to crash on :

logrs.Update

which unfortunately tells me nothing.

Steve


OK. You know the line. It is telling you there is a value it doesn't
like...near as I can tell. I'll assume you know how to step through
code. If not, click on the bar to the left of the AddNew line that puts
a dot on the bar and highlights the line. Now run the process. Check
the values of each item you are updating in the recordset.

ALso, copy the code and put into a module. Instead of plugging in the
values in the program, attempt to add with valid values. Ex:
logrs![UserName] = "Steve"
Do that for all fields. If you can't add a record with
plugged-in-manually and the values are correct for the field, maybe you
have an existing record that conflicts with something.

When are you calling this? I had a main form that called some functions
and queries that used NZ() and otherthings like that. Once the form was
loaded the functions I was using could be used. But on the loading of
the form, the VBA functions were not available.

I doubt that is the case if you have been using this method for a while.
Check your data.

If you can add a record with manual data and the data looks ok when you
step through code, then start commenting out a line where you update the
recordset and keep commenting out an update line until you know which
one goofs up. Ex:
logrs![UserName] = "Steve"
and it blows up. Comment out
'logrs![UserName] = "Steve"
and it doesn't blow up. That means there is something wrong with the
UserName field in the table or else Steve is not a valid value.

Trial and error may be the easies method to find out what is wrong.


Thanks, but I have tried what you are telling me.

Steve
Nov 13 '05 #6
I think you are correct, but the problem is I have checked out my References
and everything seems to be in order.

Steve

"Michael (michka) Kaplan [MS]" <mi*****@online .microsoft.com> wrote in
message news:40******** @news.microsoft .com...
If I had to guess I would guess that it is complaining about one of the
function calls like Time().
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 13 '05 #7
And if you take out the function calls? You might want to humor me and try
it, just for giggles....

Out of curiousity, what are the references?

There is an interesting case where even if all the references are okay that
a changed path of one of the references typelibs (DLL, OCX, or TLB) can
cause a partial reference break because the reference fixup does not happen
properly when the ES is doing the fixup as opposed to VBA doing it directly.
The workaround (if this is the problem) is to make sure that the references
are all disambiguated and loaded prior to being used.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Steve" <st****@ufrmsa1 .uniforum.org.z a> wrote in message
news:c9******** **@ctb-nnrp2.saix.net. ..
I think you are correct, but the problem is I have checked out my References and everything seems to be in order.

Steve

"Michael (michka) Kaplan [MS]" <mi*****@online .microsoft.com> wrote in
message news:40******** @news.microsoft .com...
If I had to guess I would guess that it is complaining about one of the
function calls like Time().
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.


Nov 13 '05 #8
> I think you are correct, but the problem is I have checked out my
References
and everything seems to be in order.


I've experienced similar errors (function not available...) and it turned
out to be a references issue. Checking the version and path of the
different ocx and dll files and comparing between development and client
machines is a logical troubleshooting step. You might try removing all
references (on the client machine), closing out of Access, and then going
back in and adding them all back.

Just my two cents...
Nov 13 '05 #9
Thanks the help is appreciated.

Steve

"deko" <no****@hotmail .com> wrote in message
news:r7******** ***********@new ssvr29.news.pro digy.com...
I think you are correct, but the problem is I have checked out my

References
and everything seems to be in order.


I've experienced similar errors (function not available...) and it turned
out to be a references issue. Checking the version and path of the
different ocx and dll files and comparing between development and client
machines is a logical troubleshooting step. You might try removing all
references (on the client machine), closing out of Access, and then going
back in and adding them all back.

Just my two cents...

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3683
by: Chuck | last post by:
A97. A database has a table: tblA which has a single text field, B. It is a primary field, indexed and no duplicates. It is used as a lookup for table tblC. A form based on tblA is used to add new records. The Validation Rule: <>!! was made using the expression builder. The rule produces this error: The expression you entered in the Form control's Validation Rule doesn't contain the Automation object 'Tables' If I use simply: ...
0
23470
by: HKSHK | last post by:
This list compares the error codes used in VB.NET 2003 with those used in VB6. Error Codes: ============ 3: This Error number is obsolete and no longer used. (Formerly: Return without GoSub) 5: Procedure call or argument is not valid. 6: Overflow. 7: Out of memory.
7
6875
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is expression should return the 4 leftmost characters of the FilmNo
4
8002
by: Gary | last post by:
Hi, I get this error " " when my web page run, what does it mean? Hope someone can help!!! Gary
7
3597
ak1dnar
by: ak1dnar | last post by:
Hi, I got this scripts from this URL There is Error when i submit the form. Line: 54 Error: 'document.getElementbyID(....)' is null or not an object What is this error. Complete Files
1
2883
by: Lost in Class | last post by:
I am relatively new to working with Access. I'm preparing a basic table and a form to enter data into. I'm having problems entering a validation rule and text. The field contains a currency figure. The validation rule needs to limit the entry to an amount between 18000 and 100000. Under "field properties" I have entered " Between 18000 And 100000 " . When I go to save it I get the following error message " Syntax Error (missing operator) in...
2
19443
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
2
6879
by: ryan.paquette | last post by:
Hello' I have a field in which I require validation in a before update event. The validation code I have works fine, no problem there. However I would like to turn off the default validation error message...as it shows up in addition to the message I am providing. For some reason, (SetWarnings = False) does not apply to this error...
0
2897
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
7896
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8389
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8393
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8048
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
6713
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5848
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3939
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2411
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1246
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.