Need a msgbox to display when a record is not found

[Jake1776]

Hi I need help displaying a msgbox when office and/or fiscal year do no match the input and therefore cannot be found. Right now it is confirming that the budget change has occurred even if it did not.

I'm new to programming and suspect that an If-Then statement might not be the best way to search; it seems to work, however I am open to doing away with that concept if there is a better way.

Private Sub cmdSetBudget_Click()
 
    Dim dbJob As DAO.Database
    Dim rsBudget As DAO.Recordset
 
    Set dbJob = CurrentDb
    Set rsBudget = dbJob.OpenRecordset("tblBudget")
 
'   txtSetBudget is a box on frmBudgetControl
    Dim strSetBudget As Currency
    strSetBudget = txtSetBudget
 
'   txtFiscalYear is a box on frmBudgetControl
    Dim intFiscalYear As Integer
    intFiscalYear = txtFiscalYear
 
'   txtOffice is a box on frmBudgetControl
    Dim strOffice As String
    strOffice = txtOffice
 
'   variable to check if fiscal year is four digits
    Dim strCharLength As String
    strCharLength = Len(txtFiscalYear)
 
'   MsgBox to confirm your edit
    Dim intResponse As Integer
 
'   Checks to make sure the fiscal year is four digits.
    If strCharLength = 4 Then
 
        intResponse = msgbox("Clicking yes will change the budget " & _
        "of office number " & strOffice & " to $" & strSetBudget & vbLf & _
        "This action cannot be undone!", vbYesNo)
 
        If intResponse = 6 Then
            With rsBudget
                Do Until .EOF
'               compares the office ID to the one in the box on the form
                If !Office_ID = strOffice And !FY = intFiscalYear Then
                    .Edit
'                    Sets the budget to the value in the box on the form
                    !Budget = strSetBudget
'                    This code adds to the budget
'                    !Budget = (!Budget + txtSetBudget)
                    .Update
                End If
                .MoveNext
                Loop
            End With
            msgbox "The budget for office number " & strOffice & _
            vbLf & "has been changed to: $" & strSetBudget
            Else
                msgbox "Budget not changed", 48
                Exit Sub
        End If
    Else
        msgbox "You must enter a four digit fiscal year."
        Exit Sub
    End If
 
End Sub
 

Sorry if the code didn't line up, I've never posted before.

[ADezii]

A few changes should do the trick:

Dim dbJob As DAO.Database
Dim rsBudget As DAO.Recordset
Dim curSetBudget As Currency
Dim intFiscalYear As Integer
Dim strOffice As String
Dim intResponse As Integer
 
Set dbJob = CurrentDb
Set rsBudget = dbJob.OpenRecordset("tblBudget")
 
'All Fields must contain Data
If IsNull(Me![txtSetBudget]) Or IsNull(Me![txtFiscalYear]) Or IsNull(Me![txtOffice]) Then
  MsgBox "One or more Fields are missing Critical Data", vbExclamation, "Data Missing"
    Exit Sub
End If
 
'txtSetBudget is a box on frmBudgetControl
curSetBudget = txtSetBudget
 
'txtFiscalYear is a box on frmBudgetControl
intFiscalYear = CInt(txtFiscalYear)
 
'txtOffice is a box on frmBudgetControl
strOffice = txtOffice
 
'Checks to make sure the fiscal year is four digits. Is the Year Numeric and consist
'of 4 digits?
If IsNumeric(Me![txtFiscalYear]) And Len(CStr(Me![txtFiscalYear])) = 4 Then
  'The combination of [Office_ID] and [fy] must exist in tblBudget
     'DCount("*", "tblBudget", "[Office_ID]='" & "Office A"  & "' And [fy] = " & 2010)"
  If DCount("*", "tblBudget", "[Office_ID]='" & strOffice & "' And [fy] = " & intFiscalYear) > 0 Then
    intResponse = MsgBox("Clicking yes will change the budget " & _
                         "of office number " & strOffice & " to $" & curSetBudget & vbLf & _
                         "This action cannot be undone!", vbYesNo)
      If intResponse = vbYes Then
        With rsBudget
          Do Until .EOF
            'compares the office ID to the one in the box on the form
            If !Office_ID = strOffice And !fy = intFiscalYear Then
              .Edit
                'Sets the budget to the value in the box on the form
                !Budget = curSetBudget
              .Update
            End If
              .MoveNext
          Loop
        End With
 
        MsgBox "The budget for office number " & strOffice & _
                vbLf & "has been changed to: $" & curSetBudget
      Else
        MsgBox "Budget not changed", 48
          Exit Sub
      End If
  End If
Else
  MsgBox "You must enter a four digit fiscal year."
    Exit Sub
End If
 
rsBudget.Close
Set rsBudget = Nothing

[Jake1776]

This looks great! Thank you so much. Funny how obvious the answer is, but I appreciate that you also made changes to show me what good code looks like. I have no idea what DCount is, even though I can see what it does. I will have to look that one up.

Thanks again!
Jacob

[Jake1776]

So it looks like the only problem I had with your code was that the validation for the Fiscal Year text box broke when I entered letters. It is the "data type mismatch" error.

I was getting that error before with the DCount section so I changed FY to number from short text.

If I understand correctly CInt is used to convert a string to an integer, is that correct? If so why does it complain about something that is already an integer?
I am also unsure about the DCount error. I think those single quotes are making sure strOffice is looked at as text when it tries to find it in the table?

Sorry for all the questions. I really appreciate the help. I wrote all the code I posted, but I'm still learning why it works and how to use proper syntax.

[ADezii]

CInt is used to convert a string to an integer, is that correct?

Yes, but you won't need it after the Conversion of Fiscal Year from TEXT to INTEGER.

I think those single quotes are making sure strOffice is looked at as text when it tries to find it in the table?

Correct

1. Make sure the Data Type of your Fiscal Year Field ([fy]) is set to INTEGER.
2. Change
   

   intFiscalYear = CInt(txtFiscalYear)

3. to
   

   intFiscalYear = txtFiscalYear

4. Let me know what happens.

[Jake1776]

That still gave me the "data type mismatch" error.

I got it to work when I changed

Dim intFiscalYear As Integer

to

Dim strFiscalYear As String

and left

strFiscalYear = CStr(txtFiscalYear)

I would like to know why that works though... it seems like the code can't convert an integer because it's already an integer? That doesn't really make sense to me.

I'm curious why strFiscalYear doesn't need the single quotes?

If DCount("*", "tblBudget", "[Office_ID]='" & strOffice & "' and [FY] = " & strFiscalYear) > 0 Then

[ADezii]

Are your Fiscal Years truly INTEGERS and Values that cannot be represented as INTEGERS, such as: FY2015, FY2016, etc.

[Jake1776]

They are just integers. I figured it would be easier that way.
I'm still curious as to why, but the code works now. I really appreciate your help. I found several of your posts on this site that were also very helpful for other things I'm doing. Thanks!

[ADezii]

@Jake1776:
Glad that you got your Code working and I was happy to assist. Good Luck with your Project.