VBA reference problem

[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??

[Allen Browne]

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??

[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

[Allen Browne]

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

[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?

[Allen Browne]

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?

[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?

[Allen Browne]

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?

[kaniest]

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