how to retrieve a value from an SQL statement (1 Viewer)

[nelson]

hi there,

I have a form called frmOrganisation with a text field name called txtOrganisationID, and tables tblES and tblOrganisation

now I need to execute an SQL Query

Set rst = New ADODB.Recordset
Dim strsql As String
Dim strsqlInt As Interger

rst.CursorLocation = adUseClient

strqry = "SELECT Organisation_id from tblES WHERE Organisation_id = " & Me.Organisation_id.Value & ";"

rst.Open strqry, CurrentProject.Connection

' i tried DoCmd.RunSQL <strqry> and CurrentDb.Execute <strsql> with no luck...

' i need to get the value from strqry and then pass it to an interger variable, any idea guys?

strsqlInt = strqry ( i need to cast/convert the result to int)
rst.Close


Thanks in advanced guys.... im stucked...

WinXP+Access2000

 

[nelson]

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strES As String

strSQL = "SELECT Organisation_ID"
strSQL = strSQL & " FROM Employment_Screening"
strSQL = strSQL & " WHERE Organisation_ID= " & Me.Organisation_id & ";"



Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)


If rst.RecordCount = 0 Then
' do nothing...hehehehe
Else
rst.MoveLast
strES = rst.Fields("[Organisation_id]")
End If

MsgBox "Value of strES " + strES
rst.Close

Set rst = Nothing
Set dbs = Nothing

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

dont worry...I figured it out now....hahahahahaha