Advertisement

09.17.2008 at 11:20AM PDT, ID: 23739780
[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!
6.4

List Box on Tab Control imported from Access 97 to Access 2003 not displaying results

Asked by mdonovan_88 in Access Forms, Access Architecture/Design, Access Coding/Macros

A form that I imported from Access 97 to 2003 that contains a two page tab control, each with several list boxes functions perfectly in Access 97.  In 2003 the data populates perfectly into the list boxes on the first tab page (0), however on the second page (1), no data will display.  Very strange.  If I step through the code, it seems to execute fine.  The pass-through queries related to the second tab have the correct data when I run them independently after executing the code.

I have a second form (which I can show code for as reference if necessary) that functions in the same manner, imported from the same Access 97 database, and it works fine.  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:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:

Option Compare Database
Option Explicit
 
Public Function GetQuery() As Boolean
On Error GoTo gq_error
 
    Dim strSQL As String
    Dim fReturnVal As Boolean
 
    strSQL = "Select 'Totals' ""Totals"", to_char(sum(decode(decision_code,null,decode(process_code,'U',0,loan_amt),0)),'$99,999,990.00') ""In Process"", " _
        & " sum(decode(Nvl(decision_code,'0'),'0',0,1)) ""# Apps"",sum(decode(decision_code,'A', 1, 0)) ""# Approved"",to_char(sum(decode(decision_code,'A', loan_amt, 0)), '$99,999,990.00') ""$$ Approved"",Round(((Sum(decode(decision_code,'A', 1, 0)) / sum(decode(Nvl(decision_code,'0'),'0',0,1)))*100),2)||'%' ""% Approved"" from pps.loan_application where " _
        & " process_code <> 'U' and application_date >= to_date('" & Format(DS, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and application_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by 'Totals'"
 
 
    fReturnVal = editquery("qryTodaysBusinessListSum", strSQL, strconnect)
 
    strSQL = "Select decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12')) ""Brand"", to_char(sum(decode(decision_code,null,decode(process_code,'U',0,loan_amt),0)),'$99,999,990.00') ""In Process"", " _
       & " sum(decode(Nvl(decision_code,'0'),'0',0,1)) ""# Apps"",sum(decode(decision_code,'A', 1, 0)) ""# Approved"",to_char(sum(decode(decision_code,'A', loan_amt, 0)), '$99,999,990.00') ""$$ Approved"",Round(((Sum(decode(decision_code,'A', 1, 0)) / sum(decode(Nvl(decision_code,'0'),'0',0,1)))*100),2)||'%' ""% Approved"" from pps.loan_application where " _
        & " process_code <> 'U' and application_date >= to_date('" & Format(DS, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and application_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12'))"
 
    fReturnVal = editquery("qryTodaysBusinessList", strSQL, strconnect)
    
    If chkMonthToDate Then
    
        strSQL = "Select 'Totals' ""Totals"", to_char(sum(decode(decision_code,null,decode(process_code,'U',0,loan_amt),0)),'$99,999,990.00') ""In Process"", " _
        & " sum(decode(Nvl(decision_code,'0'),'0',0,1)) ""# Apps"",sum(decode(decision_code,'A', 1, 0)) ""# Approved"",to_char(sum(decode(decision_code,'A', loan_amt, 0)), '$99,999,990.00') ""$$ Approved"",Round(((Sum(decode(decision_code,'A', 1, 0)) / sum(decode(Nvl(decision_code,'0'),'0',0,1)))*100),2)||'%' ""% Approved"" from pps.loan_application where " _
        & " process_code <> 'U' and application_date >= to_date('" & Format(txtFirstOfMonth, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and application_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by 'Totals'"
 
 
        fReturnVal = editquery("qryTodaysBusinessListSumMonth", strSQL, strconnect)
 
        strSQL = "Select decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12')) ""Brand"", to_char(sum(decode(decision_code,null,decode(process_code,'U',0,loan_amt),0)),'$99,999,990.00') ""In Process"", " _
       & " sum(decode(Nvl(decision_code,'0'),'0',0,1)) ""# Apps"",sum(decode(decision_code,'A', 1, 0)) ""# Approved"",to_char(sum(decode(decision_code,'A', loan_amt, 0)), '$99,999,990.00') ""$$ Approved"",Round(((Sum(decode(decision_code,'A', 1, 0)) / sum(decode(Nvl(decision_code,'0'),'0',0,1)))*100),2)||'%' ""% Approved"" from pps.loan_application where " _
        & " process_code <> 'U' and application_date >= to_date('" & Format(txtFirstOfMonth, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and application_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12'))"
 
        fReturnVal = editquery("qryTodaysBusinessListMonth", strSQL, strconnect)
        
    End If
    
    If chkYearToDate Then
    
        strSQL = "Select 'Totals' ""Totals"", to_char(sum(decode(decision_code,null,decode(process_code,'U',0,loan_amt),0)),'$99,999,990.00') ""In Process"", " _
        & " sum(decode(Nvl(decision_code,'0'),'0',0,1)) ""# Apps"",sum(decode(decision_code,'A', 1, 0)) ""# Approved"",to_char(sum(decode(decision_code,'A', loan_amt, 0)), '$99,999,990.00') ""$$ Approved"",Round(((Sum(decode(decision_code,'A', 1, 0)) / sum(decode(Nvl(decision_code,'0'),'0',0,1)))*100),2)||'%' ""% Approved"" from pps.loan_application where " _
        & " process_code <> 'U' and application_date >= to_date('" & Format(txtFirstOfYear, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and application_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by 'Totals'"
 
 
        fReturnVal = editquery("qryTodaysBusinessListSumYear", strSQL, strconnect)
 
        strSQL = "Select decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12')) ""Brand"", to_char(sum(decode(decision_code,null,decode(process_code,'U',0,loan_amt),0)),'$99,999,990.00') ""In Process"", " _
       & " sum(decode(Nvl(decision_code,'0'),'0',0,1)) ""# Apps"",sum(decode(decision_code,'A', 1, 0)) ""# Approved"",to_char(sum(decode(decision_code,'A', loan_amt, 0)), '$99,999,990.00') ""$$ Approved"",Round(((Sum(decode(decision_code,'A', 1, 0)) / sum(decode(Nvl(decision_code,'0'),'0',0,1)))*100),2)||'%' ""% Approved"" from pps.loan_application where " _
        & " process_code <> 'U' and application_date >= to_date('" & Format(txtFirstOfYear, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and application_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12'))"
 
        fReturnVal = editquery("qryTodaysBusinessListYear", strSQL, strconnect)
        
    End If
    
 
gq_exit:
    GetQuery = fReturnVal
    Exit Function
 
gq_error:
    fReturnVal = False
    Call errormessage(Errors, "GetQuery Error")
    Resume gq_exit
 
End Function
Public Function GetQueryFunding() As Boolean
On Error GoTo gqf_error
 
    Dim strSQL As String
    Dim fReturnVal As Boolean
 
    strSQL = "Select 'Totals' ""Totals"", to_char(sum(decode(Pool_B,-1,loan_amt,0)),'$99,999,990.00') ""   $$ Pool B "", " _
        & " sum(decode(contract_status,'C',1,0)) ""# Funded"",to_char(sum(decode(contract_status,'C',loan_amt,0)),'$99,999,990.00') ""   $$ Funded"",Round(((sum(decode(contract_status,'C',decode(pool_b,-1,loan_amt,0),0,0)) / sum(decode(contract_status,'C',loan_amt,0)))*100),2)||'%' ""% of Pool B $$"" from pps.loan_application where " _
        & " disbursement_date >= to_date('" & Format(DS, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and disbursement_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by 'Totals'"
 
 
    fReturnVal = editquery("qryTodaysBusinessListFundingSum", strSQL, strconnect)
 
    strSQL = "Select decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12')) ""Brand"", to_char(sum(decode(Pool_B,-1,loan_amt,0)),'$99,999,990.00') ""   $$ Pool B "", " _
        & " sum(decode(contract_status,'C',1,0)) ""# Funded"",to_char(sum(decode(contract_status,'C',loan_amt,0)),'$99,999,990.00') ""   $$ Funded"",Round(((sum(decode(contract_status,'C',decode(pool_b,-1,loan_amt,0),0,0)) / sum(decode(contract_status,'C',loan_amt,0)))*100),2)||'%' ""% of Pool B $$"" from pps.loan_application where " _
        & " disbursement_date >= to_date('" & Format(DS, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and disbursement_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12'))"
 
 
    fReturnVal = editquery("qryTodaysBusinessListFunding", strSQL, strconnect)
    
    
    If chkMonthToDate Then
    
        strSQL = "Select 'Totals' ""Totals"", to_char(sum(decode(Pool_B,-1,loan_amt,0)),'$99,999,990.00') ""   $$ Pool B "", " _
        & " sum(decode(contract_status,'C',1,0)) ""# Funded"",to_char(sum(decode(contract_status,'C',loan_amt,0)),'$99,999,990.00') ""   $$ Funded"",Round(((sum(decode(contract_status,'C',decode(pool_b,-1,loan_amt,0),0,0)) / sum(decode(contract_status,'C',loan_amt,0)))*100),2)||'%' ""% of Pool B $$"" from pps.loan_application where " _
        & " disbursement_date >= to_date('" & Format(txtFirstOfMonth, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and disbursement_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by 'Totals'"
 
 
        fReturnVal = editquery("qryTodaysBusinessListFundingMonthSum", strSQL, strconnect)
 
        strSQL = "Select decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12')) ""Brand"", to_char(sum(decode(Pool_B,-1,loan_amt,0)),'$99,999,990.00') ""   $$ Pool B "", " _
        & " sum(decode(contract_status,'C',1,0)) ""# Funded"",to_char(sum(decode(contract_status,'C',loan_amt,0)),'$99,999,990.00') ""   $$ Funded"",Round(((sum(decode(contract_status,'C',decode(pool_b,-1,loan_amt,0),0,0)) / sum(decode(contract_status,'C',loan_amt,0)))*100),2)||'%' ""% of Pool B $$"" from pps.loan_application where " _
        & " disbursement_date >= to_date('" & Format(txtFirstOfMonth, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and disbursement_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12'))"
        
        fReturnVal = editquery("qryTodaysBusinessListFundingMonth", strSQL, strconnect)
        
    End If
    
    If chkYearToDate Then
    
        strSQL = "Select 'Totals' ""Totals"", to_char(sum(decode(Pool_B,-1,loan_amt,0)),'$99,999,990.00') ""   $$ Pool B "", " _
        & " sum(decode(contract_status,'C',1,0)) ""# Funded"",to_char(sum(decode(contract_status,'C',loan_amt,0)),'$99,999,990.00') ""   $$ Funded"",Round(((sum(decode(contract_status,'C',decode(pool_b,-1,loan_amt,0),0,0)) / sum(decode(contract_status,'C',loan_amt,0)))*100),2)||'%' ""% of Pool B $$"" from pps.loan_application where " _
        & " disbursement_date >= to_date('" & Format(txtFirstOfYear, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and disbursement_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by 'Totals'"
 
 
        fReturnVal = editquery("qryTodaysBusinessListFundingYearSum", strSQL, strconnect)
 
        strSQL = "Select decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12')) ""Brand"", to_char(sum(decode(Pool_B,-1,loan_amt,0)),'$99,999,990.00') ""   $$ Pool B "", " _
        & " sum(decode(contract_status,'C',1,0)) ""# Funded"",to_char(sum(decode(contract_status,'C',loan_amt,0)),'$99,999,990.00') ""   $$ Funded"",Round(((sum(decode(contract_status,'C',decode(pool_b,-1,loan_amt,0),0,0)) / sum(decode(contract_status,'C',loan_amt,0)))*100),2)||'%' ""% of Pool B $$"" from pps.loan_application where " _
        & " disbursement_date >= to_date('" & Format(txtFirstOfYear, "mm/dd/yyyy") & "', 'mm/dd/yyyy') and disbursement_date < to_date('" & Format(DS + 1, "mm/dd/yyyy") & "', 'mm/dd/yyyy') group by decode(brand,'ORTHO','ORTHO','DENTAL','DENTAL','EDU',decode(substr(app_type,-3),'FEE',decode(substr(app_type,1,3),'SYL','EDU Sylvan','HUN','EDU Huntington','EDU Other LC'),'EDU K-12'))"
        
        fReturnVal = editquery("qryTodaysBusinessListFundingYear", strSQL, strconnect)
    End If
    
 
gqf_exit:
    GetQueryFunding = fReturnVal
    Exit Function
 
gqf_error:
    fReturnVal = False
    Call errormessage(Errors, "GetQueryFunding Error")
    Resume gqf_exit
 
End Function
 
Private Sub chkMonthToDate_AfterUpdate()
DS_AfterUpdate
End Sub
 
Private Sub chkYearToDate_AfterUpdate()
DS_AfterUpdate
End Sub
 
Private Sub DS_AfterUpdate()
 
If Not IsNull(DS) Then
 
txtFirstOfMonth = DateSerial(year(DS), month(DS), 1)
txtFirstOfYear = DateSerial(year(DS), 1, 1)
 
Select Case tabData.value
        
Case 0
            
    If GetQuery Then
        Me.lstbusiness.RowSource = "qrytodaysbusinesslist"
        Me.lstBusinessSum.RowSource = "qryTodaysBusinessListSum"
            
        If chkMonthToDate Then
            Me.lstbusinessMonth.RowSource = "qrytodaysbusinesslistMonth"
            Me.lstBusinessSumMonth.RowSource = "qryTodaysBusinessListSumMonth"
        Else
            Me.lstbusinessMonth.RowSource = ""
            Me.lstBusinessSumMonth.RowSource = ""
        End If
                
        If chkYearToDate Then
            Me.lstbusinessYear.RowSource = "qrytodaysbusinesslistYear"
            Me.lstBusinessSumYear.RowSource = "qryTodaysBusinessListSumYear"
        Else
            Me.lstbusinessYear.RowSource = ""
            Me.lstBusinessSumYear.RowSource = ""
        End If
        
    End If
    
Case 1
            
     If GetQueryFunding Then
        Me.lstFundingBusiness.RowSource = "qryTodaysBusinessListFunding"
        Me.lstFundingBusinessSum.RowSource = "qryTodaysBusinessListFundingSum"
            
        If chkMonthToDate Then
            Me.lstFundingBusinessMonth.RowSource = "qrytodaysbusinesslistFundingMonth"
            Me.lstFundingBusinessMonthSum.RowSource = "qryTodaysBusinessListFundingMonthSum"
            Me.Requery
            Me.Recalc
        Else
            Me.lstFundingBusinessMonth.RowSource = ""
            Me.lstFundingBusinessMonthSum.RowSource = ""
        End If
                
        If chkYearToDate Then
            Me.lstFundingBusinessYear.RowSource = "qrytodaysbusinesslistFundingYear"
            Me.lstFundingBusinessYearSum.RowSource = "qryTodaysBusinessListFundingYearSum"
        Else
            Me.lstFundingBusinessYear.RowSource = ""
            Me.lstFundingBusinessYearSum.RowSource = ""
        End If
        
    End If
       
End Select
    
    
End If
 
End Sub
 
 
Private Sub Form_Load()
DS_AfterUpdate
End Sub
 
 
Private Sub tabData_Change()
DS_AfterUpdate
End Sub
[+][-]09.17.2008 at 05:55PM PDT, ID: 22505659

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, Access Architecture/Design, Access Coding/Macros
Sign Up Now!
Solution Provided By: mdonovan_88
Participating Experts: 0
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628