I converted my database to sql server and on one of the forms I did an option which the user can send an email to only those invoices that are over 30 days old. I filtered the records by only showing those records that are over 30 days old here is the code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo err_form_open:

    DoCmd.OpenForm "frmEmailProjectManagers", acNormal, , "Now()-ProjectManagerDate>=30"

err_form_open:
    MsgBox Err.Description
End Sub

However, it seems that as an Access Project it does not work. It give me a run-time error '7874' saying that Microsoft Access can't find the object. Can anyone help me on this? Thank you

slypunk

__________________
slypunk

Now() is not supported by sql server. use GETDATE() instead. Also, if I remember correctly, there is a server filter property for the form that you may be able to use instead.



Sal

I tried with GetDate() but it gives me an error that the applyfilter action contains a filter name that cannot be applied. Any other suggestions? Thanks :)

slypunk

Did the code work before conversion? The only thing the conversion does is link tables to SQL Server instead of use Access ones and converts querydefs to views/stored procedures.
Is 'frmEmailProjectManagers' a valid form name?
Is the filter string correct? Where is 'ProjectManagerDate'?


--

Joe

Yes the code did work before conversion and the form name and field name are both correct. Do you think I have to use serverfilter somewhere in the code? Thanks :)

slypunk

sherr8, Did you receive a resolution to this issue? I am having exactly the same problem..ie. converted my Access Database to Access project, and the filter from the form will not work.

If if was resolved for you. could you let me know what you did.

Thanks

Annie

It's just a matter of converting the code to ado. Here is the code I used for the form I was having problems with. Let me know if this helps.

Option Compare Database
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Const SW_SHOWNORMAL = 1

Private Sub cmdSendMessage_Click()

Dim strInvoice As String
Dim cnInvoice As ADODB.Connection

'connect to current database
strInvoice = CurrentProject.BaseConnectionString 'this works for adp only
Set cnInvoice = New ADODB.Connection
With cnInvoice
    .CursorLocation = adUseServer
    .Open strInvoice
End With

Dim strInvNo As String
Dim rsInvoice As ADODB.Recordset

' Send a message for invoices over 30 days from PM Date"
strInvNo = (txtInvoiceNumber.Value)
Set rsInvoice = New ADODB.Recordset
'CreateObject ("ADODB.Recordset")

With rsInvoice
    .CursorLocation = adUseServer
    Form_frmEmailProjectManagers.txtInvoiceNumber.SetF ocus
End With

If Now() - ProjectManagerDate >= 30 And IsNull(ReceivedApproval) Then
     sendmessage "", "", "", "Expired Invoice", "Invoice Number " & strInvNo & " has expired. Please notify me with further information. Thank you"
Else
    MsgBox "Message cannot be sent because invoice is not over 30 days!"
End If

Set cnInvoice = Nothing
Set rsInvoice = Nothing
End Sub


Private Sub sendmessage(strEmail As String, _
                        strCC As String, _
                        strBCC As String, _
                        strSubject As String, _
                        strBody As String)
    On Error GoTo errorroutine
    Dim stext As String
    Dim sAddedtext As String

    If Len(strEmail) Then
        stext = strEmail
    End If
    If Len(strCC) Then
        sAddedtext = sAddedtext & "&CC=" & strCC
    End If
    If Len(strBCC) Then
        sAddedtext = sAddedtext & "&BCC=" & strBCC
    End If
    If Len(strSubject) Then
        sAddedtext = sAddedtext & "&Subject=" & strSubject
    End If
    If Len(strBody) Then
        sAddedtext = sAddedtext & "&Body=" & strBody
    End If
    'If Len(txtAttachment) Then
    ' sAddedtext = sAddedtext & "&Attach=" & Chr$(34) & Me!txtAttachment & Chr$(34)
    'End If

    stext = "mailto:" & stext

    If Len(sAddedtext) <> 0 Then
        Mid$(sAddedtext, 1, 1) = "?"
    End If

    stext = stext & sAddedtext

    ' launch default e-mail program
    If Len(stext) Then
        Call ShellExecute(Me.hwnd, "open", stext, vbNullString, vbNullString, SW_SHOWNORMAL)
    End If

    ' Normal Completion
    Exit Sub

errorroutine:
    MsgBox Err.Description
End Sub


Private Sub Form_Open(Cancel As Integer)
'displays only the invoices that are over 30 days
    Me.ServerFilter = "GetDate()-ProjectManagerDate>=30"
    Me.Refresh
End Sub


slypunk

Hey, you should try using the Now function with out the parameters...
So don't use 'Now()', use 'Now'

I am having a problem with the Int() function...any clues?

"Live as if you are going to die today, but plan as if you will live tomorrow"

Oh, im sorry, that won't work!!!
Instead of using Now(), use GetDate()
That is the mssql equivalent...
to convert a string to an int (my problem)...you have to do the following
cast( string_var) as int
yeah!

"Live as if you are going to die today, but plan as if you will live tomorrow"