Detecting records present

[RM Powell]

OK, this is probably a dumb, newbie quesiton. But I've searched quite a
bit on this group and in the "help" files and haven't been able to find
the answer.

I run a crisis program for kids and families. I have a form that is
completed when a new person enters the program. The basic form includes a
client ID and demographic information with subforms for information about
the particular intake. Right now, you open the form in data entry mode and
start entering the demographic information. When you move to the subform,
the form will notify you that the record can't be saved because that
person is already in the database. What I want is for Access to tell me
that before I enter all the data. I'd like to enter the client ID (which
is the primary key for tblClient where this information is stored) and
have Access tell that person is already in the database rather than
waiting until I've entered all the data and am trying to move to the
subform.

Hope that makes sense. I assume I'd need some sort of AfterUpdate function
tied to the Client_ID field. Yes?

Thanks for any help. It's a small irritant, but one I'd like to fix before
other people start using this thing. I'm still pretty new to this.

Thanks again,

Randy

[Arno R]

Randy,
You need the before-update event of Client ID
Before_update can be cancelled so this is the event to validate.
So before_update you check if Client_ID is already present.
You can do this in several ways but perhaps DLookup is sufficient for the occasion.

If DLookup("[Client ID]", "tblClient" ,"[Client ID] = " & Forms!YourForm![Client ID]) > 0 then
Msgbox "Client already in db ..."
cancel=true
end if

BTW: Avoid using spaces in fieldnames.

--
Hope this helps
Arno R
"RM Powell" <no************ ****@charter.ne t> schreef in bericht
news:pa******** *************** *****@charter.n et...

OK, this is probably a dumb, newbie quesiton. But I've searched quite a
bit on this group and in the "help" files and haven't been able to find
the answer.

I run a crisis program for kids and families. I have a form that is
completed when a new person enters the program. The basic form includes a
client ID and demographic information with subforms for information about
the particular intake. Right now, you open the form in data entry mode and
start entering the demographic information. When you move to the subform,
the form will notify you that the record can't be saved because that
person is already in the database. What I want is for Access to tell me
that before I enter all the data. I'd like to enter the client ID (which
is the primary key for tblClient where this information is stored) and
have Access tell that person is already in the database rather than
waiting until I've entered all the data and am trying to move to the
subform.

Hope that makes sense. I assume I'd need some sort of AfterUpdate function
tied to the Client_ID field. Yes?

Thanks for any help. It's a small irritant, but one I'd like to fix before
other people start using this thing. I'm still pretty new to this.

Thanks again,

Randy

[David Neagle]

"RM Powell" <no************ ****@charter.ne t> wrote in message news:<pa******* *************** ******@charter. net>...

OK, this is probably a dumb, newbie quesiton. But I've searched quite a
bit on this group and in the "help" files and haven't been able to find
the answer.

I run a crisis program for kids and families. I have a form that is
completed when a new person enters the program. The basic form includes a
client ID and demographic information with subforms for information about
the particular intake. Right now, you open the form in data entry mode and
start entering the demographic information. When you move to the subform,
the form will notify you that the record can't be saved because that
person is already in the database. What I want is for Access to tell me
that before I enter all the data. I'd like to enter the client ID (which
is the primary key for tblClient where this information is stored) and
have Access tell that person is already in the database rather than
waiting until I've entered all the data and am trying to move to the
subform.

Hope that makes sense. I assume I'd need some sort of AfterUpdate function
tied to the Client_ID field. Yes?

Thanks for any help. It's a small irritant, but one I'd like to fix before
other people start using this thing. I'm still pretty new to this.

Thanks again,

Randy

Hi Randy. You need to place the code in the Before Update event
procedure of the ClientID control. Something like this should do the
trick.

Private Sub ClientIDControl Name_BeforeUpda te(Cancel As Integer)

If DCount("[ClientID]", "YourTablename" , "[ClientID]= '" &
Me![ClientID] & "'") = 1 Then
MsgBox "The Client ID you have entered is a duplicate"
Me.Undo
Cancel = True
End If
End Sub

[RM Powell]

On Thu, 27 Nov 2003 10:16:47 +0100, Arno R wrote:

Randy,
You need the before-update event of Client ID Before_update can be
cancelled so this is the event to validate. So before_update you check
if Client_ID is already present. You can do this in several ways but
perhaps DLookup is sufficient for the occasion.

If DLookup("[Client ID]", "tblClient" ,"[Client ID] = " &
Forms!YourForm![Client ID]) > 0 then
Msgbox "Client already in db ..."
cancel=true
end if

BTW: Avoid using spaces in fieldnames.

David and Arno,

Thanks for the help. I'll try it both ways. I figured you'd have to have
something in the control to tell it to look at the table for this.

Thanks again.

BTW: Sorry about the table name posting. It's actually Client_ID.

Thanks again,

Randy