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