DLookup not working (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
I'm having issues with this code. Getting an error. "Cant find field '|1' reffered to in your expresstion" Here's the code I'm using. Trying to check a table to see if a record exists with the specific user id and weeknumber.
Private Sub NextToMeals_Click()

Dim Id As Integer, wkNum1 As Integer, wkNum2 As Integer, Status As String, lookupwk1 As Integer, lookupwk2 As Integer

Me.SelectUserID = Id
wkNum1 = 1
wkNum2 = 2
lookupwk1 = DLookup("[WeekNumber]", "Standard_Actions", "[UserID]= Id " And [WeekNumber] = wkNum1)
lookupwk2 = DLookup("[WeekNumber]", "Standard_Actions", "[UserID]= Id " And [WeekNumber] = wkNum2)
If lookupwk1 = wkNum1 Then
If lookupwk2 = wkNum2 Then
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"
Else
DoCmd.OpenForm "FRM_Edit_Standard_Actions", acNormal, , , 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"
End If
Else
DoCmd.OpenForm "FRM_Edit_Standard_Actions", acNormal, , , 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"
End If

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,357
Hi. Try it this way:
Code:
lookupwk1 = DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum1)
 

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
I had that at first and switched it because I got the error "Invalid use of Null". A little confused here. I am getting the id value from a drop down list that provides the full name, but actually has the value as a number. Don't know if that is causing any issues or not. It worked for me on other forms.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,357
I had that at first and switched it because I got the error "Invalid use of Null". A little confused here. I am getting the id value from a drop down list that provides the full name, but actually has the value as a number. Don't know if that is causing any issues or not. It worked for me on other forms.
Hi. Are you saying both UserID and WeekNumber are Numeric fields? Then, what I gave you should work. Unless, you didn't select a UserID and WeekNumber before executing the code. If that could happen, you could try using the Nz() function or maybe check there's valid values first and don't execute the DLookup() if you have missing values.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
This appears to be backwards:

Me.SelectUserID = Id
 

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
Ok, so I switched thing around a bit and it's working, but for some reason, it's not acknowledging the wkNum2. I'm testing it with one user that doesn't have a week 2 and it should go on to FRM_Edit_Standard_Actions. Instead, it pulls up the msgbox. Can you see something I don't?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
What does the code look like now? You can use the code tags (# icon) to preserve formatting.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,357
Ok, so I switched thing around a bit and it's working, but for some reason, it's not acknowledging the wkNum2. I'm testing it with one user that doesn't have a week 2 and it should go on to FRM_Edit_Standard_Actions. Instead, it pulls up the msgbox. Can you see something I don't?
If the user has no Week2, what do you want to happen?
 

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
If the StandardActions table doesn't have a WeekNumber that is equal to 2, then it should open the Edit_Standard_Actions form. Here is my revised code. I'm testing it with no week 2 in the table and it is still advancing to the Else part of the statement.

Dim Id As Integer, wkNum1 As Integer, wkNum2 As Integer, Status As String, lookupwk1 As Integer, lookupwk2 As Integer

Id = Form_FRM_Select_Users.SelectUserID.Value
wkNum1 = 1
wkNum2 = 2

If DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum1) <> 1 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 DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum2) <> 2 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​

Sorry, been unable to look at this for a couple of days. I'm in a coding boot camp and we have 23 chapters to read by Sunday. Been a little busy.
 

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
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.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​
 

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
Thanks for all the input. I went back to the DLookup and just changed the <> to >=. Works well now. Thanks again.
Dim Id As Integer, wkNum1 As Integer, wkNum2 As Integer, Status As String, lookupwk1 As Integer, lookupwk2 As Integer

Id = Form_FRM_Select_Users.SelectUserID.Value
wkNum1 = 1
wkNum2 = 2

If DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum1) <> 1 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 DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum2) >= 2 Then
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"
Else
DoCmd.OpenForm "FRM_Edit_Standard_Actions", , , "UserID =" & Id & " And " & "WeekNumber =" & wkNum1, acFormEdit
Form_FRM_Edit_Standard_Actions.RiseTime.SetFocus
DoCmd.Close acForm, "FRM_Select_Users"
End If
End If
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
I'd have used the DCount() recommended. The DLookup() is likely returning Null if there's no record with week number 2. I suppose it will fail the ">=2" test like you want, but it feels wrong to rely on it.
 

Lkwdmntr

Registered User.
Local time
Today, 04:26
Joined
Jul 10, 2019
Messages
277
You're absolutely right, I switched it around and am using the DCount. Working great. Thanks.
 

Users who are viewing this thread

Top Bottom