Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-09-2019, 09:16 AM   #1
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
DLookup not working

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

Lkwdmntr is offline   Reply With Quote
Old 09-09-2019, 09:19 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,036 Times in 1,017 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: DLookup not working

Hi. Try it this way:
Code:
lookupwk1 = DLookup("[WeekNumber]", "Standard_Actions", "[UserID]=" & Id & " And [WeekNumber] =" & wkNum1)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-09-2019, 09:33 AM   #3
Lkwdmntr
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

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 is offline   Reply With Quote
Old 09-09-2019, 09:37 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,036 Times in 1,017 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: DLookup not working

Quote:
Originally Posted by Lkwdmntr View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-09-2019, 09:38 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,096
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup not working

This appears to be backwards:

Me.SelectUserID = Id
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-09-2019, 10:18 AM   #6
Lkwdmntr
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

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?
Lkwdmntr is offline   Reply With Quote
Old 09-09-2019, 10:28 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,096
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup not working

What does the code look like now? You can use the code tags (# icon) to preserve formatting.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-09-2019, 10:40 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,036 Times in 1,017 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: DLookup not working

Quote:
Originally Posted by Lkwdmntr View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-11-2019, 05:51 PM   #9
Lkwdmntr
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

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.Se tFocus
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 is offline   Reply With Quote
Old 09-11-2019, 06:26 PM   #10
Lkwdmntr
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"
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 is offline   Reply With Quote
Old 09-12-2019, 08:30 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,096
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup not working

Looks like further discussion should happen here:

https://www.access-programmers.co.uk...d.php?t=306806
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-12-2019, 09:52 AM   #12
Lkwdmntr
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

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.Se tFocus
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
Lkwdmntr is offline   Reply With Quote
Old 09-12-2019, 09:59 AM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,096
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup not working

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-13-2019, 09:33 PM   #14
Lkwdmntr
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

You're absolutely right, I switched it around and am using the DCount. Working great. Thanks.
Lkwdmntr is offline   Reply With Quote
Old 09-14-2019, 07:15 AM   #15
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,096
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup not working

Happy to help!

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookup within DLookup not working brunopg7 Queries 3 04-22-2011 07:08 AM
Working Dlookup code not working Gazza2 Modules & VBA 16 02-28-2011 04:56 PM
Dlookup not working Djblois Forms 1 02-24-2010 07:12 AM
DLOOKUP not working monvani Queries 6 10-17-2008 12:08 PM
please can u help me why this dlookup not working aminamin Forms 11 02-03-2008 10:20 PM




All times are GMT -8. The time now is 01:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World