Unique warning on multiple columns

[Ragnar]

Hello,
I'm very new both to here and Access, so bear with me.

I've built myself a contact management and mailing app, but there's a little function I'd really like to add.
I have two columns, Name and Surname, for which i'd like to be warned, when entering data, if there's another record with the same name and surname.
It should not be impossible to enter it anyway, I'd just like an alert of some sort.
Displaying a list of the already matching records would be great, but I don't want to ask for too much.

Thanks!

 

[ColinEssex]

Firstly, don't call a field "Name" as its a reserved word in Access and gets upset about it as is "Date"

Secondly, I would force the user to do a search prior to entering a new record. There are sample search databases in the samples forum.

Col

 

[erroldou]

It would certainly be possible to enter dublicate data. To change this, you need to open your table in design view and change the 'index' property for that field to 'Yes' (No Duplicates).

 

[Ian Mac]

Hi,

I wouldn't use Name either, I always use GivenName (just a preferance, as First Name in some cultures is the Family Name) as the 1st Name.

I'm bound to be complicating things for this, but think about the bigger picture in terms of other projects.
Consider the fact that people do have the same name as other people, AND (from experiance) like their names to be exact.

For example:

Victoria Dee and Victoria Dee don't have a middle name
Neither like the Name Vicky and certainly don't like Vikki
They are both married

What I would consider is using a SystemID to seperate them, this means you could have 2 Bob Smiths in your contacts but the sysID will be unique

It's down to what else will be unique, age, address etc.

I think the lookup, you should be after is when you search for your contact.
i.e. Which one do you want?

Victoria Dee, 9 Alpha Street

or

Victoria Dee, 57a Beta Street

The above would be far more benifical AND be better foundation for future projects.

BTW, when creating a Table of contacts, I tend to create a Query straight after which is:
SysID, Fullname: [GivenName] & " " & [Surname]

 

[Ragnar]

Hi all,
wow! thanks for the great responses!

Just to clarify a bit: no need to worry about Name being a column name: in fact, I'm Italian, i just translated the names I gave to the cols.

Uniquity to each row should be given by street address combined with the two name fields, since it's the only other significant data that's always present.
Unfortunately, in Italy we don't commonly use middle names... would make things easier.

I think I'll try expanding on the method given by Pat: I'll not only get the IDs, but the full row, and display the Address in the MessageBox together with the two names to allow for easy choice.

But I think I'll buy myself a manul first... I'm a professional PHP and Linux-based firmware coder, my skils on VB are lower than zero.
Any suggestions?
Online tutorials would be welcome as well.
Thanks to all!

 

[Access_Help]

The simplest method is to use DLookup() to determine if the value presently exists:

Dim strSearch as String
Dim varKey as Variant
strSearch = "FirstName = """ & Me.FirstName & """ And LastName = """ & Me.LastName & """"
varKey = DLookup("TheKeyField", "A table or query name", strSearch) 
If Not IsNull(varKey) Then
    If MsgBox("This name already exists for Customer # " & varKey & ".  Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then
   Else
        Cancel = True
        Me.Undo
    End If
End If

Hi everyone,

This is my first post! So I hope you will be able to help me!

I am using this bit of code on the beforeupdate event of my form. I am recieveing an error message 'data type mismatch' and i think this is due to a date field. Can any1 provide some input on how I can overcome this?

Here is my code: This is to check the date and period 1 booking. If Period one is booked on a particular date it is to throw up an error message.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSearch As String
Dim varKey As Variant
Dim BDate As Date
strSearch = "BDate = """ & Me.BDate & """ And Period1 = """ & Me.Period1 & """"
varKey = DLookup("FurtherBookingsID", "Furtherbookings", strSearch)
If Not IsNull(varKey) Then
    If MsgBox("Booking already exists Booking ID: " & varKey & ".  Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then
   Else
        Cancel = True
        Me.Undo
    End If
End If

Note BDate is a date field (short for to Booking Date)

 

[Access_Help]

Dates should be enclosed with # rather than ".

Do you mean like this?

strSearch = "BDate = #" & Me.BDate & "#" And Period1 = """ & Me.Period1 & """""

Why did you define BDate as a variable? Where is its value coming from?

I dont know, I was just trying different ways to get rid of the data type mismatch error i think. I'm pretty new to VB programming and just working through scripts i find on the net. Please excuse me for my limited knowledge!

Please help

 

[Access_Help]

Thank you Pat, That worked!

The only thing is that everytime I enter the same booking date although i select a different period it still comes up with an error message saying that booking already exists? This means it only works on 1 field which is the BDate field.
how can I change the query so that it does something like this:

If Booking date and period 1 and period 2 and period 3 entered in the form match a record in the table then MsgBox("This name already exists for Customer # " & varKey & ". Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes


Help! this is really annoying.

 

[Access_Help]

Pat or anyone ? any suggestions on how to resolve the above?