Help with this code please.

[MN]

.....
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 !

 

[Douglas J. Steele]

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

 

[MN]

Wow - You are wonderful ...
Best Regards!

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

(no e-mails, please!)

....
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 !