Go Back   Access World Forums > Apps and Windows > Windows

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-26-2007, 02:22 AM   #1
Steve Lang
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Steve Lang is on a distinguished road
Access 2003 queries on Vista

Hi

I have a product working fine under XP written in VB (access 2003 SP2). Most of the program works fine on Vista business but displaying graphs throws up errors. I think i may have narrowed it down to the SQL in the queries.

I used the simple query wizard as a test, the SQL produced automatically caused an error message saying that the function is not available.

Here is the automated SQL:

SELECT DISTINCTROW Format$([MonthlyHoursInit].[Date], 'mmmm yyyy') AS [Date By Month], Sum(MonthlyHoursInit.DayCapDec) AS [Sum Of DayCapDec]
FROM MonthlyHoursInit
GROUP BY Format$([MonthlyHoursInit].[Date], 'mmmm yyyy'), Year([MonthlyHoursInit].[Date])*12+DatePart('m',[MonthlyHoursInit].[Date])-1;


Any help would be great.

Steve Lang is offline   Reply With Quote
Old 02-02-2007, 01:22 AM   #2
Dennisk
AWF VIP
 
Dennisk's Avatar
 
Join Date: Jul 2004
Posts: 1,649
Thanks: 0
Thanked 9 Times in 5 Posts
Dennisk will become famous soon enough
this sounds like a missing reference. Open a vba module and click tools/references. see if any references are flagged up a missing.
it may well be Visual Basic for applications
Dennisk is offline   Reply With Quote
Old 02-03-2007, 09:17 AM   #3
Steve Lang
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Steve Lang is on a distinguished road
Hi, Thanks for the reply.

You were right, i changed the ddl and security reference to 2.8 and that side is working fine.

BUT

I have a new problem, i am also using embedded graphs (ms graph) in access 2003, i dynamically change the SQL for the graph at runtime, the problem is that the graph does not display, if i click the graph or goto another form and then return i can see the graph. I have also added a graph slightly overlapping another and the topmost graph changes as required. This sounds like a graphic issue with Vista, i am guessing that it may be something to do with double buffering of graphics but i may be wrong.

Any help with this would be great

Thanks

Steve

Steve Lang is offline   Reply With Quote
Old 02-03-2007, 10:11 AM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
When you change the graph at run time you can try using either:
Me.Repaint:
Quote:
Originally Posted by Access database help file
The Repaint method completes any pending screen updates for a specified form . When performed on a form, the Repaint method also completes any pending recalculations of the form's controls .expression.Repaint
Return Value
Nothing

expression A variable that represents a Form object.

Remarks


Microsoft Access sometimes waits to complete pending screen updates until it finishes other tasks. With the Repaint method, you can force immediate repainting of the controls on the specified form. You can use the Repaint method:

When you change values in a number of fields. Unless you force a repaint, Microsoft Access might not display the changes immediately, especially if other fields, such as those in an expression in a calculated control , depend on values in the changed fields.
When you want to make sure that a form displays data in all of its fields. For example, fields containing OLE objects often don't display their data immediately after you open a form.
This method doesn't cause a requery of the database, nor does it show new or changed records in the form's underlying record source. You can use the Requery method to requery the source of data for the form or one of its controls.
Me.Refresh
Quote:
Originally Posted by Access Database Help File
The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment.expression.Refresh
Return Value
Nothing

expression A variable that represents a Form object.

Remarks


Using the Refresh method is equivalent to clicking Refresh on the Records menu.

Microsoft Access refreshes records automatically, based on the Refresh Interval setting on the Advanced tab of the Options dialog box, available by clicking Options on the Tools menu. ODBC data sources are refreshed based on the ODBC Refresh Interval setting on the Advanced tab of the Options dialog box. You can use the Refresh method to view changes that have been made to the current set of records in a form or datasheet since the record source underlying the form or datasheet was last refreshed.

In a Microsoft Access database , the Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source.
Me.Requery
Quote:
Originally Posted by Access Database Help File
The Requery method updates the data underlying a specified form by requerying the source of data for the form.expression.Requery
expression A variable that represents a Form object.

Remarks


You can use this method to ensure that a form or control displays the most recent data.

The Requery method does one of the following:

Reruns the query on which the form or control is based.
Displays any new or changed records or removes deleted records from the table on which the form or control is based.
Updates records displayed based on any changes to the Filter property of the form.
Controls based on a query or table include:

List boxes and combo boxes .
Subform controls.
OLE objects , such as charts .
Controls for which the ControlSource property setting includes domain aggregate functions or SQL aggregate function.
If you specify any other type of control for the object specified by expression, the record source for the form is requeried.

If the object specified by expression isn't bound to a field in a table or query, the Requery method forces a recalculation of the control.

If you omit the object specified by expression, the Requery method requeries the underlying data source for the form or control that has the focus . If the control that has the focus has a record source or row source, it will be requeried; otherwise, the control's data will simply be refreshed.

If a subform control has the focus, this method only requeries the record source for the subform, not the parent form.

Notes


The Requery method updates the data underlying a form or control to reflect records that are new to or deleted from the record source since it was last queried. The Refresh method shows only changes that have been made to the current set of records; it doesn't reflect new or deleted records in the record source. The Repaint method simply repaints the specified form and its controls.
The Requery method doesn't pass control to the operating system to allow Windows to continue processing messages. Use the DoEvents function if you need to relinquish temporary control to the operating system.
The Requery method is faster than the Requery action. When you use the Requery action, Microsoft Access closes the query and reloads it from the database. When you use the Requery method, Microsoft Access reruns the query without closing and reloading it.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-03-2007, 10:38 AM   #5
Steve Lang
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Steve Lang is on a distinguished road
Hi Bob

Thank you for taking the time to reply.

I have tried refreshing, requerying etc both the ole graph and the form but no change. If i leave the properties window open and then move it over the graphs they then paint correctly, this works fine under XP but not Vista.

Any more ideas?
Steve Lang is offline   Reply With Quote
Old 02-19-2007, 11:32 AM   #6
minniethecat
Registered User
 
Join Date: Feb 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
minniethecat is on a distinguished road
Quote:
Originally Posted by Steve Lang View Post
Hi Bob

Thank you for taking the time to reply.

I have tried refreshing, requerying etc both the ole graph and the form but no change. If i leave the properties window open and then move it over the graphs they then paint correctly, this works fine under XP but not Vista.

Any more ideas?
Hi, Steve. I have the same problem you have with graph.

If I iconize and then resize the form, the chart appears...strange...isn't it?

Have you find a way to solve it?

Thanks in advance

Enrico Berengan
minniethecat is offline   Reply With Quote
Old 02-19-2007, 11:39 AM   #7
Steve Lang
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Steve Lang is on a distinguished road
Hi Enrico

I have found a solution, its only temporary as i am moving to Dot Net with my product, in my opinion its a messy solution but it works.

I change the SQL for the graph when required, i add a timer interval for 1 second and then set focus to the chart and then to another object on the form, this forces a repaint displaying the charts correctly. Dont forget to reset the timer interval to zero.

As i said its messy but for a short term solution it has taken the pressure off whilst i move to dot net.

Regards

Steve

Steve Lang is offline   Reply With Quote
Old 02-19-2007, 11:52 AM   #8
minniethecat
Registered User
 
Join Date: Feb 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
minniethecat is on a distinguished road
Thanks for answering so fast.....

Do you think that me and you are the only persons having that kind of problem? I can't find any clue on the Net...Sounds strange.....

Thanks againg. Take care.

Enrico Berengan
minniethecat is offline   Reply With Quote
Old 02-19-2007, 11:56 AM   #9
Steve Lang
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Steve Lang is on a distinguished road
Enrico, you would think so, i have searched extensively on the net and came out with nothing.

Just another reason to leave MS Access.

Regards

Steve
Steve Lang is offline   Reply With Quote
Old 10-12-2007, 05:56 PM   #10
Algis Kuliukas
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Algis Kuliukas is on a distinguished road
Quote:
Originally Posted by Steve Lang View Post
Enrico, you would think so, i have searched extensively on the net and came out with nothing.

Just another reason to leave MS Access.

Regards

Steve
If you are leaving MS Access, what are you going to? .Net?

I hate the way Microsoft appear to be pulling the plug on Access. It's one of the most productive development environments ever and they should be putting more R & D into it as a web site rapid development platform not forcing developers to take the huge leap to .Net.

Algis
Algis Kuliukas is offline   Reply With Quote
Old 10-12-2007, 06:07 PM   #11
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Quote:
Originally Posted by Algis Kuliukas View Post
I hate the way Microsoft appear to be pulling the plug on Access. It's one of the most productive development environments ever and they should be putting more R & D into it as a web site rapid development platform not forcing developers to take the huge leap to .Net.
They aren't pulling the plug on it by any means. However, it is totally possible that they will move VBA from the VB6 based code to .NET based in coming versions. It would only make sense as it is much more robust and able to do things that VB6 can't.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 10-12-2007, 08:40 PM   #12
Algis Kuliukas
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Algis Kuliukas is on a distinguished road
Quote:
Originally Posted by boblarson View Post
They aren't pulling the plug on it by any means. However, it is totally possible that they will move VBA from the VB6 based code to .NET based in coming versions. It would only make sense as it is much more robust and able to do things that VB6 can't.
Thanks Bob. I hope you're right.

Do you have any advice on moving to 2003 apps to Access 2007. I use customised toolbars and menus extensively but they don't work in 2007 unless you use the rather clumsy Add-Ins menu. Why did they have to throw the spanner into this beautifully simple and pwoerful tool?

All the best

Algis Kuliukas
Algis Kuliukas is offline   Reply With Quote
Old 10-12-2007, 08:42 PM   #13
Algis Kuliukas
Registered User
 
Join Date: Oct 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Algis Kuliukas is on a distinguished road
PS

I also have a problem with Access 2003 apps running in Vista. The charts do not display unless you <doubleClick> them but that launches the Chart applet which is often a pain to then extracate one's self from.

Why do they have to mess things up every time?

Algis Kuliukas
Algis Kuliukas is offline   Reply With Quote
Old 10-12-2007, 11:29 PM   #14
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Quote:
Originally Posted by Algis Kuliukas View Post
PS

I also have a problem with Access 2003 apps running in Vista. The charts do not display unless you <doubleClick> them but that launches the Chart applet which is often a pain to then extracate one's self from.

Why do they have to mess things up every time?

Algis Kuliukas
Have you set the MSAccess.exe file under C:\Program Files\Microsoft Office\Office or Office11 to run as Administrator on Vista?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 10-13-2007, 04:05 AM   #15
Steve Lang
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Steve Lang is on a distinguished road
I have moved to .net, this hasn't been an easy task and some things that are so easy with access like reporting are more involved and i am also finding that i am writing a lot of low level code. The results so far are very good.

I also had the security problems with access, so i bought a digital certificate, then comes another problem, PC's with scandinavian regional settings do not recognise the id (i have tried 2, 1 from Thawte and also Verisign), this makes your software look amateurish.

The other side to .net that i like is my application runs on its own, and isnt affected by installing other products. I have had several customers of mine totally unable to use the software, either conflicting with access 2007 or a reference issue, it seemd the only way to solve this is with a format.

Access has been good to me, its easy to get started with but i think if you need greater control and connectivity then i think .net is the way to go.

Steve Lang is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deploying Access 2003 project in different Access versions Ayat General 2 12-29-2005 07:21 AM
Problems when using a DB created in Access 2002 (XP) in Access 2003 Raneil General 2 06-23-2005 02:59 PM
[SOLVED] Access 2003 Runtime Error - Function not available in expressions esvee Modules & VBA 4 04-15-2005 12:36 PM
Access 2003 to 2000 bug Skeletal General 12 03-19-2005 11:16 AM
Access 2003 on to Win NT 4 ! Tim L General 0 07-05-2004 10:42 AM




All times are GMT -8. The time now is 04:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World