Solved

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

Posted on 2002-04-21
12
230 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 44

Expert Comment

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

Expert Comment

by:pkolbus
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 44

Expert Comment

by:bruintje
Comment Utility
hmmm those regional settings where already mentioned.....
0
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 200 total points
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
Hi WarrenGlass,

Glad I could help!

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now