confirm insert on _Click()

[chelm]

I'm new to access and VBA but I've managed to get some functions up and running for the most part. I need a bit of help/advice as to how to create a confirm insert after the button is clicked.

Private Sub cmdAddCitation_Click()
Dim rsCT As Recordset
Dim db As Database
Dim newCID As Long
Dim tmpCID As Long

If Not IsNull(Me.cboTitle) And Not IsNull(Me.cboType) Then
    tmpCID = CIDFound                                   'Checks if citation entered is existing or not
    If tmpCID <> -999 Then
            AddCIDtoDS tmpCID, Me.Parent.DissertationID 'Citation is existing so it will be added to the currently selected Dissertation
            Me.Requery                                  'Join table (tblDisserationToCitationJoin) will be populated by CitationID and DIssertationID
    
    Else
        Set db = CurrentDb                              'Citation is not yet existing so it will be added to the Citations table and subsequently tblDisserationToCitationJoin will be populated
        Set rsCT = db.OpenRecordset("tblCitations")
        rsCT.AddNew
            rsCT!CitationTitle = Me.cboTitle
            rsCT!CitationYear = Me.cboYear
            rsCT!CitationAuthorLastName = Me.cboLastname
            rsCT!CitationType = Me.cboType
            newCID = rsCT!CitationID
        rsCT.Update
        AddCIDtoDS newCID, Me.Parent.DissertationID
        Set db = Nothing
        rsCT.Close
        Me.Requery
        
    End If
Else
    MsgBox "Please enter Citation Title and Year.", vbInformation, "No Citation Title or Year."
End If

End Sub

Any advice is greatly appreciated.

 

[gemma-the-husky]

not sure what you want but do you mean after each me.requery

you want

call msgbox("Record Added")


if you are not familiar with msgbox look at help, cause it has lots of bells and whistles

 

[chelm]

Sorry for the poor explanation, right now when I push the add button the record is added without any message. What I would like to happen is when the button is pushed a box pops up that says "do you want to add the record", then click yes or no. If yes it adds the record and pops up a confirm message if no it pops up a message saying it wasn't added.

The call message box would work for the last part of it, but I don't think it works for the first part.

I'll look it up and see if it will do what I need it to for the first part.

Edit: It seems like I can use a message box to do what I wanted. Thanks for the tip, I didn't think it was anything more than a way to pop up a message.

 

[Moniker]

Something tells me you're using wizards. They make code, and you don't have a lot of control over what they make. Use the wizard to learn, not to program. The code that is automatically produced is usually full of superfluous crap (save a Word Document as an HTML file for example). You need to understand what the code does, not what to remove.

 

[chelm]

Actually I'm not using a wizard at all, I'm simply finding examples online and then writing my own code. I'm actually a Senior in a BS program for Computer Science, but all I know well is c++, mysql and php. I can't seem to find a good resource for VBA and as I said I'm new to Access.

 

[gemma-the-husky]

ive added bits in red.

you may want to add the record via the recordset.

you could also construct a sql statement

"insert into etc etc"

or you could make your form bound to the underlying data, and then it does it automatically.

Generally with Access, you the programmer has less to do if you use bound forms, and stored queries where possible, although sometimes this can't give you the interface you need

another thought - you are calling a sub/function
AddCIDtoDS

so you could put a msgbox in that sub, if you prefer.
you could even pass anoother parameter into the sub to indicate whether you want a message or not.

Private Sub cmdAddCitation_Click()
Dim rsCT As Recordset
Dim db As Database
Dim newCID As Long
Dim tmpCID As Long

If Not IsNull(Me.cboTitle) And Not IsNull(Me.cboType) Then
    tmpCID = CIDFound                                   'Checks if citation entered is existing or not
    If tmpCID <> -999 Then
            AddCIDtoDS tmpCID, Me.Parent.DissertationID 'Citation is existing so it will be added to the currently selected Dissertation
            Me.Requery                                  'Join table 
[COLOR="Red"]        msgbox("Record Added")[/COLOR]    
            End If
(tblDisserationToCitationJoin) will be populated by CitationID and DIssertationID
    
    Else
        Set db = CurrentDb                              'Citation is not yet existing so it will be added to the Citations table and subsequently tblDisserationToCitationJoin will be populated
        Set rsCT = db.OpenRecordset("tblCitations")
        rsCT.AddNew
            rsCT!CitationTitle = Me.cboTitle
            rsCT!CitationYear = Me.cboYear
            rsCT!CitationAuthorLastName = Me.cboLastname
            rsCT!CitationType = Me.cboType
            newCID = rsCT!CitationID
        rsCT.Update
        AddCIDtoDS newCID, Me.Parent.DissertationID
        Set db = Nothing
        rsCT.Close
        Me.Requery
[COLOR="Red"]        msgbox("Record Added")[/COLOR]    
        End If
Else
    MsgBox "Please enter Citation Title and Year.", vbInformation, "No Citation Title or Year."
End If

End Sub

 

[chelm]

Thanks a lot for the tip, I looked up the msgbox last night and it worked perfectly.

Thanks again.

 

[gemma-the-husky]

wrt msgboxes, i'm sure you realised you can ask a msgbox a question

if msgbox("my notes/question goes here",vbyesno)

will return either vbyes, or vbno depending on which repsonse the user selected. Loads of other options also