Table Validation

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi

Using ACCESS 2000

I have a tbl.contacts with all the obvious contact details for my
'constituents'.

When my user is creating a new record, I want a validation rule that asks
the user IF they want to create a new record? OR use the existing record?
when there is a duplication of the [FirstName] & [LastName] fields.

Dependant on what option the user picks I want them to continue with
entering the new record or for them to be presented with the existing record.

Question -

a) I assume it's possible to do this and
b) how do I start? (will I do some expressions in the table that generates a
form with buttons that points the user to the right place)

Need help ...

TIA
 
You cannot do this with a validation rule in the table.

You could do it in the BeforeUpdate event of a form, testing for
Me.NewRecord. Cancel and undo before attempting to move record.
 
Hi Allen

Unfortunately I don't know how to write code. Do you have the sequence that
I could cut and paste into my BeforeUpdate event? Is there any additional
information I would need to give you?

TIA

--
Sue Compelling


Allen Browne said:
You cannot do this with a validation rule in the table.

You could do it in the BeforeUpdate event of a form, testing for
Me.NewRecord. Cancel and undo before attempting to move record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
Hi

Using ACCESS 2000

I have a tbl.contacts with all the obvious contact details for my
'constituents'.

When my user is creating a new record, I want a validation rule that asks
the user IF they want to create a new record? OR use the existing record?
when there is a duplication of the [FirstName] & [LastName] fields.

Dependant on what option the user picks I want them to continue with
entering the new record or for them to be presented with the existing
record.

Question -

a) I assume it's possible to do this and
b) how do I start? (will I do some expressions in the table that generates
a
form with buttons that points the user to the right place)

Need help ...

TIA
Click to expand...
Click to expand...
 
Sue Compelling said:
Hi Allen

Unfortunately I don't know how to write code. Do you have the sequence
that
I could cut and paste into my BeforeUpdate event?
Click to expand...

I can't write your code for you, Sue, but this is an example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If Me.NewRecord Then
strWhere = "(Surname = """ & Me.Surname & _
""") AND (FirstName = """ & Me.FirstName & """)"
varResult = DLookup("ClientNum", "tblClient", strWhere)
If Not IsNull(varResult) Then
If MsgBox("Name exists in Record " & varResult & ". Go there?",
_
vbYesNo, "DUPLICATE") = vbYes Then
Cancel = True
Me.Undo
With Me.RecordsetClone
.FindFirst "ClientNum = " & varResult
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End If
End Sub
 
Hi Allen

Thanks for the code suggestion though I’ve actually had a go at putting
something together to give me the results I want – but need a little HELP.
Pls don’t cringe at its clumsiness...

Firstly I created a query (qrydupnames) which runs on afterupdate [lastname]
which checks for duplicate records using [firstname]&[lastname] from my table
“Contactsâ€. This query does indeed do what I want it to do.

SELECT Min(([firstname] & [lastname])) AS FullName, Contacts.ID,
Contacts.Firstname, Contacts.Lastname, Contacts.HomePhone,
Contacts.WorkPhone, Contacts.MobilePhone, Contacts.Emailname, [address] &
Chr(13)+Chr(10) & [townorcity] AS CombinedAddress
FROM Contacts
GROUP BY Contacts.ID, Contacts.Firstname, Contacts.Lastname,
Contacts.HomePhone, Contacts.WorkPhone, Contacts.MobilePhone,
Contacts.Emailname, [address] & Chr(13)+Chr(10) & [townorcity]
HAVING (((Min(([firstname] &
[lastname])))=[Forms]![Contacts]![combinedName]));

Then I created a form (frmdupnames) based on this query, which has enough
information to allow the user to determine if it is a genuine duplicate. It
looks like a dialogue box though is in continuous view so that the user can
see both names.

Then I tried to make macros to assign to buttons on the (frmdupnames) that
sends the user to the record selected record. I tried this type of
expression ... [Forms]![frmcontacts]![combinedname} =
[Forms]![frmdupnames]![combinedname} but this has failed to work in all my
various attempts (findrecord, openforms, goto etc)

So, the help I need is:
• I only want my form (frmdupnames) to open if my query (qrydupnames) has
[ID] > 1. How do I do this?
• How do I tell ACCESS to go to either the; duplicate record or return to
the new record the user was creating? (i was wanting the user to be able to
click on a button).
• Lastly, if the user chooses the duplicate record, how do I tell access to
delete the new record they were creating?

Hoping you can help.
 
Sue, I've answered your question, and provided an example.

I will answer your immediate questions, explaining why the approach you
suggest is flawed, but I don't plan to continue this thread. As I have
already given you a solution, there's no point explaining why your
determination to go down a different approach won't work.

Specifics in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
So, the help I need is:
• I only want my form (frmdupnames) to open if my query (qrydupnames) has
[ID] > 1. How do I do this?
Click to expand...

You would need to DLookup() the query first to see if it has any responses.
• How do I tell ACCESS to go to either the; duplicate record or return to
the new record the user was creating? (i was wanting the user to be able
to
click on a button).
Click to expand...

You can't. If you put this in the Click event of a button rather than
Form_BeforeUpdate, the record will be saved in other ways where your code
doesn't run.
• Lastly, if the user chooses the duplicate record, how do I tell access
to
delete the new record they were creating?
Click to expand...

You can't. It may already have been created, and you did not get the ID of
the new value.
 
Thanks for the help to date .... cheers
--
Sue Compelling


Allen Browne said:
Sue, I've answered your question, and provided an example.

I will answer your immediate questions, explaining why the approach you
suggest is flawed, but I don't plan to continue this thread. As I have
already given you a solution, there's no point explaining why your
determination to go down a different approach won't work.

Specifics in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
So, the help I need is:
• I only want my form (frmdupnames) to open if my query (qrydupnames) has
[ID] > 1. How do I do this?
Click to expand...

You would need to DLookup() the query first to see if it has any responses.
• How do I tell ACCESS to go to either the; duplicate record or return to
the new record the user was creating? (i was wanting the user to be able
to
click on a button).
Click to expand...

You can't. If you put this in the Click event of a button rather than
Form_BeforeUpdate, the record will be saved in other ways where your code
doesn't run.
• Lastly, if the user chooses the duplicate record, how do I tell access
to
delete the new record they were creating?
Click to expand...

You can't. It may already have been created, and you did not get the ID of
the new value.
Click to expand...
 
Hi Allen

Also - point taken on all your feedback.

I have retried your code and after selecting yes to "Go there" I come back
with the following error;

Run-time error '3464':

Data type mismatch in criteria expression"

NOTE: my [ID] is a text field and will have number or alpha characters only
or both ...

TIA



Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If Me.NewRecord Then
strWhere = "(LastName = """ & Me.LastName & _
""") AND (FirstName = """ & Me.FirstName & """)"
varResult = DLookup("ID", "[Contacts]", strWhere)
If Not IsNull(varResult) Then
If MsgBox("Name exists in Record " & varResult & ". Go there?",
vbYesNo, "DUPLICATE") = vbYes Then
Cancel = True
Me.Undo
With Me.RecordsetClone
.FindFirst "ID = " & varResult
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End If
End Sub

--
Sue Compelling


--
Sue Compelling


Allen Browne said:
Sue, I've answered your question, and provided an example.

I will answer your immediate questions, explaining why the approach you
suggest is flawed, but I don't plan to continue this thread. As I have
already given you a solution, there's no point explaining why your
determination to go down a different approach won't work.

Specifics in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
So, the help I need is:
• I only want my form (frmdupnames) to open if my query (qrydupnames) has
[ID] > 1. How do I do this?
Click to expand...

You would need to DLookup() the query first to see if it has any responses.
• How do I tell ACCESS to go to either the; duplicate record or return to
the new record the user was creating? (i was wanting the user to be able
to
click on a button).
Click to expand...

You can't. If you put this in the Click event of a button rather than
Form_BeforeUpdate, the record will be saved in other ways where your code
doesn't run.
• Lastly, if the user chooses the duplicate record, how do I tell access
to
delete the new record they were creating?
Click to expand...

You can't. It may already have been created, and you did not get the ID of
the new value.
Click to expand...
 
if it is string it should matter but for the var result thing then
make it as string

Dim varResult As string

and bad on you making an id field text

what is being highlighted when the data missmatch error is displayed

Regards
Kelvan
 
if it is string it shouldn't matter but for the varresult thing then
make it as string

Dim varResult As string


and bad on you making an id field text


what is being highlighted when the data missmatch error is displayed?


Regards
Kelvan
 
Hey You - Lord Kelvan to the rescue -

Did you send your reposnse twice to make sure I got the message! ID field
text is bad, ID field text is bad. I'm afraid I made this DB structure as a
raw amateur 5 years ago and this is what you get when you let the uninitiated
loose with a table ...

Anway ...

highlighted line is ...

..FindFirst "ID = " & varResult
 
Hi Allen

.... read your hyperlink ... I tried ...

.FindFirst """ID = """ & varResult


and it came back with

Run-time error '3077':
Syntax error (missing operator) in expression

Obviously I haven't mastered my "" placements.

Also - would this be the only place I put the extra quotes?
 
Back
Top