SQL Query and Form

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

How can I query another table to find values and compare with a form field
to get a value?

I have a Income Level table that has Currency Values In fields Named
M1,M2,M3,L1,L2,L3,VL1,VL2,VL3 etc..
Moderate = M, Low = L Very Low = VL, the numbers following the letters a
Family size.

In a client intake form users enter Annual Income Amount and Family Size.

I need to compare Annual Income and Family size to Income Level table fields
to get a Value of 1, 2, 3, or 4.

The follwong code is not working. I keep receiving a error messasge.

Private Sub txtEI_AfterUpdate()
Dim vFamilySize As Integer
Dim strSQL As String
Dim vIncome As Currency
Dim vIncomeLevel As Integer
Dim M1, M2, M3, M4, M5, M6, M7, M8, L1, L2, L3, L4, L5, L6, L7, L8, VL1,
VL2, VL3, VL4, VL5, VL6, VL7, VL8 As Currency


vFamilySize = Me![txtFMS].Value
vIncome = Me![txtEI].Value

'Determine Family Size using case statement,
'then determine income level comparing
'Income Level entered and field comparisons

Select Case vFamilySize
Case 1
strSQL = "SELECT tblIncome_Level.[M1], tblIncome_Level.[L1],
tblIncome_Level.[VL1] FROM tblIncome_Level"
M1 = strSQL("M1")
L1 = strSQL("L1")
VL1 = strSQL("VL1")
If vIncome > L1 And vIncome <= M1 Then
vIncomeLevel = 3
ElseIf vIncome > VL1 And vIncome <= L1 Then
vIncomeLevel = 2
ElseIf vIncome <= VL1 Then
vIncomeLevel = 1
End If
Case 2
strSQL = "SELECT tblIncome_Level.[M2], tblIncome_Level.[L2],
tblIncome_Level.[VL2] FROM tblIncome_Level"
M1 = strSQL("M2")
L1 = strSQL("L2")
VL1 = strSQL("VL2")
If vIncome > L2 And vIncome <= M2 Then
vIncomeLevel = 3
ElseIf vIncome > VL2 And vIncome <= L2 Then
vIncomeLevel = 2
ElseIf vIncome <= VL2 Then
vIncomeLevel = 1
End If
Case 3
strSQL = "SELECT tblIncome_Level.[M3], tblIncome_Level.[L3],
tblIncome_Level.[VL3] FROM tblIncome_Level"
M1 = strSQL("M3")
L1 = strSQL("L3")
VL1 = strSQL("VL3")
If vIncome > L3 And vIncome <= M3 Then
vIncomeLevel = 3
ElseIf vIncome > VL3 And vIncome <= L3 Then
vIncomeLevel = 2
ElseIf vIncome <= VL3 Then
vIncomeLevel = 1
End If
Case 4
strSQL = "SELECT tblIncome_Level.[M4], tblIncome_Level.[L4],
tblIncome_Level.[VL4] FROM tblIncome_Level"
M1 = strSQL("M1")
L1 = strSQL("L1")
VL1 = strSQL("VL1")
If vIncome > L4 And vIncome <= M4 Then
vIncomeLevel = 3
ElseIf vIncome > VL4 And vIncome <= L4 Then
vIncomeLevel = 2
ElseIf vIncome <= VL4 Then
vIncomeLevel = 1
End If
Case 5
strSQL = "SELECT tblIncome_Level.[M5], tblIncome_Level.[L5],
tblIncome_Level.[VL5] FROM tblIncome_Level"
M1 = strSQL("M5")
L1 = strSQL("L5")
VL1 = strSQL("VL5")
If vIncome > L5 And vIncome <= M5 Then
vIncomeLevel = 3
ElseIf vIncome > VL5 And vIncome <= L5 Then
vIncomeLevel = 2
ElseIf vIncome <= VL5 Then
vIncomeLevel = 1
End If
Case 6
strSQL = "SELECT tblIncome_Level.[M6], tblIncome_Level.[L6],
tblIncome_Level.[VL6] FROM tblIncome_Level"
M1 = strSQL("M6")
L1 = strSQL("L6")
VL1 = strSQL("VL6")
If vIncome > L6 And vIncome <= M6 Then
vIncomeLevel = 3
ElseIf vIncome > VL6 And vIncome <= L6 Then
vIncomeLevel = 2
ElseIf vIncome <= VL6 Then
vIncomeLevel = 1
End If
Case 7
strSQL = "SELECT tblIncome_Level.[M7], tblIncome_Level.[L7],
tblIncome_Level.[VL7] FROM tblIncome_Level"
M1 = strSQL("M7")
L1 = strSQL("L7")
VL1 = strSQL("VL7")
If vIncome > L7 And vIncome <= M7 Then
vIncomeLevel = 3
ElseIf vIncome > VL7 And vIncome <= L7 Then
vIncomeLevel = 2
ElseIf vIncome <= VL7 Then
vIncomeLevel = 1
End If
Case Is >= 8
strSQL = "SELECT tblIncome_Level.[M8], tblIncome_Level.[L8],
tblIncome_Level.[VL8] FROM tblIncome_Level"
M1 = strSQL("M8")
L1 = strSQL("L8")
VL1 = strSQL("VL8")
If vIncome > L8 And vIncome <= M8 Then
vIncomeLevel = 3
ElseIf vIncome > VL8 And vIncome <= L8 Then
vIncomeLevel = 2
ElseIf vIncome <= VL8 Then
vIncomeLevel = 1
End If
End Select

Me!ctlIL.Value = vIncomeLevel
Me.Refresh

End Sub

Please Help
-Bryan
 
Hello,

How can I query another table to find values and compare with a form field
to get a value?

I have a Income Level table that has Currency Values In fields Named
M1,M2,M3,L1,L2,L3,VL1,VL2,VL3 etc..
Moderate = M, Low = L Very Low = VL, the numbers following the letters a
Family size.

In a client intake form users enter Annual Income Amount and Family Size.

I need to compare Annual Income and Family size to Income Level table fields
to get a Value of 1, 2, 3, or 4.

The follwong code is not working. I keep receiving a error messasge.

Private Sub txtEI_AfterUpdate()
Dim vFamilySize As Integer
Dim strSQL As String
Dim vIncome As Currency
Dim vIncomeLevel As Integer
Dim M1, M2, M3, M4, M5, M6, M7, M8, L1, L2, L3, L4, L5, L6, L7, L8, VL1,
VL2, VL3, VL4, VL5, VL6, VL7, VL8 As Currency


vFamilySize = Me![txtFMS].Value
vIncome = Me![txtEI].Value

'Determine Family Size using case statement,
'then determine income level comparing
'Income Level entered and field comparisons

Select Case vFamilySize
Case 1
strSQL = "SELECT tblIncome_Level.[M1], tblIncome_Level.[L1],
tblIncome_Level.[VL1] FROM tblIncome_Level"
M1 = strSQL("M1")
L1 = strSQL("L1")
VL1 = strSQL("VL1")
If vIncome > L1 And vIncome <= M1 Then
vIncomeLevel = 3
ElseIf vIncome > VL1 And vIncome <= L1 Then
vIncomeLevel = 2
ElseIf vIncome <= VL1 Then
vIncomeLevel = 1
End If
Case 2
strSQL = "SELECT tblIncome_Level.[M2], tblIncome_Level.[L2],
tblIncome_Level.[VL2] FROM tblIncome_Level"
M1 = strSQL("M2")
L1 = strSQL("L2")
VL1 = strSQL("VL2")
If vIncome > L2 And vIncome <= M2 Then
vIncomeLevel = 3
ElseIf vIncome > VL2 And vIncome <= L2 Then
vIncomeLevel = 2
ElseIf vIncome <= VL2 Then
vIncomeLevel = 1
End If
Case 3
strSQL = "SELECT tblIncome_Level.[M3], tblIncome_Level.[L3],
tblIncome_Level.[VL3] FROM tblIncome_Level"
M1 = strSQL("M3")
L1 = strSQL("L3")
VL1 = strSQL("VL3")
If vIncome > L3 And vIncome <= M3 Then
vIncomeLevel = 3
ElseIf vIncome > VL3 And vIncome <= L3 Then
vIncomeLevel = 2
ElseIf vIncome <= VL3 Then
vIncomeLevel = 1
End If
Click to expand...

<SNIP>

In each Case statement you are building an SQL string, but you are not creating
a recordset based on the SQL. Without the recordset the table is not being
queried at all so no data is available for comparison. You are getting an error
because your code is trying to read values from strSQL which is nothing more
than a line of text.

Also each Case statement is (trying) assigning the field value to the same 3
variables (M1, L1, VL1) but then goes on to determine the value of vIncome by
comparing different variables (M2, L2, VL2 etc). All these variables will be
Null (because they are variants not currency) as they have not been assigned a
value.

Try something like this -

NOTE: You must explicitly declare the data type of each variable, your line -
Dim M1, M2, M3, M4, M5, M6, M7, M8, L1, L2, L3, L4, L5, L6, L7, L8, VL1,
VL2, VL3, VL4, VL5, VL6, VL7, VL8 As Currency
Is actually declaring ONE currency variable (VL8), all the rest of the variables
are variants. To make them all currency you need to declare each one
individually Dim M1 As Currency, M2 As Currency, M3 As Currency etc


Private Sub txtEI_AfterUpdate()
Dim vFamilySize As Integer
Dim strSQL As String
Dim vIncome As Currency
Dim vIncomeLevel As Integer
Dim M1 As Currency, M2 As Currency, M3 As Currency etc...

'ADD THESE
Dim db As DAO.Database
Dim rst As DAO.Recordset

vFamilySize = Me![txtFMS].Value
vIncome = Me![txtEI].Value

'Determine Family Size using case statement,
'then determine income level comparing
'Income Level entered and field comparisons

'ADD THIS
Set db = CurrentDb()

Select Case vFamilySize
Case 1
strSQL = "SELECT M1, L1, VL1 FROM tblIncome_Level;"
'ADD THIS
Set rst = db.OpenRecordset(strSQL,dbOpenSnapshot)
If rst.RecordCount >0 Then
rst.MoveFirst
M1 = rst!M1
L1 = rst!L1
VL1 = rst!VL1

If vIncome > L1 And vIncome <= M1 Then
vIncomeLevel = 3
ElseIf vIncome > VL1 And vIncome <= L1 Then
vIncomeLevel = 2
ElseIf vIncome <= VL1 Then
vIncomeLevel = 1
End If
End If
rst.Close
Case 2
strSQL = "SELECT M2, L2, VL2 FROM tblIncome_Level;"
Set rst = db.OpenRecordset(strSQL,dbOpenSnapshot)
If rst.RecordCount >0 Then
rst.MoveFirst
M2 = rst!M2
L2 = rst!L2
VL2 = rst!VL2

If vIncome > L2 And vIncome <= M2 Then
vIncomeLevel = 3
ElseIf vIncome > VL2 And vIncome <= L2 Then
vIncomeLevel = 2
ElseIf vIncome <= VL2 Then
vIncomeLevel = 1
End If
End If
rst.Close
Case 3
etc ....

End Select

Me!ctlIL.Value = vIncomeLevel
Me.Refresh

'ADD THIS
If Not (rst Is Nothing) Then Set rst = Nothing
If Not (db Is Nothing) Then Set db = Nothing

End Sub


Wayne Gillespie
Gosford NSW Australia
 
Back
Top