SQL not working

[Lkwdmntr]

I was using a DLookup, but was advised to check to see if the record exists first. This is what I did, but I need to fine tune the SELECT statement.

Private Sub NextToMeals_Click()

Dim Id As Integer, recCount As Integer

Id = Form_FRM_Select_Users.SelectUserID.Value
recCount = "SELECT Count(Standard_Actions.[WeekNumber]) AS recCount _
            FROM Standard_Actions GROUP BY Standard_Actions.[UserId] HAVING Standard_Actions.[UserId]= Id;"
            
If recCount = 0 Then
    DoCmd.OpenForm "FRM_Meal_Categories", acNormal, , , acFormAdd
    Form_FRM_Meal_Categories.UserID.Value = Id
    Form_FRM_Meal_Categories.WeekNumber.Value = wkNum1
    Form_FRM_Meal_Categories.FullName.Value = DLookup("[FullName]", "Users", "[UserID]=" & Id)
    Form_FRM_Meal_Categories.Before_Breakfast_Snack.SetFocus
    DoCmd.Close acForm, "FRM_Select_Users"
Else
    If recCount = 1 Then
        DoCmd.OpenForm "FRM_Edit_Standard_Actions", , , "UserID =" & Id & " And " & "WeekNumber =" & wkNum2, acFormEdit
        Form_FRM_Edit_Standard_Actions.UserID.Value = Id
        Form_FRM_Edit_Standard_Actions.WeekNumber.Value = wkNum1
        Form_FRM_Edit_Standard_Actions.RiseTime.SetFocus
        DoCmd.Close acForm, "FRM_Select_Users"
        Else
        MsgBox "You have already created a plan for this user. If you would like to edit week one, select 'Edit Existing User' in the Main Menu.", vbOKOnly
        DoCmd.OpenForm "MainMenu", acNormal
        DoCmd.Close acForm, "FRM_Select_Users"
    End If
End If

End Sub

 

[The_Doc_Man]

From your title, it seems you are suggesting that SQL isn't working. But nowhere in your code have you asked it to work.

Id = Form_FRM_Select_Users.SelectUserID.Value
recCount = "SELECT Count(Standard_Actions.[WeekNumber]) AS recCount _
            FROM Standard_Actions GROUP BY Standard_Actions.[UserId] HAVING Standard_Actions.[UserId]= Id;"

You have in essence defined an SQL string that cannot be stored in the data type you declared for the variable. To use SQL, you need to do multiple steps. First you define the string. Then you do something that EXECUTES the string in any of several ways. You did the first part but not the second.

Id = Form_FRM_Select_Users.SelectUserID
recCount = DCount( "[WeekNumber]", "Standard_Actions", "[UserID]=" & CStr( ID ) )

First, you extracted .Value from the form, which is OK but kind of overkill... because the default property of a control IS the .Value if that control even CAN have a value.

Second, you defined an SQL string but didn't execute it - and didn't need to anyway. The DCount will do what it appears that you want. You don't need to GROUP any records because commutativity assures you of the correct sum regardless of the order in which the numbers are presented.

For the record, defining the SQL string in the way you did would have failed because the string is not a condoned data type for the variable you were using. Defining the string doesn't execute it. And you would have had no results with the SELECT statement anyway because SELECT doesn't return a value - it returns a recordset, and you were not set up to harvest the recordset.

 

[CJ_London]

just finished saying the same thing so no point repeating, but I would also add that when you typed

recCount = "SELECT Count(Standard_Actions.[WeekNumber]) AS recCount _
FROM Standard_Actions GROUP BY Standard_Actions.[UserId] HAVING Standard_Actions.[UserId]= Id;"

it would have gone red because the string is missing double quotes. it should be (ignoring it is syntactically incorrect anyway)

recCount = "SELECT Count(Standard_Actions.[WeekNumber]) AS [COLOR="Red"]recCount" &  _
            " FROM[/COLOR] Standard_Actions GROUP BY Standard_Actions.[UserId] HAVING Standard_Actions.[UserId]= Id;"

 

[plog]

I was using a DLookup, but was advised to check to see if the record exists first.

Use a DCount (https://www.techonthenet.com/access/functions/domain/dcount.php). It solves the problem of the record not existing--it returns 0 in those cases.

Also, are you sure you want an else/if:

...
Else
    If recCount = 1 Then...

If you have 2 or more records that get found meeting your criteria it will not hit that block of code (2!=1). It might be best just to just use else, not else/if.