Issue with checking if number already exists using .FindFirst

  • Thread starter Thread starter Rhys Davies
  • Start date Start date
R

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
 
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
 
used your dlookup solution instead.

thanks very much for your help

Paolo said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top