ACCESS FEED: [MS_AccessPros] RE: FindFirst method syntax

Selasa, 17 September 2013

[MS_AccessPros] RE: FindFirst method syntax

Thanks Duane and Graham

I played with the 6 different coding methods that Graham provided. They all worked great!

As a matter of fact, in the production db, I use the SEEK method (it works great) instead of the FIND method since it's table-type recordset. I played with the FIND methods just was because I wanted to learn more and experiment...

Except the date fields, all other fields are text data type. The procedure below is the one that's used in production db.

Thanks Duane

Phucon

Private Sub cmdSave_Click()
On Error GoTo ErrorHandler

If IsNull(Me.txtsAcctID) Or IsNull(Me.txtsName) Then
MsgBox "Acct ID and Standard Name are required fields." & vbCrLf & "Please enter the missing data.",

vbExclamation, "Information"
GoTo ExitProcedure
End If

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPled", dbOpenTable)

'rs.FindFirst "AcctId = " & Chr$(34) & Me.txtsAcctID & Chr$(34)
'rs.FindFirst "AcctId = " & "' & (Me.txtsAcctID) & '"
'rs.FindFirst "AcctId = " & "" & Me.txtsAcctID & ""

rs.Index = "sAcctID"
rs.Seek "=", Me.txtsAcctID

With rs
     If .NoMatch Then
        .AddNew
        !sAcctID = Me.txtsAcctID
        !sName = Me.txtsName
        !ZipCode = Me.txtZipCode
        !DateReceived = Me.txtDateReceived
        !sNotes = Me.txtsNotes
!ReceivedDate = Me.txtReceivedDate
!SignedDate = Me.txtSingedDate
        .Update
        .Close
      Else
          MsgBox "Cannot save record. This Acct ID already exist.", vbExclamation, "Invalid Account ID"
          GoTo ExitProcedure
      End If
End With

If MsgBox("New record saved. Close?", vbInformation + vbYesNo, "Save Record") = vbYes Then
      OKtoCloseFrm (Me.Name)
Else
     GoTo ExitProcedure
End If

ExitProcedure:
     TempVars.Remove "DateIsBlank"
     Exit Sub

ErrorHandler:
     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSave_Click"
     Resume ExitProcedure
     Resume
End Sub

--- In ms_access_professionals@yahoogroups.com, <duanehookom@...> wrote:

Phucon,
Please reply back with the significant field names and data types in the table "tblPled".

Duane Hookom MVP
MS Access

________________________________

> To: MS_Access_Professionals@yahoogroups.com
> From: saigonf7q5@...
> Date: Mon, 16 Sep 2013 15:25:36 -0700
> Subject: [MS_AccessPros] FindFirst method syntax
>
>
>
>
>
>
> Hello
>
> The syntax in my FindFirst method (in orange color) generated this error:
>
> Error 3070 (the Microsoft Access database engine does not recognize
> 'a101' as a valid field name or expression)
>
>
>
> I thought it's correct as the other 2 (color in blue). If I remember
> correctly, I think I have seen it somewhere, either 1 is correct. Am I
> wrong?
>
>
>
> Phucon
>
>
>
> Private Sub cmdSave_Click()
>
> On Error GoTo ErrorHandler
>
>
>
> If IsNull(Me.txtsAcctID) Or IsNull(Me.txtsName) Then
>
> MsgBox "Acct ID and Name cannot be left blank."
>
> GoTo ExitProcedure
>
> End If
>
>
>
> Set db = CurrentDb
>
> Set rs = db.OpenRecordset("tblPled", dbOpenDynaset)
>
>
>
> 'rs.FindFirst "AcctId = " & Chr$(34) & Me.txtsAcctID & Chr$(34)
>
>
>
> rs.FindFirst "AcctId = " & "' & (Me.txtsAcctID) & '"
>
>
>
> ' rs.FindFirst "AcctId = " & "" & Me.txtsAcctID & ""
>
>
>
> With rs
>
> If .NoMatch Then
>
> .AddNew
>
> !sAcctID = Me.txtsAcctID
>
> !sName = Me.txtsName
>
> .Update
>
> .Close
>
> Else
>
> MsgBox "Cannot save record. This Acct ID already
> exist.", vbExclamation, "Invalid Account ID"
>
> GoTo ExitProcedure
>
> End If
>
> End With
>
> ExitProcedure:
>
> TempVars.Remove "DateIsBlank"
>
> Exit Sub
>
>
>
> ErrorHandler:
>
> MsgBox "Error " & Err.Number & " (" & Err.Description & ")
> in procedure cmdSave_Click"
>
> Resume ExitProcedure
>
> End Sub
>
>
>