Message box to confirm entry of a duplicate value

[david.paton]

Hi,

I have a single table in my database and I have to make a form for entering contact details for clients, staff, funding bodies and everyone else into this table. There is a phone number and I wanted help with what code I would use the get it to check if the number already exists.

If it does, I wanted it to return a custom message telling you what entries it already exists for by displaying certain fields so that they can be identified, such as name and organisation, but then I wanted the custom message to allow you to confirm entry of the data anyway. This is because there may have been an error in entry or as some people may be contacted on the same phone number, I want it to allow the option to continue and enter the data anyway.

Thanks,
Dave

 

[JHB]

Place the below code in the before update event for the phone number control.
Remember to replace the names with yours.

Private Sub YourPhoneControlName_BeforeUpdate(Cancel As Integer)
  Dim FoundNotFound As Variant, Response As Variant
  FoundNotFound = DLookup("YourFieldNameYouWantToDisplay", "YourTableName", "YourPhoneFieldName='" & Me.YourPhoneControlName & "'")
  If Not IsNull(FoundNotFound) Then
    Response = MsgBox("Phone no found, do you want to insert anyway?", vbYesNo)
    If Response = vbNo Then
      Cancel = True
    End If
  End If
End Sub

 

[david.paton]

Thanks for that, one thing I don't understand but, what does it mean when it says "YourFieldNameYouWantToDisplay" or what do you enter here?

Thanks,
Dave

 

[david.paton]

I got it working but all it does is tell you a phone number already exists if it is in the database but allows you to confirm entry. How do I insert the custom message? For instance, I want the message to read "This number already exists for Bob Brown of Telstra" With Bob being the field name: "First_Name", Brown being the field name: "Surname" and Telstra being the field name: "Business_Name".

Thanks,
Dave

 

[JHB]

Thanks for that, one thing I don't understand but, what does it mean when it says "YourFieldNameYouWantToDisplay" or what do you enter here?

Thanks,
Dave

The fieldname in the table whose value you want to return, (see the whole explanation in the MS-Access Help-file, search word = DLookup).

 

[JHB]

How do I insert the custom message? For instance, I want the message to read "This number already exists for Bob Brown of Telstra" With Bob being the field name: "First_Name", Brown being the field name: "Surname" and Telstra being the field name: "Business_Name".

Thanks,
Dave

You can use 3 DLookup but could be to time consuming, (and it more or less silly), then it is better to get it all at once using a recordset.

  Dim Response As Variant
  Dim dbs As DAO.Database, rst As Recordset
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT [First_Name], First_Name, [Surname], [Business_Name] " _
  & "FROM YourTableName " _
  & "WHERE YourPhoneFieldName='" & Me.YourPhoneControlName & "'")
  If Not rst.EOF Then
    Response = MsgBox("This number already exists for " & rst![First_Name] & " " & rst![Surname] & " of " & rst![Business_Name], vbYesNo)
    If Response = vbNo Then
      Cancel = True
    End If
  End If

 

[david.paton]

Sorry if I seem a little slow with this, I was just unsure about a couple of things. Where it says "YourPhoneControlName", I don't understand what this is referring to.

I also don't know where to put the code, do I put it after the previous code, in the BeforeUpdate control?

Thanks,
Dave

 

[JHB]

Sorry if I seem a little slow with this, I was just unsure about a couple of things. Where it says "YourPhoneControlName", I don't understand what this is referring to.

I do not know your database and what you are calling controls, field names in you tables and table names therefore I try to use some terms that tells what to put in there.
Do you not have a control in which you type in the phone number?
"YourPhoneControlName" has to be replaced by this control name.
How would you explain it, if you have to?

I also don't know where to put the code, do I put it after the previous code, in the BeforeUpdate control?

Thanks,
Dave

The previous code has to be replaced by the new one.
If you can't get it post you database with some sample data, (zip it if you haven't post 10 post yet)

 

[david.paton]

I was getting a error when it compiled of the line:
Response = MsgBox("This number already exists for " & rst![First_Name] & " " & rst![Surname] & " of " & rst![Business_Name], vbYesNo)

I have uploaded my database as I don't know what I am doing.

 

[david.paton]

Let me rephrase that, I meant to say, I don't know what I am doing wrong, not that I have no idea what I am doing as I have a fair bit of knowledge on access but not a lot of programming knowledge.

 

[JHB]

Remove the "[First_Name]," from the below line:

  Set rst = dbs.OpenRecordset("SELECT [COLOR=Red][B][First_Name],[/B][/COLOR] First_Name, [Surname], [Business_Name] " _ 

The result should then be:

Set rst = dbs.OpenRecordset("SELECT First_Name, [Surname], [Business_Name] " _

 

[david.paton]

It is working fine now, thanks for that!