Help on writing SQL statements in a VBA event

[Guest]

Help on writing SQL statements in a VBA event

I need to create an After-Update 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!

 

[Michel Walsh]

Hi,


It is not clear why you use a SELECT query in an AFTER UPDATE (of the
form? ) You put the data... where? A SELECT query return data, so it has to
be put somewhere.

If you used an ACTION query, like an UPDATE, a DELETE or an INSERT, then
it would be a matter to do:


DoCmd.RunSQL "UPDATE ... "


in the After Update Event Procedure of the FORM ( I assume that part). That
single line, nothing more. But for a SELECT query, that sounds out of the
usual, since the data is to be place somewhere... and we just saved the
data... eventually moving to another record.


Hoping it may help,
Vanderghast, Access MVP

Help on writing SQL statements in a VBA event

I need to create an After-Update 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!

 

[Guest]

Thanks for you intrest in my problem. I was able to get this resolved and
working. To answer you question, the selcet is used to look-up values need
for stored calculations. These values are being stored in the underling
record set (qry). Yes I know that storing calculated values is not
favorable. In this case, it is the best opition I could come up with.

Hi,


It is not clear why you use a SELECT query in an AFTER UPDATE (of the
form? ) You put the data... where? A SELECT query return data, so it has to
be put somewhere.

If you used an ACTION query, like an UPDATE, a DELETE or an INSERT, then
it would be a matter to do:


DoCmd.RunSQL "UPDATE ... "


in the After Update Event Procedure of the FORM ( I assume that part). That
single line, nothing more. But for a SELECT query, that sounds out of the
usual, since the data is to be place somewhere... and we just saved the
data... eventually moving to another record.


Hoping it may help,
Vanderghast, Access MVP

Help on writing SQL statements in a VBA event

I need to create an After-Update 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!