Custom Messagebox

[Guest]

Hello,

I have a form where an 8 character number needs to be typed into a single
box manually. There are occasions where the same 8 digit number could be
required again so I don't want to put the ' do not accept duplicates' option
on the table design. However, I would like to write a piece of code that
creates a message box telling the user they have typed in a duplicate number
and if that duplicate number is valid then to state why in the comments
section of the form.

Anybody have any idea where to start?

Thanks

 

[Pieter Wijnen]

Sure

Private Sub MyText_BeforeUpdate(Cancel As Integer)
Dim RsC As DAO.RecordSet
Dim Dup As Boolean, DoClose As Boolean

If Me.FilterOn = False Then
Set RsC = Me.RecordsetClone
Else
Set RsC = CurrentDb.OpenRecordset(Me.RecordSource,DAO.dbOpenSnapshot)
DoClose = True
End If
If Me.NewRecord Then
RsC.FindFirst "MyText = '" & Me.MyText.Value & "'"
Else
RsC.FindFirst "ID <> " & Me.ID.Value & " AND MyText = '" &
Me.MyText.Value & "'"
End If
Dup = Not RsC.EOF
If DoClose Then
RsC.Close
End If
Set RsC = Nothing
If Dup Then
Cancel = MsgBox("Duplicate Number Ok?", vbOkCancel Or vbQuestion) =
vbCancel
End If
End Sub

 

[Guest]

In the BeforeUpdate event procedure of the control bound to the field in
question put code along these lines:

' play about with the spaces in the following constant declaration
' to get the spacing of the message as you want it.
Const conMESSAGE = " The number you have entered " & _
"duplicates one in another record." & vbNewLine & _
"If this is a valid duplication click OK " & _
"and give the reason in the comments box." & _
vbNewLine & vbNewLine & " " & _
" Confirm duplication."

Dim strCriteria As String

strCriteria = "YourField = " & Me.YourField

If Not IsNull(DLookup("YourField", "YourTable", strCriteria)) Then
If MsgBox(conMESSAGE, vbOKCancel, "Duplicated Number") = vbCancel Then
Cancel = True
End If
End If

The above assumes that the field is a number data type. If it’s a text data
type wrap the value in quotes when building the criterion:

strCriteria = "YourField = """ & Me.YourField & """"

Bear in mind that in a multi-user environment two or more users could be
entering the same previously unused number simultaneously, in which case the
above would not detect the duplication if each user has not yet saved the
data to the table. Using the form's BeforeUpdate event procedure rather than
the control's would cater better for such a scenario, but would not be
completely bullet-proof. To be confident that such a duplication has been
detected a more elaborate method would be necessary, such as inserting each
number immediately into a separate table in which the column is uniquely
indexed and handling the index violation error which would arise if the
number already existed in the table. Even then a user could change their
mind after another user had been alerted to a duplication and change the
number back to a previously unused one, thus rendering the alert unnecessary
and any comment superfluous.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

Hello,

I have a form where an 8 character number needs to be typed into a single
box manually. There are occasions where the same 8 digit number could be
required again so I don't want to put the ' do not accept duplicates' option
on the table design. However, I would like to write a piece of code that
creates a message box telling the user they have typed in a duplicate number
and if that duplicate number is valid then to state why in the comments
section of the form.

Anybody have any idea where to start?

Thanks

Use the BeforeUpdate event of the textbox for the code. Something like:

Private Sub txtMyField_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not IsNull(DLookUp("[fieldname]", "[tablename]", "[fieldname] = '" _
& Me!txtMyField & "'") Then
iAns = MsgBox("Duplicate code. Click Yes to use it anyway, No to reenter," _
& " Cancel to start this record over.", vbYesNoCancel
Select Case iAns
Case vbYes
MsgBox "Please enter a reason"
Me.txtComments.SetFocus
Case vbNo
Cancel = True
Me.txtMyField.Undo
Case vbCancel
Me.Undo ' erase the whole form
End Select
End If
End Sub

John W. Vinson [MVP]

 

[Guest]

Thanks alot everybody most useful