change text box based on result of sql query (count > 0)

[Banaticus]

What do I need to do to get something like the following to work:
Private Sub Form_Load()
IF (SELECT COUNT(1) FROM [App Info] INNER JOIN [Class Info] ON [App
Info].[Soc Sec #]=[Class Info].[Soc Sec] WHERE [Class Info].[Class Name]
alike "Qual%") > 0 THEN
[Qual Class Text Box].Text = (SELECT [Class Info].[Class Date] FROM [App
Info] INNER JOIN [Class Info] ON [App Info].[Soc Sec #]=[Class Info].[Soc
Sec] WHERE [Class Info].[Class Name] alike "Qual%")
End Sub

 

[Michel Walsh]

Hi,


You can try something like:


Private Sub Form_Load()

Dim strSQL = "SELECT COUNT(1) FROM [App Info] INNER JOIN [Class Info] ON
[App Info].[Soc Sec #]=[Class Info].[Soc Sec] WHERE [Class Info].[Class
Name] like 'Qual%' "

Dim strSQL2= "SELECT [Class Info].[Class Date] FROM [App Info] INNER JOIN
[Class Info] ON [App Info].[Soc Sec #]=[Class Info].[Soc Sec] WHERE [Class
Info].[Class Name] like 'Qual%' "


If CurrentProject.Connection.Execute(strSQL).Fields(0).Value > 0 then
[Qual Class Text Box].Text =
CurrentProject.Connection.Execute(strSQL2).Fields(0).Value
End if

End Sub



Hoping it may help,
Vanderghast, Access MVP