ACCESS FEED: RE: [MS_AccessPros] RE: FindFirst method syntax

Selasa, 17 September 2013

RE: [MS_AccessPros] RE: FindFirst method syntax

Hi Phucon

I'm glad you have got it working :-)

One thing you should be aware of, regarding the Seek method, is that a linked table cannot be opened as a table-type Recordset, and therefore Seek will not work. As your application grows, and especially when you put it into production, you should split it into a back-end containing the data and a front-end containing the code and linked tables. At that point you will need to convert all your Seek methods to FindFirst.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of saigonf7q5@yahoo.com
Sent: Wednesday, 18 September 2013 08:32
To: MS_Access_Professionals@yahoogroups.com
Subject: [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
>
>
>