dialog box yes / no details (2 Viewers)

akika

Registered User.
Local time
Today, 07:54
Joined
Aug 7, 2018
Messages
102
hi
access 16, have several text box (ID, CIR, Name) in a form with a save btn..
if when input a CIR that already exist then below display added the code in before_update..

how to amend it that when click on yes it goes to next text box i.e name
when click on no.. it clear the text box CIR


Dim varCIR As Variant

varCIR = MsgBox("exist. Do you want to continue? ", vbYesNo, "Close Confirmation")
Select Case varCIR
Case vbYes
<<< allow cir entry and goes to next text box>>
Case vbNo
Cancel = True
<<< clear text box CIR>>
Exit Sub
End Select
 

Ranman256

Well-known member
Local time
Today, 10:54
Joined
Apr 9, 2015
Messages
4,339
if your going to use many boxes, just use a form with many boxes and 1 OK button.
eliminate all the code and all the msgboxes.
 

akika

Registered User.
Local time
Today, 07:54
Joined
Aug 7, 2018
Messages
102
actually the code will be pops up for duplicated CIR.
if CIR exists in the table then msg will be display that it exists and option to click on Yes it should allow the CIR entry and cursor go to next text

If Click NO, then it should clear text box

Is it possible to do that??
 

isladogs

MVP / VIP
Local time
Today, 14:54
Joined
Jan 14, 2017
Messages
18,186
Something like this perhaps (untested air code)

Code:
Private Sub CIR_BeforeUpdate (Cancel as integer)

Dim varCIR As String 'CHANGED

    varCIR = MsgBox("This CIR value already exists. Do you want to continue? ", vbYesNo, "Close Confirmation")
    Select Case varCIR
        Case vbYes
           Me.Dirty=False
           Me.othercontrolname.SetFocus
        Case vbNo
           Cancel = True
           Me.CIR="" 'or = Null if CIR is  number
           'Exit Sub 'NOT NEEDED as no code follows it
    End Select

End Sub
 
Last edited:

akika

Registered User.
Local time
Today, 07:54
Joined
Aug 7, 2018
Messages
102
i tried the code but getting error:

when click on yes
get run time error '2115'
debug pointing to Me.Dirty = False

when click No
runtime error '-2147352567(80020009)
debug point to Me.CIR = ""

The macro or function set to the beforeUpdate or validationRule property for this field is preventing from saving data in this fields

Any idea?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:54
Joined
May 7, 2009
Messages
19,175
Code:
Private Sub CIR_BeforeUpdate(Cancel As Integer)

    Dim lngCount As Long
    '* check if CIR already exists in the table "yourTable"
    lngCount = DCount("*", "yourTable", "CIR = """ & Me.CIR & """")
    If lngCount > 0 Then
        Cancel = MsgBox("This CIR value already exists. Do you want to continue? ", vbYesNo, "Close Confirmation") <> vbYes
        
    End If
        
End Sub
note if CIR is numeric, change to:

lngCount = DCount("*", "yourTable", "CIR = " & Me.CIR)
 

isladogs

MVP / VIP
Local time
Today, 14:54
Joined
Jan 14, 2017
Messages
18,186
Ok suggest you post a copy of your database for someone to look at.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:54
Joined
May 7, 2009
Messages
19,175
Code:
Private Sub CIR_BeforeUpdate(Cancel As Integer)

    Dim lngCount As Long
    '* check if CIR already exists in the table "yourTable"
    lngCount = DCount("*", "yourTable", "CIR = """ & Me.CIR & """")
    If lngCount > 0 Then
        Cancel = MsgBox("This CIR value already exists. Do you want to continue? ", vbYesNo, "Close Confirmation") <> vbYes
        
    End If
    If Cancel Then Me.CIR.Undo
End Sub
BeforeUpdate occurs when you try to leave the control, so it will automatically go to the next control.
 

akika

Registered User.
Local time
Today, 07:54
Joined
Aug 7, 2018
Messages
102
thanks arnelgp :)
perfect.. it works !
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:54
Joined
May 7, 2009
Messages
19,175
youre welcome! :)
 

Users who are viewing this thread

Top Bottom