Using If and Case statements

[Ben_Entrew]

Hi all,

I got a problem with the following statement.
Basically I want to check if the Reporting Month already exists in the Months Table. If it exists it should ask the User wheter to overwrite or not. If the User clicks on OK it should delete one record from Months Table if the User clicks no, nothing should happen.

However it doesn't delete the record, when I click on OK.
Can someone please help here?

Thanks in advance.
Regards,
Ben

Public Sub IMPORT_Click()
Dim filelocation As Variant
Dim f As Object
Dim Message As String, Title As String, Default As String
Dim sql As String
Dim MsgReply As Integer
Dim Monthsets As DAO.Recordset
'Ask User for Reporting month. Set prompt:
Message = "Enter a reporting month (mmyyyy)"

'Set title:
Title = "Reporting Month"

'Set default:
Default = " "

'Display dialog box at position 5000, 5000.
repmonth = InputBox(Message, Title, Default, 5000, 5000)


If DCount("Reporting_Month", "months", "Reporting_Month = '" & repmonth & "'") > 0 Then

MsgReply = MsgBox("This reporting month already exists. Do you want to overwrite the data?", vbYesNo)

Select Case MsgReply

    Case vbOK
    DoCmd.RunSQL "Delete FROM Months Where Months.Reporting_Month = '012014'"

    Case vbNo

End Select

Else
MsgBox ("Yes it's indeed new")
Set Monthsets = CurrentDb.OpenRecordset("Months")
Monthsets.AddNew
Monthsets("Reporting_Month").Value = repmonth
Monthsets.Update
  
End If

End Sub

 

[mrojas]

Your code below the If DCount statement should look something like this:

If Msgbox("This reporting month already exists. Do you want to overwrite the data?", vbYesNo) = vbYes Then
  DoCmd.RunSQL "Delete FROM Months Where Months.Reporting_Month =  '" & repmonth & "'"
Else

followed by the rest of you code below the Else statement