run time error 2465

[karma]

Hi...Please help....this code works but with one problem the feilds [Item] and [EmailAddress] both require an exclamation mark ! in front of them for it to work, this makes the results of the feilds display the first record from the table where as the feilds [Job Number] and [Job Name] don't require the !. so i get the right results from them.


When i take the ! mark away from the feilds i get run time error 2465 "feild can't be found"...

Private Sub cmdJointApp_Click()
On Error GoTo Macro6_Err
Dim oOApp As Outlook.Application
Dim oOMail As Outlook.MailItem
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSubject As String
Dim strBody As String
Dim strEmail As String
Dim strJobNumber As String
Dim sql As String

With CodeContextObject
    strJobNumber = Me.Job_Number
End With
Call CombimneValuesJA

Set oOApp = CreateObject("Outlook.Application")
Set oOMail = oOApp.CreateItem(olMailItem)
Set db = CurrentDb
 
      Set rs = db.OpenRecordset("TempItems", dbOpenDynaset)
       With rs
       rs.FindFirst "[Job Number] = ""strJobNumber"""
       
      If Not rs.NoMatch Then
        Exit Sub
    End If
   
                        
            sql = ![Items]
            strSubject = ([JobNumber] & " " & [Job Name] & " items ready for Joint Approval")
            strBody = ("The Factory requires joint approval on the following items, " & Chr(13) & Chr(10) & sql)
            strEmail = ![EmailAddress]
        End With
With oOMail
.To = strEmail
.subject = strSubject
.Body = strBody
.Display
End With
Macro6_Err:
    MsgBox Error$
    Me.Undo
Exit Sub
End Sub

[orange]

What are the fields in table

"TempItems"

[John_G]

Hi -

It means that the two fields [Items] and [EmailAddress] are in the recordset rs., but are not recognized as fields or controls on your form. Which is it you want them to be?

The same idea goes for [Job Number] and [Job Name] - do you want the data from the recordset rs., or a form field?
![Job Number] and [Job Number] are not the same thing.

If as you say ![Items] and ![emailaddress] are showing the wrong values, it means that rs.FindFirst "[Job Number] = ""strJobNumber""" is not finding the correct record. Your logic is wrong here:

If Not rs.NoMatch Then
Exit Sub
End If

What this does is exit the sub if the findfirst SUCCEEDS - the opposite of what you want, no? That is why you always get the first record - if findfirst fails, it stays on the first record. You want to take the Not out: If rs.NoMatch Then....

HTH

John

[karma]

Hi John, thanks for the help . i have got it working. First of all the feilds [Job number] & [Job Name] where giving me the correct value, that was where i was stumped....I thought that the values where coming directly from the table after opening recordset...well i was wrong!!! So i took your advice and added the feilds to the form and bingo it works....

Once again thanks for the help.

Dave