Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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
0
WarrenGlass
Asked:
WarrenGlass
  • 4
  • 3
  • 2
  • +3
1 Solution
 
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
NosterdamusCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now