Grabbing Field Value-VBA

[4ukh]

hi!
how to get the field value in VBA with the help of SQL statement and assign this value to a Variable.... and then use that in the code...?

thank you

4ukh

[aktharshaik]

In which appliation R U writing VBA code? MS-Access or excel?
Where is the table from which u want to read the fields from? Is it an Excel Spreadsheet or an Access Database or some other DB?

Please make some clarity in your problem and will help to solve it out precisely.

[4ukh]

well it is MS Access 2003 and what i need is to pick the largest integer value of the field and pass this value to a variable.
more datelines are as under:

TABLE NAME: CRID_INDEX
TABLE COLUMNS: CRID. CRID_NUM and CRID_TYPE.

SELECT THE RECORD WHERE CRID_NUM HAVE A LARGEST VALUE AND ASSIGN THIS VALUE TO A VARIABLE CALLED Vtemp AS INTEGER.

HOPE THIS HELP

THANKS

[aktharshaik]

USE THE OBJECT CurrentDb.

Public Sub GetMaxCRID()

    Dim Vtemp As Integer

    On Error GoTo GetMaxCRID_Error

    'Here first we check whether or not if any record exists in the table
    If CurrentDb.OpenRecordset("SELECT COUNT(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0) > 0 Then
        Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0)
    Else
        Vtemp = 0
    End If
    'Now you have the maximum number in the field CRID_NUM

GetMaxCRID_Done:
    Exit Sub

GetMaxCRID_Error:
    MsgBox "An error has occured in procedure GetMaxCRID." & vbCrLf & vbCrLf & "Error No: " & Err.Number & vbCrLf & Err.Description
    Resume GetMaxCRID_Done

End Sub

[4ukh]

Yahoooooooooooo!

it works...

thanks man

[4ukh]

Ops excuse me mr. shaikh would you please tell me one more thing why do you write Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0) this "(0)" at the end of the above statement...

thank you...

best regards

[aktharshaik]

Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0)

Here (0) is to return the value of the first field in the query and store it into Vtemp. If say u have more number of fields and u want to get the value of the third field in the sequence of recordset values u have to write (2) like

x = CurrentDb.OpenRecordset("SELECT sName, sLastName, sAge FROM EMPLOYEE WHERE sEmpId = 23")(2)
This will return the value of field age into variable x

[4ukh]

OK good i got it...

thanks

Best Regards