G
Guest
I need some help!!
Can someone look at this and tell me what I am doing wrong.
I am trying lookup a value in a table based on to form-field values and
return the result to a particular form-field. If I dun the query in access
and manually provide “parameters†the query runs and always returns one
record with one value (as expected).
The query also runs in VB if a manually use “generic values†but fails when
I use declared from-field values. Clearly, I am doing something wrong.
Also, I do not know how to return the result to a form field.
Private Sub cmdTest_Click()
'Delcare and instantiate one connection object and record set
Dim rst1 As New ADODB.Recordset
Dim cmd As New ADODB.Command
'Delcare form feild values
Dim recID As Object
Dim optionID As Object
Dim rlAmount As Object
Dim optionLTV As Object
' var for SQL text
Dim cmdTxt As Variant
'Set Feild Values needed for SQL statement
Set optionID = Me.fkOptionsMatrixID
Set rlAmount = Me.ReqLoanAmount
'Set Prim Key for current record in form
Set recID = Me.LoanID
'Set current "LTV Limit" value.
'This value will be updated with SQL result
Set optionLTV = Me.LtvLimit
'cmd string
cmdTxt = "SELECT tLTVm.fkOptionsMatrixId, tLTVm.LtvLimit " & _
"FROM tblLtvMatrix AS tLTVm " & _
"WHERE ((fkOptionsMatrixId = (optionID)) " & _
"AND (LowerLimit <= rlAmount) " & _
"AND (UpperLimit >= rlAmount));"
' Make sure an option value has been selected and a loan amount is entered.
If optionID.Value < 1 Or rlAmount.Value <= 0 Then
Exit Sub
Else 'Run SQL statement
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = cmdTxt
Set rst1 = cmd.Execute
'Next line Just for testing
Debug.Print rst1.GetString
'**** Need code to return SQL result to "optionLTV"
(Me.fkOptionsMatrixID)
'**** Need to refresh "Form Data" (Me.fkOptionsMatrixID is used to
calculate values in underling record set for form)
'Clean-up
rst1.Close
Set rst1 = Nothing
Set cmd = Nothing
End If
Can someone look at this and tell me what I am doing wrong.
I am trying lookup a value in a table based on to form-field values and
return the result to a particular form-field. If I dun the query in access
and manually provide “parameters†the query runs and always returns one
record with one value (as expected).
The query also runs in VB if a manually use “generic values†but fails when
I use declared from-field values. Clearly, I am doing something wrong.
Also, I do not know how to return the result to a form field.
Private Sub cmdTest_Click()
'Delcare and instantiate one connection object and record set
Dim rst1 As New ADODB.Recordset
Dim cmd As New ADODB.Command
'Delcare form feild values
Dim recID As Object
Dim optionID As Object
Dim rlAmount As Object
Dim optionLTV As Object
' var for SQL text
Dim cmdTxt As Variant
'Set Feild Values needed for SQL statement
Set optionID = Me.fkOptionsMatrixID
Set rlAmount = Me.ReqLoanAmount
'Set Prim Key for current record in form
Set recID = Me.LoanID
'Set current "LTV Limit" value.
'This value will be updated with SQL result
Set optionLTV = Me.LtvLimit
'cmd string
cmdTxt = "SELECT tLTVm.fkOptionsMatrixId, tLTVm.LtvLimit " & _
"FROM tblLtvMatrix AS tLTVm " & _
"WHERE ((fkOptionsMatrixId = (optionID)) " & _
"AND (LowerLimit <= rlAmount) " & _
"AND (UpperLimit >= rlAmount));"
' Make sure an option value has been selected and a loan amount is entered.
If optionID.Value < 1 Or rlAmount.Value <= 0 Then
Exit Sub
Else 'Run SQL statement
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = cmdTxt
Set rst1 = cmd.Execute
'Next line Just for testing
Debug.Print rst1.GetString
'**** Need code to return SQL result to "optionLTV"
(Me.fkOptionsMatrixID)
'**** Need to refresh "Form Data" (Me.fkOptionsMatrixID is used to
calculate values in underling record set for form)
'Clean-up
rst1.Close
Set rst1 = Nothing
Set cmd = Nothing
End If