Advertisement

08.28.2008 at 12:44PM PDT, ID: 23686932
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.3

MS Access 2003 If Then Else Statements not firing properly

Asked by jdol2949 in Access Forms, SQL Query Syntax, Microsoft Development

Tags: , , ,

MS Access 2003 -
I have a sales order form.  In the form footer, I have a complete button that runs several queries, but has two different If Then statements that fire off before they are allowed to send.  I have a sub form that requires all three fields have something in them, or the user can't send their data.  I also have a few fields on my main form that are required too, and are validated after the subform data's If Then statement.  The problem that I'm having is my subform validation is working correctly, but if I don't enter the required fields in the main form after the screen gives me one prompt for doing so, then the data sends off anyway, and the sales order is created.  I don't want this to happen.  Someone please take a look at my button code and let me know what's in the wrong spot.  Thanks!Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:

Option Compare Database
 
Private Sub cmdSend_Click()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strMax As String
Dim strSO As String
Dim strSQL As String
Dim strSOList As String
Set rs1 = CurrentDb.OpenRecordset("SELECT PrinterID, ServiceTech, Electronic, InterimSONum FROM tblRMASOExtras WHERE InterimSONum = " & Me!InterimSONum)
If IsNull(rs1!PrinterID) Or IsNull(rs1!ServiceTech) Or IsNull(rs1!Electronic) Then
    MsgBox "Please make sure the Service Tech, PrinterID, and Method of Communication fields are entered in the RMA Extras tab.", vbCritical, "Can't send data to SW"
Else
strSOList = "The following Sales Orders were created in Service Works:" & vbCrLf & vbCrLf
 
    If IsNull(Me!txtLocationNmbr) Or IsNull(Me!cboRequestBy) Or IsNull(Me!cboSalesRep) Or IsNull(Me!txtRMANumber) Or (Me!txtRMANumber = 0) Then
        MsgBox "Please make sure the REQUEST BY, SALES REP, and LOCATION NUMBER fields are filled in.", vbCritical, "Can't send data to SW"
    Else
        rs1.Close
End If
    Set rs = CurrentDb.OpenRecordset("SELECT InterimSONum, SalesOrderNmbr FROM tblRMA WHERE Status = 'HOLD';")
    
    strMax = DMax("SalesOrderNmbr", "SalesOrder", "LEFT(SalesOrderNmbr, 1) = 'S'")
    iMax = Val(Mid(strMax, 2))
    
    Do Until rs.EOF
        iMax = iMax + 1
        strSO = "S" & Right("00000" & iMax, 5)
        rs.Edit
            rs!SalesOrderNmbr = strSO
        rs.Update
                
        strSQL = "UPDATE tblRMAItem SET SalesOrderNmbr = '" & strSO & "' WHERE InterimSONum = " & rs!InterimSONum & ";"
        
        DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        strSOList = strSOList & "  " & strSO & vbCrLf
        
        rs.MoveNext
    Loop
    rs.Close
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qapCreateRMASOToSW"
    DoCmd.OpenQuery "qapCreateRMASOItemToSW"
    DoCmd.OpenQuery "qupSetSOsToSent"
    DoCmd.OpenQuery "qupSetSOsToCancelled"
    
    DoCmd.SetWarnings False
    
    MsgBox strSOList, vbInformation, "Success"
 
Dim strMaxSO As String
Dim iMaxSO As String
 
strMaxSO = DMax("SalesOrderNmbr", "SalesOrder", "LEFT(SalesOrderNmbr, 1) = 'S'")
iMaxSO = Val(Mid(strMaxSO, 2))
iMaxSO = iMaxSO + 1
strMaxSO = "S" & Right("00000" & iMaxSO, 5)
strSQL = "UPDATE Company SET SalesOrderNmbr = '" & strMaxSO & "';"
 
 
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
 
        DoCmd.Close acForm, Me.Name
    End If
End Sub
[+][-]08.28.2008 at 12:52PM PDT, ID: 22339082

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Access Forms, SQL Query Syntax, Microsoft Development
Tags: Microsoft, Access, 2003, VBA
Sign Up Now!
Solution Provided By: mwvisa1
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628