• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

SQL not working (1 Viewer)

Lkwdmntr

Registered User.
Local time
Yesterday, 21:10
Joined
Jul 10, 2019
Messages
199
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.

Code:
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
 
Last edited by a moderator:

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 23:10
Joined
Feb 28, 2001
Messages
18,057
From your title, it seems you are suggesting that SQL isn't working. But nowhere in your code have you asked it to work.

Code:
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.

Code:
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

Super Moderator
Staff member
Local time
Today, 04:10
Joined
Feb 19, 2013
Messages
12,542
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)

Code:
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

Banishment Pending
Local time
Yesterday, 23:10
Joined
May 11, 2011
Messages
10,076
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:

Code:
...
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.
 

Users who are viewing this thread

Top Bottom