Behaviour on close button (1 Viewer)

[Chumpalot]

Hi all,

I have a bound form, the contents of which are simple. 1 bound field where a year may (or may not) be entered.

I have Data Validation on this field (yyyy).

I have the form working to a degree. Here's what I have:

Private Sub cmd_closeform_Click()
If IsNull(Me.txtvintage) Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
Else
        MsgBox "The Vintage was saved", , "Record Saved"
        DoCmd.Close
End If
End Sub

This works well but I would like to add some more functionality.

- There is no option for a user to close the form after realising that the value they've entered already exists.
- If a value is added into txtvintage where it already exists in tbl_vintage, make txt_error visible.
- If a value is typed into txtvintage that violates the Data Validation, suppress Access' error, display a custom error and set the focus back onto txt_vintage.

Thanks for the help.

Dave

 

[TJPoorman]

The first two of your questions can be accomplished by performing a DLookup() on your table. See Here.

The third question, you can provide your own error message for validation using the "Validation Text" property.

 

[Chumpalot]

Thank you for taking the time to reply.

I have this at the moment and unfortunately it fails to work.

Private Sub cmd_closeform_Click()
Dim Answer As Variant
    Answer = DLookup("[Vintage]", "tbl_vintage", "[Vintage] = '" & Me.txtvintage & "'")
        
If IsNull(Me.txtvintage) Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
End If
If Not IsNull(Answer) Then
            Me.txterror.Visible = True
            Cancel = True
            Me.txtvintage.Undo
Else
        MsgBox "The Vintage was saved", , "Record Saved"
        DoCmd.Close
End If
End Sub

I'm guessing at how to nest IFs together I'm afraid as I've never tried them before.

Regards

 

[TJPoorman]

What datatype is Vintage in tbl_vintage?

 

[Chumpalot]

What datatype is Vintage in tbl_vintage?

Short text. The entries work and are appended to the table if they're new. I just need to somehow get all the other elements working at the same time.

Cheers

 

[TJPoorman]

Try this:

Private Sub cmd_closeform_Click()
If Nz(Me.txtvintage, "") = "" Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
ElseIf Not Nz(DLookup("[Vintage]", "tbl_vintage", "[Vintage] = '" & Me.txtvintage & "'"), "") <> "" Then
            Me.txterror.Visible = True
            Me.txtvintage.Undo
Else
        MsgBox "The Vintage was saved", , "Record Saved"
        DoCmd.Close
End If
End Sub

 

[Chumpalot]

Try this:

Private Sub cmd_closeform_Click()
If Nz(Me.txtvintage, "") = "" Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
ElseIf Not Nz(DLookup("[Vintage]", "tbl_vintage", "[Vintage] = '" & Me.txtvintage & "'"), "") <> "" Then
            Me.txterror.Visible = True
            Me.txtvintage.Undo
Else
        MsgBox "The Vintage was saved", , "Record Saved"
        DoCmd.Close
End If
End Sub

Thank you for this suggestion. Unfortunately when I enter a unique value into the txt field it isn't added to the table. The "This Vintage already exists!" is shown no matter what I put in.

Thanks again

 

[TJPoorman]

Sorry try removing the "Not" in RED

Private Sub cmd_closeform_Click()
If Nz(Me.txtvintage, "") = "" Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
ElseIf [COLOR="Red"]Not[/COLOR] Nz(DLookup("[Vintage]", "tbl_vintage", "[Vintage] = '" & Me.txtvintage & "'"), "") <> "" Then
            Me.txterror.Visible = True
            Me.txtvintage.Undo
Else
        MsgBox "The Vintage was saved", , "Record Saved"
        DoCmd.Close
End If
End Sub

 

[Chumpalot]

Nothing happens when I click the 'Save & Close' button I'm afraid.

Thank you for your tenacity though

Dave

 

[Grumm]

Is the txtvintage linked to the field Vintage ? Or is it just a plain textbox that is not bound to the table field ?
If the second one is your case, you will have to manually save the record before closing the form.

 

[Chumpalot]

Is the txtvintage linked to the field Vintage ? Or is it just a plain textbox that is not bound to the table field ?
If the second one is your case, you will have to manually save the record before closing the form.

Hi Grumm, thanks for your reply.

txtvintage is linked to the field 'Vintage' in tbl_vintage.

I think the code is close to what I need. I'm just failing to get my head around the IF statements.

So when the form loads...
- IF nothing is entered into the field = Msgbox "Please enter a Vintage"
- IF the vintage year already exists = make visible a label ("Vintage already exists") and put the focus back into txtvintage
- IF the year doesn't exist in the table = Msgbox "Vintage is entered" and closes form.

Due to my lack of VBA skills, I have no idea how this would be written. I can (with the help of the posts above) get parts of this to work, but not all.

:banghead:

 

[Grumm]

Can you try this instead of just closing the form :

DoCmd.RunCommand acCmdSaveRecord 
 DoCmd.Close

This should trigger the save record before closing.
Do you have any other code somewhere else ? (AfterUpdate or other behind the txt ?)

 

[Chumpalot]

Can you try this instead of just closing the form :

DoCmd.RunCommand acCmdSaveRecord 
 DoCmd.Close

This should trigger the save record before closing.
Do you have any other code somewhere else ? (AfterUpdate or other behind the txt ?)

No other code anywhere.

Error 3022 appears when there is a duplicate record. The form closes when a unique entry is added.

Can I capture this error? Replace with my own Msgbox error and go from there?

 

[Chumpalot]

The button I use to open the form looks like this:

Private Sub cmd_addvintage_Click()
DoCmd.OpenForm "frm_addvintage"
DoCmd.GoToRecord , , acNewRec
End Sub

 

[Chumpalot]

This is what I have so far...

Private Sub cmd_closeform_Click()
If IsNull(Me.txtvintage) Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
Else
DoCmd.RunCommand acCmdSaveRecord
    MsgBox "The Vintage was saved", , "Record Saved"
    DoCmd.Close
End If
End Sub

2 out of 3 things work fine.

The third thing: capturing the error (3022) and making the txt_label visible. Where would this go?

 

[Grumm]

Can you check this demo ? And see if it is what you want ?
(You need to add a new record. When you start the form, you see the actual first record of the table tbl_vintage)

Can you post a small sample of what you have now so that we can check ?

 

[Chumpalot]

Can you check this demo ? And see if it is what you want ?
(You need to add a new record. When you start the form, you see the actual first record of the table tbl_vintage)

Can you post a small sample of what you have now so that we can check ?

You are a star. Thank you very much. I've extracted the VBA and it works flawlessly.

Onto the next issue

Thanks again.
Dave

 

[Grumm]

The vba code wasn't more than a copy past of this answer...
Please thank him too

Sorry try removing the "Not" in RED

Private Sub cmd_closeform_Click()
If Nz(Me.txtvintage, "") = "" Then
        MsgBox "Please enter a Vintage", , "Enter a Vintage"
        Me.txtvintage.SetFocus
ElseIf [COLOR="Red"]Not[/COLOR] Nz(DLookup("[Vintage]", "tbl_vintage", "[Vintage] = '" & Me.txtvintage & "'"), "") <> "" Then
            Me.txterror.Visible = True
            Me.txtvintage.Undo
Else
        MsgBox "The Vintage was saved", , "Record Saved"
        DoCmd.Close
End If
End Sub

 

[Chumpalot]

I swear I tried that code but couldn't get it to work.

Thank you both so much for your help.