Else If.....claen programming

D

datacontrol

Registered User.
Local time
Today, 14:59
Joined
Jul 16, 2003
Messages
142
Hello all....I have come a long way in learning VBA with the help of this forum. As seen below, I have a large amount of else if's. 45 to be exact. Is there any way to clean this up? It works great, but I am just wondering if it could be simplified.

How would I store the sql command INSERT INTO so that it could be referenced bay a variable name?




ElseIf (Me!task) = 1 Then
DoCmd.RunSQL "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, dst_time, dst_task_id) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "')"
DoCmd.Close
MsgBox "Your CYCLE END data has been saved."
Exit Sub

ElseIf (Me!task) = 2 Then
DoCmd.RunSQL "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, dst_time, dst_task_id) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "')"
DoCmd.Close
MsgBox "Your OUTSIDE LISTS data has been saved."
Exit Sub

ElseIf (Me!task) = 3 Then
DoCmd.RunSQL "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, dst_time, dst_task_id) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "')"
DoCmd.Close
MsgBox "Your DISCONTINUED OR TRANSFERRED PUB CONVERSION FILES data has been saved."
Exit Sub......................

.............................
 
To store the SQL, simply dimension a string variable, and use that:

Dim strSQL as string

strSQL = "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, dst_time, dst_task_id) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "')"

I would also use a Select Case, rather than many ElseIf's:

Select Case Me!Task
Case 1
DoCmd.RunSQL strSQL
MsgBox "Your CYCLE END data has been saved."
Exit Sub
Case 2
DoCmd.RunSQL strSQL
MsgBox "Your OUTSIDE LISTS data has been saved."
Exit Sub
Case 3
DoCmd.RunSQL strSQL
MsgBox "Your DISCONTINUED OR TRANSFERRED PUB CONVERSION FILES data has been saved."
Exit Sub
Case 4
etc....
etc....
etc....
End Select

HTH,

Matt.

NB: This would probably have been better posted in the Modules/VBA forum.:D
 
Last edited:
Thanks. I implemented the variable at this point. It works just the same as before.

There is an unrelated problem though. When the user clicks apply, I get a message "You are about to append 1 row(s)." If you click okay, then the data is appended, etc. If you click cancel on this window, I get a run time error. How can I just close that window and return to the form when cancel is clicked?

I did not create this pop up mesage, rather I believe it is a generic access window.

Below is my form:

_________________________________________________

Option Compare Database
Option Explicit


Private Sub apply_Click()

Dim strSQL As String
strSQL = "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, dst_time, dst_task_id) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "')"

If IsNull(Me!user) Then
MsgBox "You must enter a user name."

ElseIf IsNull(Me!rr) Then
MsgBox "You must place a value in the request received field. If you did not receive a request this date, enter 0"

ElseIf IsNull(Me!rc) Then
MsgBox "You must place a value in the request completed field. If you did not complete a request this date, enter 0"

ElseIf IsNull(Me!time) Then
MsgBox "You must place a values in the time field. If you used no time for this process today, enter 0"

ElseIf IsNull(Me!task) Then
MsgBox "You must select as task."

ElseIf (Me!task) = 1 Then
DoCmd.RunSQL strSQL
DoCmd.Close
MsgBox "Your CYCLE END data has been saved."
Exit Sub

ElseIf (Me!task) = 2 Then
DoCmd.RunSQL strSQL
DoCmd.Close
MsgBox "Your OUTSIDE LISTS data has been saved."
Exit Sub.........................................................

etc.......
 
To get rid of the warning message you have two choices. Either turn the warnings off, then on:

DoCmd.SetWarnings False
DoCmd.RunSql strSQL
DoCmd.SetWarnings True

OR, replace DoCmd.RunSql with CurrentDB.Execute:

CurrentDB.Execute strSQL
 
after implementing...everything works good

okay, thanks for the help. I have implemented your suggestions and the outcome is great.

This form is a generic form for users to enter a selection of tasks, time, etc.

The user selects these tasks from a drop down list (values stored in a table).

There are some fields that will not need to be keyed into upon selection of certain tasks. Those fields are Me!rr and Me!rc.

The problem is that I have thses fields "required" in my else if statements.

The other field is totalunits, which is on my form but not yet listed in my code. Is there a way to disable these fields upon selection of a certain task? If there is, how do I fit it into my mess of If's?

form:

Option Compare Database
Option Explicit



Private Sub apply_Click()

Dim Response As String

Dim strSQL As String
strSQL = "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, dst_time, dst_task_id) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "')"

If IsNull(Me!user) Then
MsgBox "You must enter a user name."

ElseIf IsNull(Me!rr) Then
MsgBox "You must place a value in the request received field. If you did not receive a request this date, enter 0"

ElseIf IsNull(Me!rc) Then
MsgBox "You must place a value in the request completed field. If you did not complete a request this date, enter 0"

ElseIf IsNull(Me!time) Then
MsgBox "You must place a values in the time field. If you used no time for this process today, enter 0"

ElseIf IsNull(Me!task) Then
MsgBox "You must select as task."

ElseIf (Me!task) = 1 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your CYCLE END data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 2 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your OUTSIDE LIST data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 3 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your DISCONTINUED OR TRANSFERRED PUB PRELIMINARY CONVERSION FILES data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 4 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your DISCONTINUED OR TRANSFERRED PUB LIVE CONVERSION FILES data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 5 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your DISCONTINUED OR TRANSFERRED PUB SHUT DOWN data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 6 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your FREQUENCY CHANGES TEST data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 7 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your FREQUENCY CHANGES LIVE data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 8 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your NEW PUB SET UP - TABLE SET UP data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 9 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your NEW PUB SET UP - FILE CONVERSION TEST data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 10 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your NEW PUB SET UP - FILE CONVERSION LIVE data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 11 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your RENEWALS AND AUTORENEWALS - MAINTENANCE data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 12 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your RENEWALS AND AUTORENEWALS - PROCESSING ORDERS data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 13 Then
Response = MsgBox("You are about to append your data to the database." & Chr(13) & "You can not edit your data after it has been saved." & Chr(13) & "Are you sure you want to continue?", vbYesNo + vbExclamation)
If Response = vbYes Then
CurrentDb.Execute strSQL
MsgBox "Your SPECIAL LETTER, SYSTEM LETTER, LABEL CREATION AND ORDERS data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

and the list of else if's continue to Else If (Me!task) = 42 Then..........
 
You say that if users select certain tasks then the rr and rc fields will not need to be entered, but in the code you check to make sure they have a value?

I'm assuming the combo box the users select the task from is called task? The following on the after_update event will force the value to 0 and disable the control for the selected tasks:

Code: 
Private Sub Task_AfterUpdate()
    Select Case Task.Value
    Case 3, 5, 8, 15, 32
    rc.Value = 0: rc.Enabled = False
    rr.Value = 0: rr.Enabled = False
    Case Else
    rc.Enabled = True
    rr.Enabled = True
    End Select
End Sub

I've added a second sql string - which does not update the rr and rc fields. If you're happy to push through the default value of 0 that they get as a result of the above code, then stick with just the one sql string.

Code: 
Private Sub apply_Click()

Dim varResponse As Variant ' This should be a variant not a string
Dim strSQL1 As String ' Full sql insert string
Dim strSQL2 As String ' Partial sql insert string
Dim strMessage As String ' Message to check user wants to append data

    strSQL1 = "INSERT INTO tbldst(dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, " _
        & "dst_time, dst_task_id) VALUES ('" & Me.Date & "','" & Me.User & "'," _
        & "'" & Me.rr & "','" & Me.rc & "','" & Me.Time & "','" & Me.Task & "')"

    strSQL2 = "INSERT INTO tbldst(dst_date, dst_user, dst_time, dst_task_id) VALUES " _
        & "('" & Me.Date & "','" & Me.User & "','" & Me.Time & "','" & Me.Task & "')"

    strMessage = "You are about to append your data to the database." & Chr(13) _
        & "You can not edit your data after it has been saved." & Chr(13) _
        & "Are you sure you want to continue?"
    
    If IsNull(Me!User) Then
    MsgBox "You must enter a user name."

    ElseIf IsNull(Me!rr) Then
    MsgBox "You must place a value in the request received field. " _
        & "If you did not receive a request this date, enter 0"

    ElseIf IsNull(Me!rc) Then
    MsgBox "You must place a value in the request completed field. " _
        & "If you did not complete a request this date, enter 0"

    ElseIf IsNull(Me!Time) Then
    MsgBox "You must place a values in the time field. " _
        & "If you used no time for this process today, enter 0"

    ElseIf IsNull(Me!Task) Then
    MsgBox "You must select as task."

    ElseIf (Me!Task) = 1 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your CYCLE END data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 2 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your OUTSIDE LIST data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 3 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your DISCONTINUED OR TRANSFERRED PUB PRELIMINARY CONVERSION FILES " _
        & "data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 4 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your DISCONTINUED OR TRANSFERRED PUB LIVE CONVERSION FILES " _
        & "data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 5 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL2
    MsgBox "Your DISCONTINUED OR TRANSFERRED PUB SHUT DOWN data has " _
        & "been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 6 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL2
    MsgBox "Your FREQUENCY CHANGES TEST data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 7 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL2
    MsgBox "Your FREQUENCY CHANGES LIVE data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 8 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your NEW PUB SET UP - TABLE SET UP data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 9 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your NEW PUB SET UP - FILE CONVERSION TEST data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 10 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your NEW PUB SET UP - FILE CONVERSION LIVE data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 11 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your RENEWALS AND AUTORENEWALS - MAINTENANCE data has been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 12 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your RENEWALS AND AUTORENEWALS - PROCESSING ORDERS data has been " _
        & "saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    ElseIf (Me!Task) = 13 Then
    varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
    If varResponse = vbYes Then
    CurrentDb.Execute strSQL1
    MsgBox "Your SPECIAL LETTER, SYSTEM LETTER, LABEL CREATION AND ORDERS data has " _
        & "been saved.", vbInformation
    DoCmd.Close
    End If
    Exit Sub

    End If
Exit Sub

HTH,

Matt.
 
what a gem

I tried this code, I have to say it works great. Just what I needed.

My next daunting task is to set up a single form that acts as an advanced search. I have laid out this form very similar to the one we were just working on.

The query needs to return data laid out on a report. How would I go about doing this? QBF? Below is my current form code: (keep in mind that I have not yet changed everything, field names, etc.)


Option Compare Database
Option Explicit



Private Sub Form_Load()
OneDate.Enabled = False
End Sub

Private Sub one_date_option_AfterUpdate()
If one_date_option.Value = True Then
UserStartDate.Enabled = False
UserEndDate.Enabled = False
OneDate.Enabled = True
Me.UserStartDate = ""
Me.UserEndDate = ""
End If

If one_date_option.Value = False Then
UserStartDate.Enabled = True
UserEndDate.Enabled = True
OneDate.Enabled = False
Me.OneDate = ""

End If

End Sub




Private Sub apply_Click()
Dim varResponse As Variant ' This should be a variant not a string
Dim strQUERY1 As String ' Query selects....
Dim strSQL2 As String ' Partial sql insert string
Dim strMessage As String ' Message to check user wants to append data

strQUERY1 = "SELECT * FROM tbldst WHERE (dst_date, dst_user, dst_num_req_rec, dst_num_req_pro, " _
& "dst_time, dst_task_id, dst_total_unit) VALUES ('" & Me.date & "','" & Me.user & "'," _
& "'" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.task & "','" & Me.total & "')"

strSQL2 = "INSERT INTO tbldst(dst_date, dst_user, dst_time, dst_task_id, dst_total_unit) VALUES " _
& "('" & Me.date & "','" & Me.user & "','" & Me.time & "','" & Me.task & "','" & Me.total & "')"

strMessage = "You are about to append your data to the database." & Chr(13) _
& "You can not edit your data after it has been saved." & Chr(13) _
& "Are you sure you want to continue?"



If IsNull(Me!user) Then
MsgBox "You must enter a user name."

ElseIf IsNull(Me!rr) Then
MsgBox "You must place a value in the request received field. If you did not receive a request this date, enter 0"

ElseIf IsNull(Me!rc) Then
MsgBox "You must place a value in the request completed field. If you did not complete a request this date, enter 0"

ElseIf IsNull(Me!time) Then
MsgBox "You must place a values in the time field. If you used no time for this process today, enter 0"

ElseIf IsNull(Me!task) Then
MsgBox "You must select as task."

ElseIf (Me!task) = 1 Then
varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
If varResponse = vbYes Then
CurrentDb.Execute strSQL1
MsgBox "Your CYCLE END data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub

ElseIf (Me!task) = 2 Then
varResponse = MsgBox(strMessage, vbYesNo + vbExclamation)
If varResponse = vbYes Then
CurrentDb.Execute strSQL1
MsgBox "Your OUTSIDE LIST data has been saved.", vbInformation
DoCmd.Close
End If
Exit Sub
 
You must log in or register to reply here.

Similar threads

W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
6
Views
1,130
Ken Sheridan
Ken Sheridan
A
Combining multiple reports into a PDF export
Replies
2
Views
923
KitaYama
K
D
Solved Unbound form required using VBA
Replies
7
Views
1,200
vanzie
V
C
Sendkeys not working anymore
Replies
5
Views
2,421
NauticalGent
NauticalGent
R
code with old value
Replies
4
Views
938
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom