Help on writing SQL statements in a VBA event

[Guest]

Help on writing SQL statements in a VBA event

I need to create an On-Change event that runs a small query in VB. The
query need to run anytime the “Loan Amount†value changes. The query is
needed to return a value from a table that is not directly related to the
form’s record set and store the value to a particular field [LtvLimit]. I
have created and tested the query as a parameter query and it works as
expected but I do not know how to do this in VB. I am experienced at writing
VBA code for Excel applications but not Access.

The parameter query is as follows:

SELECT tLTVm.LtvLimit

FROM tblOptionsMatrix AS tOPTm INNER JOIN (tblLtvMatrix AS tLTVm INNER JOIN
tblLoanInfo AS [tLI-1] ON tLTVm.fkOptionsMatrixId =
[tLI-1].fkOptionsMatrixID) ON tOPTm.OptionsMatrixID = tLTVm.fkOptionsMatrixId

WHERE ((([tLI-1].LoanID)=[ID]) AND
(([tLI-1].fkOptionsMatrixID)=[tLTVm].[fkOptionsMatrixID]) AND
((tLTVm.LowerLimit)<[tLI-1.ReqLoanAmount]) AND
((tLTVm.UpperLimit)>=[tLI-1.ReqLoanAmount]));

-------------------------------------------------------------------------------------------------------
Table tLTVm is stores LTV amounts based on lower and upper of loan amount
limits AND “option typesâ€. The table includes the following fields:

[LtvMatrixID] (Prime-Key)
[fkOptionsMatrixID]
[LowerLimit]
[UpperLimit]
[LTV]

[tLTVm].[fkOptionsMatrixID] has a many to 1 relation to table “[tOPTm].
[OptionsMatrixID]†Note that [tLTVm] is not directly related to [tLI-1]

-------------------------------------------------------------------------------------------------------

Sample Data in [tLTVm]:

[LtvMatrixID], [fkOptionsMatrixID], [LowerLimit], [UpperLimit], [LTV]

1, 1, $0.00, $500,000, 0.90
2, 1, $500,000, $650,000, 0.85
3, 1, $650,000, $1,000,000, 0.70
4, 2, $0.00, $333,700, 0.90
5, 2, $333,700, $500,000, 0.85
6, 2, $500,000, $750,000, 0.80
7, 2, 750,000, $1,000,000, 0.65
Etc, Etc

The query relies on 2 values in the form’s record set.
[RequestedLoanAmount] AND [fkOptionsMatrixID]. The form’s record set also
has a field named [fkOptionsMatrixID]. Both fields are related to an
intermediate table that stores “Option Typesâ€. Each Option Type has
muiltiple [tLTVm] records. The query is intended to find the corresponding
[tLTVm].[LTV] value for the current option selected in the form and also
based on the [RequestedLoanAmount] value found within the
[tLTVm].[UpperLimit] and [tLTVm].[LowerLimit]

Thanks for any help!

 

[[MVP] S.Clark]

The OnChange Event fires the instant the value changes. This means the
first keystroke of change, so you probably don't want to fire then. Instead
consider using the AfterUpdate event.

Executing a query from code can be done using the Execute command with
either ADO or DAO.

For more information about forms and coding them, feel free to visit any one
of the forms newsgroups.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Help on writing SQL statements in a VBA event

I need to create an On-Change event that runs a small query in VB. The
query need to run anytime the "Loan Amount" value changes. The query is
needed to return a value from a table that is not directly related to the
form's record set and store the value to a particular field [LtvLimit]. I
have created and tested the query as a parameter query and it works as
expected but I do not know how to do this in VB. I am experienced at writing
VBA code for Excel applications but not Access.

The parameter query is as follows:

SELECT tLTVm.LtvLimit

FROM tblOptionsMatrix AS tOPTm INNER JOIN (tblLtvMatrix AS tLTVm INNER JOIN
tblLoanInfo AS [tLI-1] ON tLTVm.fkOptionsMatrixId =
[tLI-1].fkOptionsMatrixID) ON tOPTm.OptionsMatrixID = tLTVm.fkOptionsMatrixId

WHERE ((([tLI-1].LoanID)=[ID]) AND
(([tLI-1].fkOptionsMatrixID)=[tLTVm].[fkOptionsMatrixID]) AND
((tLTVm.LowerLimit)<[tLI-1.ReqLoanAmount]) AND
((tLTVm.UpperLimit)>=[tLI-1.ReqLoanAmount]));

-------------------------------------------------------------------------- -----------------------------
Table tLTVm is stores LTV amounts based on lower and upper of loan amount
limits AND "option types". The table includes the following fields:

[LtvMatrixID] (Prime-Key)
[fkOptionsMatrixID]
[LowerLimit]
[UpperLimit]
[LTV]

[tLTVm].[fkOptionsMatrixID] has a many to 1 relation to table "[tOPTm].
[OptionsMatrixID]" Note that [tLTVm] is not directly related to [tLI-1]

-------------------------------------------------------------------------- -----------------------------

Sample Data in [tLTVm]:

[LtvMatrixID], [fkOptionsMatrixID], [LowerLimit], [UpperLimit], [LTV]

1, 1, $0.00, $500,000, 0.90
2, 1, $500,000, $650,000, 0.85
3, 1, $650,000, $1,000,000, 0.70
4, 2, $0.00, $333,700, 0.90
5, 2, $333,700, $500,000, 0.85
6, 2, $500,000, $750,000, 0.80
7, 2, 750,000, $1,000,000, 0.65
Etc, Etc

The query relies on 2 values in the form's record set.
[RequestedLoanAmount] AND [fkOptionsMatrixID]. The form's record set also
has a field named [fkOptionsMatrixID]. Both fields are related to an
intermediate table that stores "Option Types". Each Option Type has
muiltiple [tLTVm] records. The query is intended to find the corresponding
[tLTVm].[LTV] value for the current option selected in the form and also
based on the [RequestedLoanAmount] value found within the
[tLTVm].[UpperLimit] and [tLTVm].[LowerLimit]

Thanks for any help!