Check to see if Record Exists

[Guest]

This is going to be a lengthy explaination, but without it I will get
questions as to "why" it is being done this way; so forgive me in advance

I have a lookup form that list all the members enrolled with our company.
This list comes from the corporate office as a text file and is uploaded to a
sql backend. We can not get a live link! It contains strictly the member
demographics.

The first forms record source is simply a table that is created from the
download from the corp. office. The user looks up the member in this form
and then click a button to copy the member demograhics to the second form.

The second form has a different table as a record source (it contains more
information then just demographics). And not every member with the company
will require a record to be created in this database.

The code to copy the member demographics from the first form to the second
form works great until I try to add a DCount function to check and see if the
member exist in the second form table source prior to copying the information
over. The only field that needs to be checked for existance is the MemberID
field.

I just want to take the MemberID field from the first form (MemberLkp) and
check it against tblMemberInfo and if it exist I just want a msg box telling
the user that the record already exist and then exit the function, otherwise
if the member hasn't been created yet in the tblMemberInfo then continue on
with the copy function.

I have tried several variations of the DCount function and apparently am
just not getting the syntax right. I have tried setting a break point at the
beginning of the IF statement and it never breaks, so I can't even step
through it and see what I am doing wrong <arrrrrgh> ........ someone please
help --- what am I doing wrong.

Thank you so much!
Charlie

 

[Guest]

Hi Charlie

The dcount if statement should look something like this...

if dcount("*", "tblMemberInfo", "MemberID = " & Forms!MemberLkp.MemberID) >=
1 then
msgbox "Member Already Exists"
exit sub
end if

I've assumed MemberID is numeric.
If it is text then the 4rd parameter to dcount will be...
"MemberID = """ & Forms!MemberLkp.MemberID & """"

hth

If there are still peoblems, post the code you already have and any messages
you get.

Andy Hull

 

[Guest]

Hey Andy,

Thank you! However, looks like i did have the code right as I am now
getting the same error when I use your code.

When I run it, I am getting an error telling me that "M42949" is an invalid
column Name. "M42949" is the data in the field MemberID. Any thoughts as
the what the heck is going on??

Charlie

 

[John W. Vinson]

When I run it, I am getting an error telling me that "M42949" is an invalid
column Name. "M42949" is the data in the field MemberID. Any thoughts as
the what the heck is going on??

Charlie

If it's a Text field - which you didn't say! - you need syntactically required
quotemarks around the criterion. Try

dcount("*", "tblMemberInfo", "MemberID = '" & Forms!MemberLkp.MemberID & "'")

Spaced out for clarity, that's

"MemberID = ' " & Forms!MemberLkp.MemberID & " ' "

so that the criterion ends up being

MemberID = 'M42949'


John W. Vinson [MVP]

 

[Guest]

John, you were right, my bad!! Yes, it is a text field. Apparently I fat
fingered my " and my '.

Thank you, working great now.