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]

Re: VBA reference problem

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" <goxmail77_MOVE _@gmail.com> wrote in message
news:dum4g0$1ug $1@ss405.t-com.hr...[color=blue]
> 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??[/color]

[Gox]

Re: VBA reference problem

Allen, thanks for reply.

How determine which SP for office I have?


Allen Browne wrote:[color=blue]
> Disable Sandbox mode:
> http://support.microsoft.com/kb/239482/en-us
>[/color]

[Allen Browne]

Re: VBA reference problem

Help | About lets you know your Office service pack.

To know your JET 4 service, pack, locate msjet40.dll (typically in
windows\system3 2), 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" <goxmail77_MOVE _@gmail.com> wrote in message
news:dum8u0$c31 $1@ss405.t-com.hr...[color=blue]
> Allen, thanks for reply.
>
> How determine which SP for office I have?
>
>
> Allen Browne wrote:[color=green]
>> Disable Sandbox mode:
>> http://support.microsoft.com/kb/239482/en-us[/color][/color]

[Gox]

Re: VBA reference problem

Allen Browne wrote:[color=blue]
> Help | About lets you know your Office service pack.
>
> To know your JET 4 service, pack, locate msjet40.dll (typically in
> windows\system3 2), 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
>[/color]

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]

Re: VBA reference problem

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" <goxmail77_MOVE _@gmail.com> wrote in message
news:dvu0i4$sm5 $1@ss405.t-com.hr...[color=blue]
> Allen Browne wrote:[color=green]
>> Help | About lets you know your Office service pack.
>>
>> To know your JET 4 service, pack, locate msjet40.dll (typically in
>> windows\system3 2), 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
>>[/color]
>
> My version is 4.0.8618.0 and others clients too.
> Still I have not a solution of my problem.
>
> Any others suggestions?[/color]

[Gox]

Re: VBA reference problem

Allen Browne wrote:[color=blue]
> Let's try re-registering the VBA library.
>[/color]


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

And another problem with dynamic query.
First of all, I run my function queryIsExist(My QueryName) /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(My QueryName 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!T ables.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(My QueryName) Then
db.QueryDefs.De lete MyQueryName
End If
Set qdf = db.CreateQueryD ef(MyQueryName, strSQL)


Line: db.QueryDefs.De lete MyQueryName
don't work, because I got a error: 3012. Object <name> already exists.
on line below: Set qdf = db.CreateQueryD ef(MyQueryName, strSQL)

Trying to create a object, before deleting existing object.

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

[Allen Browne]

Re: VBA reference problem

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" <goxmail77_MOVE _@gmail.com> wrote in message
news:e0094d$h80 $1@ss405.t-com.hr...[color=blue]
> Allen Browne wrote:[color=green]
>> Let's try re-registering the VBA library.
>>[/color]
>
>
> Allen sorry, but no result. Same error, same problem.
>
> And another problem with dynamic query.
> First of all, I run my function queryIsExist(My QueryName) /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(My QueryName 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!T ables.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(My QueryName) Then
> db.QueryDefs.De lete MyQueryName
> End If
> Set qdf = db.CreateQueryD ef(MyQueryName, strSQL)
>
>
> Line: db.QueryDefs.De lete MyQueryName
> don't work, because I got a error: 3012. Object <name> already exists.
> on line below: Set qdf = db.CreateQueryD ef(MyQueryName, strSQL)
>
> Trying to create a object, before deleting existing object.
>
> This not work on XP machine, 2003 access.
> Any hint?
>[/color]

[kaniest]

Re: VBA reference problem

Gox schreef:[color=blue]
> If queryIsExist(My QueryName) Then
> db.QueryDefs.De lete MyQueryName
> End If
> Set qdf = db.CreateQueryD ef(MyQueryName, strSQL)
>
>
> Line: db.QueryDefs.De lete MyQueryName
> don't work, because I got a error: 3012. Object <name> already exists.
> on line below: Set qdf = db.CreateQueryD ef(MyQueryName, strSQL)
>
> Trying to create a object, before deleting existing object.
>
> This not work on XP machine, 2003 access.
> Any hint?[/color]

Try querydefs.refre sh before (re)creating the query.

--
Paul