View Single Post
Old 09-11-2019, 06:26 PM   #10
Newly Registered User
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: DLookup not working

Looking over the sugguestions, I thought a SELECT statement that counts the number of weeks for the given user Id would be appropriate. I just need to fine tune the SELECT Statement. Here's the re-re-revised code.
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.Se tFocus
DoCmd.Close acForm, "FRM_Select_Users"
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
DoCmd.Close acForm, "FRM_Select_Users"
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
Lkwdmntr is offline   Reply With Quote