> You can't use DMax on a recordset.
>
> Try:
>
> !pat_id = Nz(DMax("pat_id", "dbo_A", "lname = '" & _
> Trim(Me![lname]) & "' and fname='" & Trim(Me![fname]) & "'"), 0) + 1
>
> Using single quotes on names, though, will lead to trouble when the name has
> an apostrophe in it (D'Arcy, O'Reilly, etc.) You'd be better off using
>
> !pat_id = Nz(DMax("pat_id", "dbo_A", _
> "lname = '" & Replace(Trim(Me![lname]), "'", "''") & _
> "' and fname='" & Replace(Trim(Me![fname]), "'", "''") & "'"), 0) + 1
>
> --
> Doug Steele, Microsoft Access MVP
>
http://I.Am/DougSteele> (no e-mails, please!)
>
>
> "MN" <MN[ at ]discussions.microsoft.com> wrote in message
> news:171B0225-F361-494B-A7D1-DFF29DFA77EC[ at ]microsoft.com...
> > ....
> > Set RsA = CurrentDb.OpenRecordset("dbo_A", dbOpenDynaset, dbSeeChanges)
> > strSQL = "SELECT * FROM dbo_A WHERE lname = '" & Trim(Me![lname]) & "' and
> > "
> > strSQL = strSQL + "fname='" & Trim(Me![fname]) & " '"
> > Set rst = CurrentDb.OpenRecordset(strSQL)
> > If rst.RecordCount > 0 Then
> > If (Me![lname] = rst![lname]) And (Me![fname] = rst![fname]) Then
> > DoCmd.OpenForm "frmA", , , "Lname = '" & Me!lname & _
> > "' And fname = '" & Me!fname & "'"
> > End If
> > Else
> > Ans = MsgBox("No Record FOUND -- Do you want Add New record ?", vbYesNo)
> > Select Case Ans
> > Case vbYes
> > With RsA
> > .AddNew
> > !pat_id = Nz(DMax(pat_id, RsA), 0) + 1
> > .upDate
> > .....
> > Odbc link Sybase DB. Fields pat_ID is number, Long int, Primary, index, no
> > duplicate. Some how I couldn't add new record. Error "Type mismatch" and
> > the
> > field pat_ID=null (?) Thanks you in advance !
>
>
>