Issue with checking if number already exists using .FindFirst

[Rhys Davies]

Hi i am having some trouble getting this code to work. On exit of the
membership number field (which is not a primary key and is a text field) I
want it to check the relevant table and see if that number already exists and
just flag it up with a message box but it doesnt like my code on the
..findfirst line. Could someone help me please!

Thanks,

Rhys.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbldramaloan")
With rs
..MoveLast
..MoveFirst
..FindFirst ("[membershipno]='" & Me!membershipno & "'") 'doesnt like this line
If rs.NoMatch = False Then
MsgBox "You already have a record using this membership number. "
End if
End With

 

[Paolo]

Hi Rhys Davies,

If in the table tbldramaloan the field [membershipno] is defined as number
you must avoid the quotes so your findfirst will be

..FindFirst "[membershipno]=" & Me!membershipno 'doesnt like this line

An easyer way to do the same thing is using dlookup function in this way:

if not isnull(dlookup("membershipno","tbldramaloan","[membershipno]=" &
Me!membershipno then '' all on the same line
MsgBox "You already have a record using this membership number. "
endif

HTH Paolo

 

[Rhys Davies]

used your dlookup solution instead.

thanks very much for your help

Hi Rhys Davies,

If in the table tbldramaloan the field [membershipno] is defined as number
you must avoid the quotes so your findfirst will be

.FindFirst "[membershipno]=" & Me!membershipno 'doesnt like this line

An easyer way to do the same thing is using dlookup function in this way:

if not isnull(dlookup("membershipno","tbldramaloan","[membershipno]=" &
Me!membershipno then '' all on the same line
MsgBox "You already have a record using this membership number. "
endif

HTH Paolo

Hi i am having some trouble getting this code to work. On exit of the
membership number field (which is not a primary key and is a text field) I
want it to check the relevant table and see if that number already exists and
just flag it up with a message box but it doesnt like my code on the
.findfirst line. Could someone help me please!

Thanks,

Rhys.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbldramaloan")
With rs
.MoveLast
.MoveFirst
.FindFirst ("[membershipno]='" & Me!membershipno & "'") 'doesnt like this line
If rs.NoMatch = False Then
MsgBox "You already have a record using this membership number. "
End if
End With