Solved

Trouble with VBA Code that use to work????

Posted on 2002-04-21
12
243 Views
Last Modified: 2008-03-17
A customer got a new computer, loaded the same version of Access and moved over my application.  He ran the Link Manager to re-ink the tables. (Split database) The references are the same and in the same order.  Everything works in the application except the following piece of code.  It comes up with my error message.  Any suggestions?

Warren


Public Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim FirstOfMonth As Date
Dim LastOfMonth As Date
Dim M As Integer
Dim Y As Integer
M = Combo7                                  
Y = Combo12

    M = Me.MonthNumber
    Y = Me.Combo12
    FirstOfMonth = DateSerial(Y, M, 1)
       
        If (M < 12) Then
            LastOfMonth = CVDate(DateSerial(Y, M + 1, 1) - 1)
        Else
            LastOfMonth = DateSerial(Y, 12, 31)
        End If
   
    Me.Text20 = FirstOfMonth
    Me.Text21 = LastOfMonth

    Dim dtBgn As Date
    Dim dtEnd As Date
   
    dtBgn = FirstOfMonth
    dtEnd = LastOfMonth
   
Dim dbsCoach As Database
Dim rstTmp As Recordset
Dim rstWrk As Recordset
Dim sNID As String
Dim dtWDate As Date
Dim sWDesc As String
Set dbsCoach = CurrentDb()

CurrentDb.Execute "DELETE * FROM tblTmpWork"
Set rstTmp = dbsCoach.OpenRecordset("tblTmpWork", dbOpenDynaset)

If IsNull(Me.Combo27) Then
Set rstWrk = dbsCoach.OpenRecordset("SELECT * FROM WorkTbl " _
& "WHERE [DateOfWork] Between #" & dtBgn & "# And #" & dtEnd _
& "# ORDER BY [PersonID], [DateOfWork], [WorkDescription]")
Else
Set rstWrk = dbsCoach.OpenRecordset("SELECT * FROM WorkTbl " _
& "WHERE [DateOfWork] Between #" & dtBgn & "# And #" & dtEnd _
& "# AND [PersonID]= " & Forms![Choose A Report Form].[Combo27].Column(0) _
& " ORDER BY [PersonID], [DateOfWork], [WorkDescription]")
End If

sNID = ""
dtWDate = #1/1/1980#
sWDesc = ""

Do Until rstWrk.EOF
 If (rstWrk![PersonID] <> sNID) Or (rstWrk![DateOfWork] <> dtWDate) Then
   sNID = rstWrk![PersonID]
   dtWDate = rstWrk![DateOfWork]
   sWDesc = rstWrk![WorkDescription]
 Else
   sWDesc = sWDesc & ", " & rstWrk![WorkDescription]
 End If
 
 rstWrk.MoveNext
 
 If rstWrk.EOF Then
   rstTmp.AddNew
   rstTmp![PersonID] = sNID
   rstTmp![DateOfWork] = dtWDate
   rstTmp![WorkDescription] = sWDesc
   rstTmp.Update
 ElseIf (rstWrk![PersonID] <> sNID) Or (rstWrk![DateOfWork] <> dtWDate) Then
   rstTmp.AddNew
   rstTmp![PersonID] = sNID
   rstTmp![DateOfWork] = dtWDate
   rstTmp![WorkDescription] = sWDesc
   rstTmp.Update
 End If
Loop
Set rstTmp = Nothing
Set rstWrk = Nothing
 
    Dim stQueryName As String
    Dim stDocName As String
    Dim StQueryWorkTbl As String
    Dim stQueryNameBalance As String
    Dim stQueryWorkTblBalance As String
       
    stQueryName = "QueryWorkTblBetweenDatesCT"
    stQueryNameBalance = "QueryWorkTblBeforDateCT"
    stDocName = "CalendarReport2"
    StQueryWorkTbl = "WorkTbl_Crosstab"
    stQueryWorkTblBalance = "Worktbl_CrosstabBalance"
       
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stQueryName
    DoCmd.OpenQuery stQueryNameBalance
    DoCmd.OpenReport stDocName, acPreview
    DoCmd.RunCommand acCmdZoom100
    DoCmd.Maximize
    Me![Combo27] = Null
    [Combo27].SetFocus
       
Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox "Must Choose Year and Month" 'Err.Description
    Resume Exit_Command17_Click
   
End Sub
0
Comment
Question by:WarrenGlass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6958517
Hi WarrenGlass,

-what was the err.description?
-maybe it could be something in the regional settings since it is about date settings

:O)Bruintje
0
 
LVL 1

Author Comment

by:WarrenGlass
ID: 6958559
Hi Bruintie,

It just comes up with my error message dialog box "Must Choose Year and Month" which, as you can see from the code could be any error anywhere in the code.  Since it has worked for several years on several computers, I guest it is a setting from a freshly installed Access 2000 that has something set differently.  

Warren
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6958630
Hi Warren,

now i see, thought you also had something like & err.description which would be more helpfull

is it not an option to include this to see what goes wrong? or write it to a error log just for debugging purposes? it would be easier to pinpoint and you can take it later

:O)Bruintje
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 44

Expert Comment

by:bruintje
ID: 6958631
i meant take it out later when it's fixed
0
 
LVL 1

Expert Comment

by:pkolbus
ID: 6958655
Warren,

One thing which always causes me trouble in VB/VBA apps that use dates are assumptions about date formats.

Take a look at the Regional Settings in the Windows Control Panel.  Compare the Date settings between this machine and another that works.

There are VBA functions that allow code to be more date-independent - Format(), Date(), Year(), Month(), DateSerial(), etc.

Peter
0
 
LVL 35

Expert Comment

by:David Todd
ID: 6958812
Hi,

If the machine has just been bought then it is set for US region.

I'm in New Zealand and we use dd/mm/yy for our dates.

I'm amazed how often other outside computer professionals set up client machines and don't set the region. I have an application we install/support that insists on the correct date format when the application loads ...

Regards
  David
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6958878
hmmm those regional settings where already mentioned.....
0
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 200 total points
ID: 6959233
Hi WarrenGlass,

Just to emphsis what was alrady said:

Mark the following code as remark (put single-quote in front of each line):

'Err_Command17_Click:
'   MsgBox "Must Choose Year and Month" 'Err.Description
'   Resume Exit_Command17_Click

and replace it With:
Err_Command17_Click:
   MsgBox Err.Number & ": " & Err.Description
   Resume Exit_Command17_Click

This will give us a clue regarding the origin of the error. After you resolve the problem, you can set Err_Command17_Click back to it's soriginal state.

Please post the Err number & description here.

Thanx!

Nosterdamus
0
 
LVL 57
ID: 6959734
Your going to have a problem with all the criteria checks as Access expects everything in the US format of MM/DD/YYYY.

The best way to handle this is to setup a fuction to properly format the passed date.  Something like this:

Function USAFormat(varDate As Variant) As Variant
   
    USAFormat = "#" & DatePart("m", varDate) & "/" & DatePart("d", varDate) & "/" & DatePart("yyyy", varDate) & "#"

End Function


Then do USAFormat(dtDate) in your query criteria.

Jim.
0
 
LVL 1

Author Comment

by:WarrenGlass
ID: 6964054
Hi Nosterdamus,

I used your code to bring up the error code and its description and of course it worked perfectly.  There was nothing wrong with my code.  The customer never installed any printer or printer driver on the new computer.  The error code was 2202 "You must install a printer before you can preview or print a report".  Problem solved. Thank you for your help!!

Thanks to everyone else that took the time to respond also.

Warren
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6964803
Hi WarrenGlass,

Glad I could help!

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6964811
To bruintje!

Follow the link bellow to collect some points for putting us on the right track...

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msaccess&qid=20292943

Thanx!

Nosterdamus
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question