Returning a value from a query field to a form in Access 2000

[Frank]

Hi,
Is it possible to return a value from a SELECT query "field" to:
1) a form control -- or
2) a table
I have a query I created in the SQL editor that returns a PASS/FAIL value in
a Result field. I would like to pass this Result from the 30 queries/tests
I'm doing to a main form, which displays names for each of the available
queries/reports. If there is a FAIL anywhere in the query Result field then
that test fails (see below). The test names/records on the form are txtboxes
that feed from a table named ReportNames.
Is the only way to get the result is to create separate update queries for
for each test, or is there a way to pass values from that Result field in the
SELECT query.
Below--the very last select field is the one I would like to pass to a label
or update in the table ReportNames. Thanks for any suggestions or help.

Investment percentage test
Investment Amount Portfolio % Result
USCPA $6,085.00 69.18% PASS
MMF $2,060.00 23.42% PASS
TRV $651.00 7.40% FAIL <--The main form would then
show FAIL for this particular Test:

Investment percentage Test FAIL

SELECT
IIf(([SP Rating ]="A-1+" Or [SP Rating]="A-1")
And [MDY Rating]="P-1",
"A-1 / P-1",
"A-2 / P-2") AS [Investment Rating],
[Asset and SLN Data].[Counterparty ],
Sum([Asset and SLN Data].[Orig Notional]) AS [Investment Notional],
IIf([Investment Rating]="A-1 / P-1",200,75) AS [Limit ($m)],
Sum([Orig Notional ]/DSum("[Orig Notional]","Asset and SLN Data","[Trade
TypeID ] = 'USCPA'")) AS [Portfolio %],
IIf([Investment Rating]="A-1 / P-1" And [Investment
Notional]<=200000000,"PASS",IIf([Investment Rating]="A-2 / P-2" And
[Investment Notional]<75000000,"PASS","FAIL")) AS Result

FROM
[Asset and SLN Data]

WHERE
((([Asset and SLN Data].[Trade TypeID ])="uscpa"))

GROUP BY
IIf(([SP Rating ]="A-1+" Or [SP Rating]="A-1") And [MDY Rating]="P-1","A-1 /
P-1","A-2 / P-2"),
[Asset and SLN Data].[Counterparty ],
IIf([SP Rating]="A-1+" Or [SP Rating]="A-1",1,2),
IIf([MDY Rating]="P-1",1,2)

ORDER BY
IIf(([SP Rating ]="A-1+" Or [SP Rating]="A-1") And [MDY Rating]="P-1","A-1 /
P-1","A-2 / P-2");

 

[Theo Geer]

Frank,

You can do either. if you need the information in a form, use the below
process. If you would prefer it in a table (If it's static data and isn't
going to change often this would be easiest) then just change the Query to an
Update Query (Right click on the table area of Query Builder and select Query
Type --> Update Query).

The general process is below, but you should be able to expand it however
you need.

Private Sub GetPassOrFail()
Dim rst as Recordset

' This line will set the results of your Query to a Recordset
set rst = currentdb.openrecordset("NAMEOFYOURQUERY")

' You can use this line to set any control to any field in your Query
me.NAMEOFCONTROL = rst("Result")

rst.close
End Sub

If you're looping this use the following in the body of your sub.

dim i as integer
for i=1 to rst.recordcount
' ENTER ALL OF YOUR COMANDS HERE
rst.movenext
next i

Hope this helps!

Theo

Hi,
Is it possible to return a value from a SELECT query "field" to:
1) a form control -- or
2) a table
I have a query I created in the SQL editor that returns a PASS/FAIL value in
a Result field. I would like to pass this Result from the 30 queries/tests
I'm doing to a main form, which displays names for each of the available
queries/reports. If there is a FAIL anywhere in the query Result field then
that test fails (see below). The test names/records on the form are txtboxes
that feed from a table named ReportNames.
Is the only way to get the result is to create separate update queries for
for each test, or is there a way to pass values from that Result field in the
SELECT query.
Below--the very last select field is the one I would like to pass to a label
or update in the table ReportNames. Thanks for any suggestions or help.

Investment percentage test
Investment Amount Portfolio % Result
USCPA $6,085.00 69.18% PASS
MMF $2,060.00 23.42% PASS
TRV $651.00 7.40% FAIL <--The main form would then
show FAIL for this particular Test:

Investment percentage Test FAIL

SELECT
IIf(([SP Rating ]="A-1+" Or [SP Rating]="A-1")
And [MDY Rating]="P-1",
"A-1 / P-1",
"A-2 / P-2") AS [Investment Rating],
[Asset and SLN Data].[Counterparty ],
Sum([Asset and SLN Data].[Orig Notional]) AS [Investment Notional],
IIf([Investment Rating]="A-1 / P-1",200,75) AS [Limit ($m)],
Sum([Orig Notional ]/DSum("[Orig Notional]","Asset and SLN Data","[Trade
TypeID ] = 'USCPA'")) AS [Portfolio %],
IIf([Investment Rating]="A-1 / P-1" And [Investment
Notional]<=200000000,"PASS",IIf([Investment Rating]="A-2 / P-2" And
[Investment Notional]<75000000,"PASS","FAIL")) AS Result

FROM
[Asset and SLN Data]

WHERE
((([Asset and SLN Data].[Trade TypeID ])="uscpa"))

GROUP BY
IIf(([SP Rating ]="A-1+" Or [SP Rating]="A-1") And [MDY Rating]="P-1","A-1 /
P-1","A-2 / P-2"),
[Asset and SLN Data].[Counterparty ],
IIf([SP Rating]="A-1+" Or [SP Rating]="A-1",1,2),
IIf([MDY Rating]="P-1",1,2)

ORDER BY
IIf(([SP Rating ]="A-1+" Or [SP Rating]="A-1") And [MDY Rating]="P-1","A-1 /
P-1","A-2 / P-2");

 

[Frank]

Theo,
I finally had a chance to work on this. The code works great and will do
wonders for my project -- thanks a bunch.
I've adapted it to run through a table of query names to open and return
PASS/FAIL values from -- the only problem I'm having is returing those value
to a table ( I'm missing something with the update or edit or addnew was
hoping the openrecordset method applied to tables as well:

Private Sub CmdRunResults_Click()
Dim Rst As Recordset
Dim Rslt As String
Dim i As Integer

Dim TblSet As Recordset
Dim RptCount As Integer
Dim RptName As String

Set TblSet = CurrentDb.OpenRecordset("Report Names")
For RptCount = 1 To TblSet.RecordCount
RptName = TblSet("Long Name")

Set Rst = CurrentDb.OpenRecordset(RptName)
For i = 1 To Rst.RecordCount
If Rst("Result") = "FAIL" Then
Rslt = Rst("Result")
Exit For
Else
Rslt = "PASS"
Rst.MoveNext
End If
Next i
TblSet.AddNew("test results") = Rslt <<<----GIVING ERROR HERE - test
results is the field name in the table
Rst.Close
Next RptCount
TblSet.Close
End Sub

 

[Frank]

Folks,
I don't know if anyone else can help with getting values updated to a table
field. AddNew is definitley not right for it adds a new record. See below.
Thanks for any assistance.
Frank