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: