SQL Result to a variable

[Guest]

I'm attempting to assign the results of a SQL Statement to a variable, which
will then result in an insert statement. The SQL Statement is being created
correctly, but I can't seem to figure out, how to get the results of the
statement to my variable. Variables not declared here are declared in the
global section.

Any help would be greatly appreciated.

Private Sub Submit_Click()
Dim SID As Variant
Dim CLI As Variant
Dim Alias As Variant
Dim NetworkName As Variant
Dim ServiceName As Variant
Dim SiteName As Variant
Dim CountryName As Variant
Dim Domain As Variant
Dim CountryCode As Variant
Dim InsertString As Variant
Dim Language As Variant
Dim SubmitDate As Date

SubmitDate = Now
Set dbs = CurrentDb

If IsNull(Me.CountryName) Then
Response = MsgBox("Country Name can not be empty", vbOKOnly,
"Country Empty")
If Response = vbOK Then
Me.CountryName.SetFocus
End If
End If

If IsNull(Me.NetworkName) Then
Response = MsgBox("Network Name can not be empty", vbOKOnly,
"Network Empty")
If Response = vbOK Then
Me.NetworkName.SetFocus
End If
End If

NetworkName = Me.NetworkName
ServiceName = Me.ServiceName
SiteName = Me.SiteName
CountryName = Me.CountryName
CountryCode = Me.CountryCode
Domain = Me.NewDomainName

SQL = "SELECT First(CountryLangID) " & _
"FROM dbo_CountryLang WHERE CountryCode = '" & _
CountryCode & "';"

Language = "German"

MSG = "Country Language ID: " & Language & " SQL Statement: " & SQL
Title = "Variable Values"
Style = vbOKOnly
Response = MsgBox(MSG, Style, Title)

If Response = vbOK Then
Me.CountryName.SetFocus
End If


End Sub

 

[John Nurick]

You need to use the SQL to open a recordset. Example:

Dim rs As DAO.Recordset
Dim LangID As Long
...
Set rs = DBEngine(0)(0).OpenRecordset( _
SQL, dbOpenSnapshot)
If Not rs.EOF Then
LangID = rs.Fields(0).Value
Else
'no record found
End If
rs.Close