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

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
LVL 1
WarrenGlassAsked:
Who is Participating?
 
NosterdamusConnect With a Mentor Commented:
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
 
bruintjeCommented:
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
 
WarrenGlassAuthor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
bruintjeCommented:
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
 
bruintjeCommented:
i meant take it out later when it's fixed
0
 
pkolbusCommented:
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
 
David ToddSenior DBACommented:
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
 
bruintjeCommented:
hmmm those regional settings where already mentioned.....
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
WarrenGlassAuthor Commented:
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
 
NosterdamusCommented:
Hi WarrenGlass,

Glad I could help!

Nosterdamus
0
 
NosterdamusCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.