Validation of data entered into a form

[vexing]

Ok,
I posted yesterday about having problems with code in a form

Here is what I've come up with do far:

Private Sub Customer__Name_LostFocus()

Dim vTruncate

vTruncate = 4
Forms!Customers![Customer ID] = Left(Customer__Name, vTruncate)

End Sub

Private Sub Customer_ID_NotInList(NewData As String, Response As Integer)

Dim ctl As Control

Set ctl = Me.Customer_ID

' Prompt user to verify they wish to add new value.
If MsgBox("Do you want to add this customer to the table?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Open dialog box for user to enter data and set new data as default value

DoCmd.OpenForm "Customers", , , , , acDialog
Forms!Customers![Customer ID] = NewData

'requery control to update list addition
Forms!Customers![Customer ID].Requery
Response = acDataErrContinue

Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

When I enter a new customer, the customer ID truncates fine. Problem is, what happens if there is already an ID in the table? I just get a generic, duplicate value error. I need to somehow be able to let the client verify that this is, indeed a new customer, and maybe add a number to the end of the Customer ID. The "On NotInList" code should then somehow run and prompt the user to enter the ID somehow... Can anyone help me? I AM trying here... : )

 

[charityg]

Do you think you can trap that particular error number? Then prompt for a different ID?
I have no idea what error number it is, but if worse comes to worse, you can prompt for input on any error.

 

[vexing]

Sounds like a good idea, however; I wouldn't BEGIN to know how to trap an error. I am VB inhibited...heheheh. Anyway, I just want to be able to have a client enter in a customer ID, have the form atuo generate a customer ID, and then check to make sure the ID does not already exist in the customer table. If it doesn't, fine, let them add it. If it DOES, I guess Access should pop-up what it considers a duplicate and give the client a chance to decide if it IS a duplicate. If it IS a duplicate, indeed, the client should be able to cancel the whole deal. If it IS NOT a duplicate, the customer should be able to add the new customer with a new auto generated id (maybe Access could just put a "1" on the end or something). See what I'm aiming for here?

 

[Liv Manto]

Let me make sure I got your problem correct.

For example you have an existing Customer Hallman and you entered Hallmark, they would share the same HALL, correct?

You could probably tag the second HALL as HALL-2

use recordset, assign your newly inputted table to a variable, then go through each record comparing values in field against your variable value.

So if you have existing

HALL
HALL-1
HALL-2

Get the maximum value of the number part,
val() function, add 1, add it to the variable HALL which would make it HALL-3.

Etc.Etc.

 

[vexing]

Sounds like a good idea, I'll spend today looking into recordsets...since I know nothing about them...