On Click query

I am new to access and struggling with the query below. I am looking for this to check to make sure that the person completed all 4 boxes. After that, they can click a button to move them to the next record. Getting a Compile Error "Block IF without End If"

My "Test" Message was to see if I am getting though each step.


Private Sub Command239_Click()
If IsNull(Me.Customer2) Then
MsgBox ("Please Select Customer")
If IsNull(Me.Month_End2) Then
MsgBox ("Please Select Expiration Date")
If IsNull(Me.Conversation_Date) Then
MsgBox ("Please complete Meeting Date")
If IsNull(Me.Combo124) Then
MsgBox ("Please Select Renewal Option")
Me.Option = Null
Else:
MsgBox ("TEST")
End If
Me.Refresh
End Sub
Michael FranzCFOAsked:
Who is Participating?
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.

Gustav BrockCIOCommented:
Try with:

Private Sub Command239_Click()

    If IsNull(Me.Customer2) Then
        MsgBox ("Please Select Customer")
    ElseIf IsNull(Me.Month_End2) Then
        MsgBox ("Please Select Expiration Date")
    ElseIf IsNull(Me.Conversation_Date) Then
        MsgBox ("Please complete Meeting Date")
    ElseIf IsNull(Me.Combo124) Then
        MsgBox ("Please Select Renewal Option")
    Else
        MsgBox ("TEST")
        Me.Opion = "OK"
    End If
    Me.Refresh

End Sub 

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Hi,

With VBA, the If instruction must finish with an End, unless you write it on a single line:
If (condition) Then
    '// more instructions here
End If

If (condition) Then instruction

Open in new window


Notes concerning your code:
In case of multiple input error, your code will display many message boxes, wich is annoying for the user.
In my opinion, it is better to build a string, and display all errors at once.
Typically:
Private Sub Command239_Click()
    Dim inputError As String

    If IsNull(Me.Customer2) Then
        inputError = inputError & "Please, select a Customer." & vbcrlf
    End If
    If IsNull(Me.Month_End2) Then
        inputError = inputError & "Please Select Expiration Date." & vbcrlf
    End If
    If IsNull(Me.Conversation_Date) Then
        inputError = inputError & "Please complete Meeting Date." & vbcrlf
    End If
    If IsNull(Me.Combo124) Then
        inputError = inputError & "Please Select Renewal Option." & vbcrlf
    End If

    If (inputError <> vbNullString) Then
        MsgBox inputError, vbOkOnly + vbCritical
    Else
        MsgBox ("TEST")
    End If
End Sub

Open in new window

Also, within a form, the Me keyword is optional when refering to its controls.

Finally (and I can't stress it enough:
Choose meaningfull names for your variables, forms, controls ect …
As it stand, your control's name are not meaningfull. choosing a meaningfull names will greatly increase the meaning of your code for readers (us) and for you later when you'll need to modify your code (for whatever reasons).
0
Michael FranzCFOAuthor Commented:
That got me through the error.... How do I take that to the next step which would be clearing 10 items on the form...

Would I add the following

Private Sub Command239_Click()
If IsNull(Me.Customer2) Then
    MsgBox ("Please Select Customer")
ElseIf IsNull(Me.Month_End2) Then
    MsgBox ("Please Select Expiration Date")
ElseIf IsNull(Me.Conversation_Date) Then
    MsgBox ("Please complete Meeting Date")
ElseIf IsNull(Me.Combo124) Then
    MsgBox ("Please Select Renewal Option")
Else
     Me.field1 = ""
    Me.Field2 = ""
    Me.field3 = ""
    Me.field10 = ""
 
End If
Me.Refresh
End Sub
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Fabrice LambertFabrice LambertCommented:
Guess you were writing while I posted my answer, thus didn't see it.

By respect to SRP (Single Responsibility Principle), it is best to write a procedure that will clear controls, and call it in case of input mistakes:
Private Sub clearControls()
    field1 = ""
    Field2 = ""
    field3 = ""
    field10 = ""
End Sub

Private Sub Command239_Click()
    Dim inputError As String

    If IsNull(Me.Customer2) Then
        inputError = inputError & "Please, select a Customer." & vbcrlf
    End If
    If IsNull(Me.Month_End2) Then
        inputError = inputError & "Please Select Expiration Date." & vbcrlf
    End If
    If IsNull(Me.Conversation_Date) Then
        inputError = inputError & "Please complete Meeting Date." & vbcrlf
    End If
    If IsNull(Me.Combo124) Then
        inputError = inputError & "Please Select Renewal Option." & vbcrlf
    End If

    If (inputError <> vbNullString) Then
        MsgBox inputError, vbOkOnly + vbCritical
        clearControls
    Else
        MsgBox ("TEST")
    End If
End Sub

Open in new window

0

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
Gustav BrockCIOCommented:
You should set them to Null:

    Me.Field1 = Null
    Me.Field2 = Null
    Me.Field3 = Null
    Me.Field10 = Null

Open in new window

0
Michael FranzCFOAuthor Commented:
So I've adopted the code to the following... I have one issue left that I can see... If the person selects say 2 of the inputs (Customer_Box & Expiration_Box) and tries to submit they first are presented with the pop-up box to complete the remaining 2 items, but when they close that window the form clears. The form can not clear until all 4 items have been selected.



Private Sub Submit2_Click()
 Dim inputError As String

    If IsNull(Me.Customer_Box) Then
        inputError = inputError & "Please Select a Customer." & vbCrLf
    End If
    If IsNull(Me.Expiration_Box) Then
        inputError = inputError & "Please Select Expiration Date." & vbCrLf
    End If
    If IsNull(Me.Pre_Renewal_Meeting_Date) Then
        inputError = inputError & "Please Complete Meeting Date." & vbCrLf
    End If
    If IsNull(Me.Option_Box) Then
        inputError = inputError & "Please Select Renewal Option." & vbCrLf
    End If

    If (inputError <> vbNullString) Then
        MsgBox inputError, vbOKOnly + vbCritical
        ClearControls
    Else
        MsgBox ("Please Contact Your ID Admin")
    End If

End Sub



Including the following:


Private Sub ClearControls()
Customer_Box = ""
Expiration_Box = ""
CustNo = ""
Customer = ""
Exec = ""
Month_End1 = ""
Premium = ""
Pre_Renewal_Meeting_Date = ""
Option_Box = ""
Due_Date = ""
CSA = ""
Comments = ""
End Sub
0
Fabrice LambertFabrice LambertCommented:
Erm, this is confusing.

Under wich conditions the form should be cleared ?
0
Michael FranzCFOAuthor Commented:
Fabrice,

I apologize for any confusion I may have caused... I have inherited this and have modified it....and I struggle with too much vs not enough...

The data base does this.

  • Employee Table - All Sales EXEC

  • Renewal Table - Table has 12 fields (all fields are listed in the  Private Sub ClearControls)... The table is populated monthly (append). 8 of the 12 field contain data. 4 of the fields are NULL when loaded monthly

Renewal Form -

  • The employee logins in using a login form (only used to filter the records that belong to them) Something like cbouser=Exec...

  • The employee selects Customer_Box (requery) then selects Expiration_Box (requery). Based on those 2 selections it goes to the Renewal Table and finds the record that matched the 2 selections and populates the form. The employee is then responsible for completing Pre-Renewal Meeting Date, Option_Box, and adding Comments (not required). Only then can they hit the submit button and clear the form.

  • Why Only then? Because there is a Private Sub that completes a "Stamp_Date"... Records with a Stamp_date no longer can be queried in the selection stage above.

I have added the sub-queries... Not sure if they help or not.

Your assistance is greatly appreciated....
0
Fabrice LambertFabrice LambertCommented:
So, the form should be cleared when everything is Ok ?
If (inputError <> vbNullString) Then
        MsgBox inputError, vbOkOnly + vbCritical
    Else
        MsgBox ("TEST")
        clearControls
    End If

Open in new window

PS: Like Gustav said, the clearControls procedure should set control's value to Null instead of vbNullString.
Because a Null value is not an empty string.
0
John TsioumprisSoftware & Systems EngineerCommented:
While the above codes work their functionality is entirely code related so if you want to manipulate it in the future you have to revisit the code again and again....
With the use of related table this procedure can be separated from the code so that can easily handle all the future needs.
E.g. for the Controls that require that all the data have entered...
A table name RequiredFields
ControlID -->Number
ControlName -->Text

1   Customer2
2  Month_End2
..... etc

Then the code should be
Private Function FieldsFilledCorrectly() As Boolean
    Dim blnError As Boolean
    Dim rst As DAO.Recordset
    Dim ctl As Control
    blnError = True
    Set rst = CurrentDb.OpenRecordset("RequiredFields", dbOpenDynaset)
    For Each ctl In Me.Controls
        rst.FindFirst "[ControlName] = '" & ctl.Name & "'"
        If Not rst.NoMatch Then
            If Len(Nz(ctl, "")) = 0 Then
                MsgBox "Please Check...you haven't provided data for " & ctl.Name
                blnError = False
                Exit For
            End If
        End If
    Next
End Function

Open in new window

0
Michael FranzCFOAuthor Commented:
Thanks for the additional feedback.... My main problems is this is clearing the data in the table and not just the form.. I do not want the table to erase date, just clear the form


Private Sub ClearControls()
Customer_Box = ""
Expiration_Box = ""
CustNo = ""
Customer = ""
Exec = ""
Month_End1 = ""
Premium = ""
Pre_Renewal_Meeting_Date = ""
Option_Box = ""
Due_Date = ""
CSA = ""
Comments = ""
End Sub
0
John TsioumprisSoftware & Systems EngineerCommented:
In this case with a Table e.g. named FieldsToClear...with a similar structure to the one that checks fields
Private Sub FieldToClear() As Boolean

    Dim rst As DAO.Recordset
    Dim ctl As Control

    Set rst = CurrentDb.OpenRecordset("FieldsToClear", dbOpenDynaset)
    For Each ctl In Me.Controls
        rst.FindFirst "[ControlName] = '" & ctl.Name & "'"
        If Not rst.NoMatch Then                    
                ctl = Null
            End If
        End If
    Next
End Sub

Open in new window


In my previous code i missed the return of the iteration result so it should be just before End Function
FieldsFilledCorrectly = blnError 
End Function

Open in new window

0
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.