Message Boxes - vbYes vbNo

[Mary]

Hello-
I was hoping someone can help me. I don;t know how to
write the code for creating a message box that lets you
do have a selection of yes or no --
Essentially, I did a dcount in a macro to see if a
duplicate value existed and if it does it sends a message
that " this record exists"
However, I need to do more and I don;t know what to do

So on the event before update
check to see if value in txt box (non primary value and
can be duplicates) exists in the underlying table-
if it exists then the user can say yes I want to go to
that record or no I want to continue adding the record
even though one like it exists and move the focus back
and don't prompt me on a before update : )

please help
thanks in advance

 

[Howard Brody]

Would something like this help?

Private Sub txtCustomer_AfterUpdate()

' declare variables
Dim strCust as String
Dim i as Integer

strCust = [txtCustomer]

' check if there is already a customer with this name
i = DCount("[CustomerName]","tblCustomers","[CustomerName]
='" & strCust & "'")

' if not, end the sub here and continue adding it
If i = 0 Then
Exit Sub
End If

' if so, ask user if they want to move to record
If MsgBox("There is already a customer with this name in
the database. Would you like to view their
record?",vbYesNo,"Duplicate Customer?") = vbYes Then
' if yes, filter form to display customer
Dim strFilter As String
strFilter = "[CustomerName]='" & strCust & "'"
DoCmd.ApplyFilter , strFilter
Else
' if not, end the sub here
Exit Sub
End If

End Sub

Hope this helps!

Howard Brody

 

[Rick Brandt]

Hello-
I was hoping someone can help me. I don;t know how to
write the code for creating a message box that lets you
do have a selection of yes or no --
Essentially, I did a dcount in a macro to see if a
duplicate value existed and if it does it sends a message
that " this record exists"
However, I need to do more and I don;t know what to do

So on the event before update
check to see if value in txt box (non primary value and
can be duplicates) exists in the underlying table-
if it exists then the user can say yes I want to go to
that record or no I want to continue adding the record
even though one like it exists and move the focus back
and don't prompt me on a before update : )

Dim RetVal as Variant

RetVal = MsgBox("Message Text", vbYesNo,"Title Bar Text")

If RetVal = vbNo Then...

 

[Fredg]

Use "Cancel = True" to return to the control on the form.

In the Before Update event...

Dim intResponse as Integer
If DCount("*","YourTable","[SomeField] = '" & Me.[SomeField] & "'") > 0 Then
intResponse = MsgBox("This name already exits. Continue? Yes/No
?",vbExclamation + vbYesNo)
If intResponse = vbNo Then
Cancel = True ' go back to the control
Else
' Do something else here
End If
End If

 

[Mary]

This works really well-- thank you
Except for the part of vbNO
Being that the value is not unique I wanted to ask if
they did n0t want to view the other record- its a
situation where they may or may not want to
and if they don't want to view the previous record it
lets them go back to that text field (without being
prompted again ) and add a new record...
(if vbno then set focus to field and update -- something
like that)-- make sense?
thank you again.

-----Original Message-----
Would something like this help?

Private Sub txtCustomer_AfterUpdate()

' declare variables
Dim strCust as String
Dim i as Integer

strCust = [txtCustomer]

' check if there is already a customer with this name
i = DCount ("[CustomerName]","tblCustomers","[CustomerName]
='" & strCust & "'")

' if not, end the sub here and continue adding it
If i = 0 Then
Exit Sub
End If

' if so, ask user if they want to move to record
If MsgBox("There is already a customer with this name in
the database. Would you like to view their
record?",vbYesNo,"Duplicate Customer?") = vbYes Then
' if yes, filter form to display customer
Dim strFilter As String
strFilter = "[CustomerName]='" & strCust & "'"
DoCmd.ApplyFilter , strFilter
Else
' if not, end the sub here
Exit Sub
End If

End Sub

Hope this helps!

Howard Brody

-----Original Message-----
Hello-
I was hoping someone can help me. I don;t know how to
write the code for creating a message box that lets you
do have a selection of yes or no --
Essentially, I did a dcount in a macro to see if a
duplicate value existed and if it does it sends a message
that " this record exists"
However, I need to do more and I don;t know what to do

So on the event before update
check to see if value in txt box (non primary value and
can be duplicates) exists in the underlying table-
if it exists then the user can say yes I want to go to
that record or no I want to continue adding the record
even though one like it exists and move the focus back
and don't prompt me on a before update : )

please help
thanks in advance
.

.