?lookup?

  • Thread starter Thread starter smannell
  • Start date Start date
S

smannell

I am working with an existing database of nineteen
fields. Field one is "LastFirstName". I am trying to set
up a validation rule that will not allow duplicate clients
in Field one. This needs to happen as data is being
entered into the table from applications received from the
clients. Some of these clients will be new, others will
have submitted an application the previous year. The
validation text should read "Entry is current client".
All other fields can be duplicated.

Thanks in advance for any help.

smannell
 
it can be done by making your field one as your primary key

but there will be problems...
sample
record 1 - AAAAA BBBBBBB (last first format)

what if the encoder type it as BBBBBB AAAAA (thinking or
based on form that its in last firstname format)?

i suggest you to design your tables good enough to handle
all this simple kind of problems..

splitting the first and last is one...
 
Everything here is done LastFirst, so that is not an
issue. There will be multiple entries with the same last
name (ie. 100+ Smiths), same goes for first name. I need
this to happen as it's being entered, what do I enter as
the validation rule to give me what I want?

smannell
 
I am working with an existing database of nineteen
fields. Field one is "LastFirstName". I am trying to set
up a validation rule that will not allow duplicate clients
in Field one.
Click to expand...

Ummm... this is a Very Bad Idea.

I have three friends named Fred Brown. Names are *not* unique; it's
very possible that you might have two clients who happen to share the
same name!

In addition, if you're storing a name, it's generally much better to
have separate firstname and lastname fields. They can be concatenated
dynamically for display purposes.
This needs to happen as data is being
entered into the table from applications received from the
clients. Some of these clients will be new, others will
have submitted an application the previous year. The
validation text should read "Entry is current client".
All other fields can be duplicated.
Click to expand...

What you may want to consider instead is putting VBA code in the
BeforeUpdate event of the form; use DLookUp() to find previous clients
with the same name and give the user the *option* of adding this as a
new person (who happens to have the same name) or to cancel the
addition.
 
Well, first, I'd recommend you use an input form to enter
clients. Then, in the AfterUpdate event of your
field "one," enter this code:

On Error GoTo Err_LastFirstName_AfterUpdate
If DCount
("[LastFirstName]", "[qryYourUnderlyingTableOrQuery]", "[L
astFirstName]=Forms!frmYourClientInputForm!
[LastFirstName]") <> 0 Then
Beep
MsgBox "Entry is current client!", 16, "Current
Client"
End If

Exit_LastFirstName_AfterUpdate:
Exit Sub

Err_LastFirstName_AfterUpdate:
MsgBox Error$
Resume Exit_LastFirstName_AfterUpdate
 
Ok John. I am totally new to Access, so forgive me.
What's a VBA code? Will the "BeforeUpdate" event happen
in form as well as table format of the database?

Thanks
Suzanne
 
Ok John. I am totally new to Access, so forgive me.
Click to expand...

Forgive *me* - I should have asked your level of experience first
before enrolling you in the "Deep End School of Swimming".

VBA is Visual Basic for Applictions - the Modules that you can see on
the Modules tab or in form Event procedures.
What's a VBA code? Will the "BeforeUpdate" event happen
in form as well as table format of the database?
Click to expand...

Forms have events - tables don't. I'd really recommend using *only*
Forms for updating data; you can put a BeforeUpdate event on the form
but not on the table! On the table you're limited to the very
restrictive validation rule which would mean that if you have two
clients both named "Smith, William" it would simply be *impossible* to
enter both of them.

To create the BeforeUpdate event, open the Form in design mode; view
its Properties; and on the Event tab click the ... icon by the line
"Before Update" and select Code Builder. Access will put you into the
VBA editor with a Sub and End Sub line provided free of charge; edit
it to something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer ' allocate a variable for user's answer
' Use the DLookUp function to look up existing records with this name
' The multiple quotes allow names with apostrophes - two " inside a
' string translates to a single " so you'll be looking up (e.g.)
' [LastFirstName] = "O'Brien, Robert"
If Not IsNull(DLookUp("[LastFirstName]", "[YourTableName]", _
"[LastFirstName] = """ & Me![LastFirstName] & """")) Then
iAns = MsgBox("This person's name is already in. Add anyway?", _
vbYesNo)
If iAns = vbNo Then
Cancel = True
Me.Undo ' erase the form
End If
End If
End Sub
 
Wow! It'd be easier to do it in Excel! I'll give it
shot tomorrow while at work, and post again when i get
totally lost again!

Thanks a lot for the info and being patient with me!

Suzanne
-----Original Message-----
Ok John. I am totally new to Access, so forgive me.
Click to expand...

Forgive *me* - I should have asked your level of experience first
before enrolling you in the "Deep End School of Swimming".

VBA is Visual Basic for Applictions - the Modules that you can see on
the Modules tab or in form Event procedures.
What's a VBA code? Will the "BeforeUpdate" event happen
in form as well as table format of the database?
Click to expand...

Forms have events - tables don't. I'd really recommend using *only*
Forms for updating data; you can put a BeforeUpdate event on the form
but not on the table! On the table you're limited to the very
restrictive validation rule which would mean that if you have two
clients both named "Smith, William" it would simply be *impossible* to
enter both of them.

To create the BeforeUpdate event, open the Form in design mode; view
its Properties; and on the Event tab click the ... icon by the line
"Before Update" and select Code Builder. Access will put you into the
VBA editor with a Sub and End Sub line provided free of charge; edit
it to something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer ' allocate a variable for user's answer
' Use the DLookUp function to look up existing records with this name
' The multiple quotes allow names with apostrophes - two " inside a
' string translates to a single " so you'll be looking up (e.g.)
' [LastFirstName] = "O'Brien, Robert"
If Not IsNull(DLookUp
Click to expand...
("[LastFirstName]", "[YourTableName]", _
"[LastFirstName] = """ & Me![LastFirstName] & """")) Then
iAns = MsgBox("This person's name is already in. Add anyway?", _
vbYesNo)
If iAns = vbNo Then
Cancel = True
Me.Undo ' erase the form
End If
End If
End Sub



.
Click to expand...
 
it can be done by making your field one as your primary key

but there will be problems...
sample
record 1 - AAAAA BBBBBBB (last first format)

what if the encoder type it as BBBBBB AAAAA (thinking or
based on form that its in last firstname format)?

i suggest you to design your tables good enough to handle
all this simple kind of problems..

splitting the first and last is one...
Click to expand...
 
Back
Top