Solved

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

Posted on 2002-04-21
12
237 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Any Way to Print an Import Spec? 3 32
Binding recordsets to a form 6 27
aggregate query? 20 53
Filter a form 8 15
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …

831 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