Run Query in VB code

[T_Tronix]

I have a forum where I am using a checkbox. Now I only want one record to have this checkbox = true so I created a query that checked if there are any records with this checkbox as "true". But now I want to execute this query once someone clicks on the checkbox field in a new record (to give an error message if the query is not null that the checkbox was already checked in another record).



I got this code but keep getting errors:

Dim dbsCurrent As Database
Dim rsCount As Recordset
Dim result As String

Set dbsCurrent = CurrentDb
Set rsCount = dbsCurrent.OpenRecordset("SELECT Tasks.Resource_F, Tasks.Resource_L, Tasks.Project_Lead, Tasks.Resource_ACF2ID FROM Tasks WHERE(((Tasks.Project_Name)=[forms]![frmTasks]![cmbProject]) AND ((Tasks.Project_Lead)=True))")

result = rsCount![Resource_F]
result1 = rsCount![Resource_L]
rsCount.Close


MsgBox "There is already someone checked:" & result & " " & result1, vbInformation, "Example"
Me!chkLead = False
Exit Sub
End If

[June7]

Variables cannot be enclosed in quote marks, they must be concatenated. Reference to control on form is a variable.

Set rsCount = dbsCurrent.OpenRecordset("SELECT Resource_F, Resource_L, Project_Lead, Resource_ACF2ID FROM Tasks WHERE Tasks.Project_Lead=True AND Project_Name='" & [forms]![frmTasks]![cmbProject]) & "';")

Assumes Project_Name is a text data type.

Alternative to opening a recordset is DCount(). Access Help has info about domain aggregate functions.

[ssanfu]

You have to concatenate the value of the combo box to the string, otherwise you are looking for the string "[Forms]![frmTasks]![cmbProject]"

This might get you moving again:
(Assumes the combo box is text)

   Dim dbsCurrent As DAO.Database
   Dim rsCount As DAO.Recordset
   Dim result As String
   Dim sSQL As String

   Set dbsCurrent = CurrentDb

   sSQL = "SELECT Tasks.Resource_F, Tasks.Resource_L, Tasks.Project_Lead, Tasks.Resource_ACF2ID"
   sSQL = sSQL & " FROM Tasks"
   sSQL = sSQL & " WHERE Tasks.Project_Name = '" & [Forms]![frmTasks]![cmbProject] & "' AND Tasks.Project_Lead =True"

   Set rsCount = dbsCurrent.OpenRecordset(sSQL)

   result = rsCount![Resource_F]
   '   result1 = rsCount![Resource_L]
   
   rsCount.Close
   Set rsCount = Nothing
   Set dbsCurrent = Nothing

   If result > 0 Then
      MsgBox "There is already someone checked:" & result & " " & result1, vbInformation, "Example"
      Me!chkLead = False
   End If

edit: I see June beat me again

[T_Tronix]

fixed it up:

strSQL = "SELECT Tasks.Resource_F, Tasks.Resource_L, Tasks.Project_Lead, Tasks.Resource_ACF2ID"
strSQL = strSQL & " FROM Tasks"
strSQL = strSQL & " WHERE Tasks.Project_Name = " & "'" & Me!cmbProject & "'" & " AND Tasks.Project_Lead= True;"

Works now...