MS Access 2007 - controlling UI behaviour after attempt to insert duplicate record

Creating a simple UI using MS Access, hoping to do minimal actual coding (actually helping a friend who is not a coder).

Simplified requirement: Single table, primary key is phone number, lots of other non-mandatory fields. Display a form allowing just the phone number to be entered, if a record with that key exists display the full record, if a record with that key does not exist bring up an form allowing the other fields to be entered for this phone number and hence create a new record.

Q1: Any simple way to achieve this kind of function? Example?

We've got some of this going with a standard form, can execute code if insertion fails, but a standard dialogue box is displayed warning abou开发者_运维技巧t the duplciate key violation.

Q2: How can we trap that attempted insertion, avoid having the dialogue come up?

You will have to get your hands dirty and write some code to get this outcome. A starting point would be something like this presto code. Post back if you get stuck on any of the parts.

If fCheckIfRecordExists(lYourKey)=True then
    Docmd.OpenForm “frmEditExistingRecord”
Else
    Docmd.OpenForm “frmEnterNewRecord”
End if

Public function fCheckIfRecordExists (lYourKey as Long) as Boolean
‘Code to check if a record exists, simple method is to use dLookup or a count SQL statement with the criteria as the key you are trying to find
End function

EDIT:

First things first make a form with 1 text box called txtPhone_number and a command button called cmdSearch.

Next put this bit of code in the module behind the form

Public Function fDoes_record_exist(strPhone_number As String) As Boolean
If DCount("Phone_number", "tblYour_table", "Phone_number=" & strPhone_number) > 0 Then
    fDoes_record_exist = True
Else
    fDoes_record_exist = False
End If
End Function

Next you need to put some code behind the click event of the command button. This code can be expanded on to check for a valid phone number later if you want

If fDoes_record_exist(Me.txtPhone_number) = True Then
    DoCmd.OpenForm "frmShow_existing_record"
Else
    DoCmd.OpenForm "frmEnter_new_record"
End If

That should set you on your way nicely but post back if you run into problems

Here is an overview of the process with Access logic:

You need an unboud control labelled Phone in the form header, where user will be able to enter the phone number to search. You need to use the After_Update event of that control to trigger your search. There will be a second Phone control, bound this time, in the Detail section of the form for effective data entry/update.

Use the Form_Error event to intercept the error message when user tries to save a duplicate key, in order to display a nice message, and eventually Cancel his changes.

The advice from Kevin Ross to use VB Code is clearly one approach, and I think is appropropriate if we anticipate less trivial requirements in future. However I'm in a situation where I'm helping someone with zero coding background and hence if possible I'd prefer to let them use simple Macros rather than full-scale VB.

As it happens the functionality I require can be implemented with just Macros, and it depends on the suggestion from iDevelop.

The outline of the solution I used:

Create an InitialEntry form with no association to any particular table, it has:

• a data entry field for the telephone number
• a read-only text box where I can display a message
• a button labelled Add
• a button labelled Show

I write three macros:

1. A macro AlreadyExists that displays a message saying "We already that one"
2. A macro NewEntry that opens a data entry form for my table, in Add mode, and which copies the phone number from InitialEntry!TelephoneNumber

3. A macro TestForExisting this uses a condition

    DCount("*","MyTable","[PhoneNumber] = [FormPhoneNumber] " ) > 0
   

to control whether to execute AlreadyExists, and a similar test to control whether to call NewEntry.

While this is not as efficient as VB, it does seem to be understandable by a non-coder, so at least we can implement our application.