[AccessD] Get Value from Record Set?

For the achives. Again, big thanks to Stuart for helping out.  V happy as now I writing this directly into the report.  Much faster than how I used to do it.

' -------- Working Code Start --------------
Option Compare Database
Option Explicit

Sub UpdateReportTL_Headers()

Dim sSQL As String
Dim lAPLID As Long
Dim lFYPID As Long
Dim rs1 As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim sPERIOD As String

On Error GoTo UpdateReportTL_Headers_Error

Set db = CurrentDb
For i = 0 To 5

    lFYPID = [Forms]![frm_FYP_APLID_Admin].FYPID.Value
    lFYPID = (lFYPID - i)
    
    sSQL = "SELECT tbl_FY_Period.FY_P"
    sSQL = sSQL & " FROM tbl_FY_Period"
    sSQL = sSQL & " WHERE (((tbl_FY_Period.FY_P_ID)=" & lFYPID & "));"
    
    Set rs1 = db.OpenRecordset(sSQL)
    sPERIOD = Right(rs1!FY_P.Value, 2)
    Select Case i
        Case 0
            [Reports]![rpt_ProjectSummary].lblNow.Caption = sPERIOD
        Case 1
            [Reports]![rpt_ProjectSummary].lblNow_1.Caption = sPERIOD
        Case 2
            [Reports]![rpt_ProjectSummary].lblNow_2.Caption = sPERIOD
        Case 3
            [Reports]![rpt_ProjectSummary].lblNow_3.Caption = sPERIOD
        Case 4
            [Reports]![rpt_ProjectSummary].lblNow_4.Caption = sPERIOD
        Case 5
            [Reports]![rpt_ProjectSummary].lblNow_5.Caption = sPERIOD
    End Select
    Set rs1 = Nothing
Next i
Set db = Nothing

On Error GoTo 0
Exit Sub

'============= ERROR HANDLE ===================================
UpdateReportTL_Headers_Error:

    Set rs1 = Nothing
    Set db = Nothing
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateReportTL_Headers of Module Update_ReportHeadersTL"

End Sub
' -------- Working Code End --------------






-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stuart
McLachlan
Sent: Wednesday, 21 May 2008 3:35 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Get Value from Record Set?


Take your pick :-)

[Forms]![frm_FYP_APLID_Admin].[FYP1].Value = rs1!FYP

or

[Forms]![frm_FYP_APLID_Admin].[FYP1].Value = rs1(0)

or

[Forms]![frm_FYP_APLID_Admin].[FYP1].Value = rs1("FYP")
 


On 21 May 2008 at 15:25, Darryl Collins wrote:

> 
> 
> Hey Everyone,
> 
> I want to return one value from a table and I am struggling with it. I can get the KeyID I need and can get the entry I want, but I cannot seem to actually read the value itself.  Clearly I am stuffing something obvious up.
> 
> Table layout is like this
> 
> KeyID   FYP       OtherBlah   OtherBlah2
> 100     FY08 01   ra ra       ra ra
> 101     FY08 02   ra ra       ra ra
> 102     FY08 03   ra ra       ra ra
> etc
> 
> The value I want back is actually the FYP field.
> 
> '''--- CODE SAMPLE BELOW -------
> Sub UpdateTL_1()
> 
> Dim sSQL As String
> Dim lFYPID As Long
> Dim rs1 As DAO.Recordset
> Dim db As DAO.Database
> 
>    'On Error GoTo UpdateTL_1_Error
> 
> lFYPID = [Forms]![frm_FYP_APLID_Admin].FYPID.Value
> lFYPID = (lFYPID - 1) ' <-- THIS GET THE KEYID.
> 
> sSQL = "SELECT tbl_FY_Period.FY_P"
> sSQL = sSQL & " FROM tbl_FY_Period"
> sSQL = sSQL & " WHERE (((tbl_FY_Period.FY_P_ID)=" & lFYPID & "));"
> 
> Set db = CurrentDb
> Set rs1 = db.OpenRecordset(sSQL)
> 
> MsgBox rs1.RecordCount '<-- THIS RETURNS 1
> MsgBox rs1.Fields.Count '<-- THIS RETURNS 1
> 
> ' AIRCODE - THIS BIT I AM STUCK ON. How do I get the VALUE of that field
> '[Forms]![frm_FYP_APLID_Admin].[FYP1].Value = rs1.Fields.FYP.Value
> 
> Set rs1 = Nothing
> Set db = Nothing
> 
> This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.