If Else without repeating code

I have some VBA code that checks if a control is blank and if so asks the user if it is intentional, it adds a notations to a text box and saves the data, if no then it returns the user to the control. That all works fine, however if the control is Not blank the data is not saved, nothing happens, I believe that is due to my If statement that checks for the empty control does nit know what to do if the condition is not true. I think i could fix it by adding an Else but wouldn't I have to copy all that code that inserts the data or is there an easier way to handle the 2 conditions?
 
Private Sub btnNext_Click()
'Single Item Save Button
Dim strSql As String
Dim MsgAns As Integer
On Error GoTo Error_Procedure

bSaveClicked = True

If Len(Me.cboSubcat & "") = 0 Then
MsgAns = MsgBox("You Did Not Select a Subcategory for " & Me.ITEMNMBR & vbCrLf & " Click YES if this was intentional and" & vbCrLf & "Text will be added to the Notes field Automaticaly and Saved.", vbYesNo + vbExclamation + vbDefaultButton2, "Blank Subcategory")
If MsgAns = vbYes Then
Me.cboAddSub = "Yes"
Me.txtComm = "The Subcategory has been left blank for this Item Number"
'Checks item_cat_subcat table aginst the form Me.txtItem control for a record if none it inserts it
 If DCount("ITEMNMBR", "item_cat_subcat", "(ITEMNMBR = '" & Me.txtItem & "')") = 0 Then
       strSql = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,Comments,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Nz(Me.cboSubcat, 0) & "','" & Me.txtComm & "','" & Me.cboAddSub & "');"
       CurrentDb.Execute strSql, dbFailOnError
   'End If

  strSql = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Nz(Me.cboAttrb, 0) & "','" & Nz(Me.cboAttribvl, 0) & "','" & Nz(Me.cboAddAttrib, 0) & "','" & Nz(Me.cboAddAttribvl, 0) & "');"
  CurrentDb.Execute strSql, dbFailOnError

  DoCmd.GoToRecord , "", acNext

  Me.cboSubcat = Null
  Me.cboAttrb = Null
  Me.cboAttribvl = Null
Me.cboAddSub = Null
Me.cboAddAttrib = Null
Me.cboAddAttribvl = Null
Me.txtComm = ""
Me.cboAddSub = ""
End If
ElseIf MsgAns = vbNo Then
Cancel = True
Me.cboSubcat.SetFocus
End If
End If

Exit_Procedure:
  
  Exit Sub

Error_Procedure:
   MsgBox Err.Number & " - " & Err.Description
   Resume Exit_Procedure

End Sub

Open in new window

skull52IT director Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
If you want the code to save the record to run regardless of the button clicked it should be outside the if else block.
Dale FyeOwner, Developing Solutions LLCCommented:
Why are you using an unbound form?  This is so much easier with a bound form.

Dale
skull52IT director Author Commented:
Dale, I know but it was not an option I could do given the overall function of the application.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

skull52IT director Author Commented:
Marco, Not sure what you mean. the IfElse fires if they answer no and give them the option to go back and add the Subcategory. but it wont save even if the Subcategory is not empty.
MacroShadowCommented:
This is the code you use to save the record:
strSql = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Nz(Me.cboAttrb, 0) & "','" & Nz(Me.cboAttribvl, 0) & "','" & Nz(Me.cboAddAttrib, 0) & "','" & Nz(Me.cboAddAttribvl, 0) & "');"
  CurrentDb.Execute strSql, dbFailOnError

Open in new window

correct?

Move those lines to after the else if:
....
strSql = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Nz(Me.cboAttrb, 0) & "','" & Nz(Me.cboAttribvl, 0) & "','" & Nz(Me.cboAddAttrib, 0) & "','" & Nz(Me.cboAddAttribvl, 0) & "');"
  CurrentDb.Execute strSql, dbFailOnError
ElseIf MsgAns = vbNo Then
Cancel = True
Me.cboSubcat.SetFocus
End If
....

Open in new window

PatHartmanCommented:
it was not an option I could do given the overall function of the application.
I hate to argue but there are very few valid reasons for not using bound forms.

1. Cancel = True isn't available in this event.  If you are not getting a compile error, you do not have Option Explicit in the module header.  Best practice is to ALWAYS define your variables.  That way you get compile errors which are far less embarrassing and dangerous than run time errors.
2. The code would be much easier to read if it were properly aligned.
3. Macro told you how to fix the immediate problem.
skull52IT director Author Commented:
Pat, ok, I Lined up my Code, added Option Explicit in the module header but now when I compile the code I get a compile error " Variable not defined" on all my Cancel = True I assume that is what you were referring to?
skull52IT director Author Commented:
Also the reason I used unbound controls is that I used cascade filtering on on them. and needed to insert values into 2 tables simultaneously, and thought that was the best way to do it. I am not by any any stretch of the imagination an expert on this, i am more of a self taught hack, which is why I rely on you guys for assistance and you have always come through.
skull52IT director Author Commented:
Ok, Marco if I moved the insert statements after the ElseIf  then if  if the user selects  no it saves a blank value anyway and it still wont save if the Condition is False i.e the Subcategory is not blank.
skull52IT director Author Commented:
I want the save to run if the condition is either true or false.  If it is true the it saves the Item number and some texted data, if it is false then it  saves all the data in the controls, it is not doing that part.
skull52IT director Author Commented:
Here is the code reposted, cleaned up and notated. all is working well except that it will not save if cboSubcat is Not empty

Private Sub btnNext_Click()
'Single Item Save Button
Dim strSql As String
Dim MsgAns As Integer
On Error GoTo Error_Procedure
'Confirms The saved Button was pressed
bSaveClicked = True
'Checks if the Subcategory Control is blank
If Len(Me.cboSubcat & "") = 0 Then
MsgAns = MsgBox("You Did Not Select a Subcategory for " & Me.ITEMNMBR & vbCrLf & " Click YES if this was intentional and" & vbCrLf & "Text will be added to the Notes field Automaticaly and Saved.", vbYesNo + vbExclamation + vbDefaultButton2, "Blank Subcategory")
If MsgAns = vbYes Then
Me.cboAddSub = "Yes"
Me.txtComm = "The Subcategory has been left blank for this Item Number"
'Checks item_cat_subcat table aginst the form Me.txtItem control for a record if none it inserts it
If DCount("ITEMNMBR", "item_cat_subcat", "(ITEMNMBR = '" & Me.txtItem & "')") = 0 Then
strSql = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,Comments,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Nz(Me.cboSubcat, 0) & "','" & Me.txtComm & "','" & Me.cboAddSub & "');"
CurrentDb.Execute strSql, dbFailOnError
'inserts records into the Attribute table
strSql = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Nz(Me.cboAttrb, 0) & "','" & Nz(Me.cboAttribvl, 0) & "','" & Nz(Me.cboAddAttrib, 0) & "','" & Nz(Me.cboAddAttribvl, 0) & "');"
CurrentDb.Execute strSql, dbFailOnError
'Moves to next record
DoCmd.GoToRecord , "", acNext
'Clears controls
Me.cboSubcat = Null
Me.cboAttrb = Null
Me.cboAttribvl = Null
Me.cboAddSub = Null
Me.cboAddAttrib = Null
Me.cboAddAttribvl = Null
Me.txtComm = ""
Me.cboAddSub = ""
   End If
ElseIf MsgAns = vbNo Then
'Cancel = True
Me.cboSubcat.SetFocus
    End If
End If

Exit_Procedure:
  
  Exit Sub

Error_Procedure:
   MsgBox Err.Number & " - " & Err.Description
   Resume Exit_Procedure

End Sub

Open in new window

PatHartmanCommented:
As Macro already mentioned, the save was in the wrong place.  When you have conditional code like this, you either have to call subroutines to do the save or exit from the main procedure as soon as you determine that you do not want to exit.

Private Sub btnNext_Click()
'Single Item Save Button
Dim strSql As String
Dim MsgAns As Integer
On Error GoTo Error_Procedure
'Confirms The saved Button was pressed
    bSaveClicked = True
'Checks if the Subcategory Control is blank
    If Len(Me.cboSubcat & "") = 0 Then
        MsgAns = MsgBox("You Did Not Select a Subcategory for " & Me.ITEMNMBR & vbCrLf & " Click YES if this was intentional and" & vbCrLf & "Text will be added to the Notes field Automaticaly and Saved.", vbYesNo + vbExclamation + vbDefaultButton2, "Blank Subcategory")
        If MsgAns = vbYes Then
            Me.cboAddSub = "Yes"
            Me.txtComm = "The Subcategory has been left blank for this Item Number"
            'Checks item_cat_subcat table aginst the form Me.txtItem control for a record if none it inserts it
            If DCount("ITEMNMBR", "item_cat_subcat", "(ITEMNMBR = '" & Me.txtItem & "')") = 0 Then
                'continue
            Else
                Exit Sub
            End If
        Else
            Me.cboSubcat.SetFocus
            Exit Sub
        End If
    End If
    
'save record
    strSql = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,Comments,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Nz(Me.cboSubcat, 0) & "','" & Me.txtComm & "','" & Me.cboAddSub & "');"
    CurrentDb.Execute strSql, dbFailOnError
    'inserts records into the Attribute table
    strSql = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Nz(Me.cboAttrb, 0) & "','" & Nz(Me.cboAttribvl, 0) & "','" & Nz(Me.cboAddAttrib, 0) & "','" & Nz(Me.cboAddAttribvl, 0) & "');"
    CurrentDb.Execute strSql, dbFailOnError
    'Moves to next record
    DoCmd.GoToRecord , "", acNext
    'Clears controls
    Me.cboSubcat = Null
    Me.cboAttrb = Null
    Me.cboAttribvl = Null
    Me.cboAddSub = Null
    Me.cboAddAttrib = Null
    Me.cboAddAttribvl = Null
    Me.txtComm = ""
    Me.cboAddSub = ""
Exit_Procedure:
  
  Exit Sub

Error_Procedure:
   MsgBox Err.Number & " - " & Err.Description
   Resume Exit_Procedure

End Sub

Open in new window


PS - this is what properly indented code looks like.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
More than 10 comments!, upload a sample demonstrating the issue.
skull52IT director Author Commented:
Thanks once again for all the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.