By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

VBA reference problem

P: n/a
Gox
Hi,
I got a problem with VBE6.DLL (VBA reference)
On my machine everything work well.(access 2k, vesion dll 6.0.84.35)
First machine NO PROBLEM (access 2003, vesion dll 6.4.99.72)
Second machine problem (access xp, vesion dll 6.4.99.69)

I used pivot query and function Format([FIELD_NAME], "mmm") for Column
Heading

This is error:
Error No. 3075 Function is not available in expression in query
expression 'Format([PERIOD],"mmm")'

Function Format is part of VBA reference, and I think this dll
(VBE6.DLL) is problem.

Similar problem I got with CDate function.

Any hint??
Mar 8 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Disable Sandbox mode:
http://support.microsoft.com/kb/239482/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gox" <go*************@gmail.com> wrote in message
news:du**********@ss405.t-com.hr...
Hi,
I got a problem with VBE6.DLL (VBA reference)
On my machine everything work well.(access 2k, vesion dll 6.0.84.35)
First machine NO PROBLEM (access 2003, vesion dll 6.4.99.72)
Second machine problem (access xp, vesion dll 6.4.99.69)

I used pivot query and function Format([FIELD_NAME], "mmm") for Column
Heading

This is error:
Error No. 3075 Function is not available in expression in query expression
'Format([PERIOD],"mmm")'

Function Format is part of VBA reference, and I think this dll (VBE6.DLL)
is problem.

Similar problem I got with CDate function.

Any hint??

Mar 8 '06 #2

P: n/a
Gox
Allen, thanks for reply.

How determine which SP for office I have?
Allen Browne wrote:
Disable Sandbox mode:
http://support.microsoft.com/kb/239482/en-us

Mar 8 '06 #3

P: n/a
Help | About lets you know your Office service pack.

To know your JET 4 service, pack, locate msjet40.dll (typically in
windows\system32), right-click and choose Properties. On the Version tab,
you should see:
4.0.8xxx.0
The xxx digits don't matter, but if you don't see at least the 8, you need
to download the JET 4 SP8 service pack from:
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gox" <go*************@gmail.com> wrote in message
news:du**********@ss405.t-com.hr...
Allen, thanks for reply.

How determine which SP for office I have?
Allen Browne wrote:
Disable Sandbox mode:
http://support.microsoft.com/kb/239482/en-us

Mar 8 '06 #4

P: n/a
Gox
Allen Browne wrote:
Help | About lets you know your Office service pack.

To know your JET 4 service, pack, locate msjet40.dll (typically in
windows\system32), right-click and choose Properties. On the Version tab,
you should see:
4.0.8xxx.0
The xxx digits don't matter, but if you don't see at least the 8, you need
to download the JET 4 SP8 service pack from:
http://support.microsoft.com/kb/239114


My version is 4.0.8618.0 and others clients too.
Still I have not a solution of my problem.

Any others suggestions?
Mar 23 '06 #5

P: n/a
Let's try re-registering the VBA library.

On the machine that is playing up, open the Immediate Window (Ctrl+G), and
enter:
? References("VBA").FullPath

It should return the path such as:
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

Close Access.
Open a command prompt.
Unregister the library. Use your actual path, but it will be something like
this:
regsvr32 /u "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6.DLL"

Then re-register the library again:
regsvr32 "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6.DLL"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gox" <go*************@gmail.com> wrote in message
news:dv**********@ss405.t-com.hr...
Allen Browne wrote:
Help | About lets you know your Office service pack.

To know your JET 4 service, pack, locate msjet40.dll (typically in
windows\system32), right-click and choose Properties. On the Version tab,
you should see:
4.0.8xxx.0
The xxx digits don't matter, but if you don't see at least the 8, you
need to download the JET 4 SP8 service pack from:
http://support.microsoft.com/kb/239114


My version is 4.0.8618.0 and others clients too.
Still I have not a solution of my problem.

Any others suggestions?

Mar 23 '06 #6

P: n/a
Gox
Allen Browne wrote:
Let's try re-registering the VBA library.

Allen sorry, but no result. Same error, same problem.

And another problem with dynamic query.
First of all, I run my function queryIsExist(MyQueryName) /true-false
and if query exist delete query and create new query with same name and
different sql statement.
If not exist then skip deleting query.

Function queryIsExist(MyQueryName As String) As Boolean
'
' Determines if a Query is exist.
'
Dim db As DAO.Database, dcm As Document, dcms As Documents
Set db = CurrentDb
Set dcms = db.Containers!Tables.Documents

queryIsExist = False
For Each dcm In dcms
If dcm.Name = MyQueryName Then
queryIsExist = True
Exit Function ' Quit function once form has been found.
End If
Next
End Function

This is dynamic part:

If queryIsExist(MyQueryName) Then
db.QueryDefs.Delete MyQueryName
End If
Set qdf = db.CreateQueryDef(MyQueryName, strSQL)
Line: db.QueryDefs.Delete MyQueryName
don't work, because I got a error: 3012. Object <name> already exists.
on line below: Set qdf = db.CreateQueryDef(MyQueryName, strSQL)

Trying to create a object, before deleting existing object.

This not work on XP machine, 2003 access.
Any hint?

Mar 24 '06 #7

P: n/a
Sorry, the symptoms don't make sense to me.

Perhaps someone else has a suggestion to contribute.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gox" <go*************@gmail.com> wrote in message
news:e0**********@ss405.t-com.hr...
Allen Browne wrote:
Let's try re-registering the VBA library.

Allen sorry, but no result. Same error, same problem.

And another problem with dynamic query.
First of all, I run my function queryIsExist(MyQueryName) /true-false
and if query exist delete query and create new query with same name and
different sql statement.
If not exist then skip deleting query.

Function queryIsExist(MyQueryName As String) As Boolean
'
' Determines if a Query is exist.
'
Dim db As DAO.Database, dcm As Document, dcms As Documents
Set db = CurrentDb
Set dcms = db.Containers!Tables.Documents

queryIsExist = False
For Each dcm In dcms
If dcm.Name = MyQueryName Then
queryIsExist = True
Exit Function ' Quit function once form has been found.
End If
Next
End Function

This is dynamic part:

If queryIsExist(MyQueryName) Then
db.QueryDefs.Delete MyQueryName
End If
Set qdf = db.CreateQueryDef(MyQueryName, strSQL)
Line: db.QueryDefs.Delete MyQueryName
don't work, because I got a error: 3012. Object <name> already exists.
on line below: Set qdf = db.CreateQueryDef(MyQueryName, strSQL)

Trying to create a object, before deleting existing object.

This not work on XP machine, 2003 access.
Any hint?

Mar 24 '06 #8

P: n/a
Gox schreef:
If queryIsExist(MyQueryName) Then
db.QueryDefs.Delete MyQueryName
End If
Set qdf = db.CreateQueryDef(MyQueryName, strSQL)
Line: db.QueryDefs.Delete MyQueryName
don't work, because I got a error: 3012. Object <name> already exists.
on line below: Set qdf = db.CreateQueryDef(MyQueryName, strSQL)

Trying to create a object, before deleting existing object.

This not work on XP machine, 2003 access.
Any hint?


Try querydefs.refresh before (re)creating the query.

--
Paul
Mar 24 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.