DLookup not working

L

Lkwdmntr

Registered User.
Local time
Today, 15:29
Joined
Jul 10, 2019
Messages
315
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
​
 
Hi. Try it this way:
Code: 
lookupwk1 = DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum1)
 
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.
 
Lkwdmntr said:
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.
Click to expand...
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.
 
This appears to be backwards:

Me.SelectUserID = Id
 
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?
 
What does the code look like now? You can use the code tags (# icon) to preserve formatting.
 
Lkwdmntr said:
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?
Click to expand...
If the user has no Week2, what do you want to happen?
 
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.
 
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​
 
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
​
 
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.
 
You're absolutely right, I switched it around and am using the DCount. Working great. Thanks.
 
You must log in or register to reply here.

Similar threads

S
Delete All controls from A Form
2
Replies
20
Views
837
Pat Hartman
P
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
256
Mike Krailo
Mike Krailo
D
Solved Change password using tempvar not passing to following form
Replies
4
Views
354
Gasman
Gasman
J
Colour text in Text Box
Replies
8
Views
547
arnelgp
arnelgp
A
How i make remember password in form login on my pc only !
Replies
7
Views
1,397
KitaYama
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom