DLookup and DCount statements aren't working (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
Hi,

I have a form that when the next button on the previous form is clicked, it grabs the value of a combo box from the previous form and uses it to fetch the FullName field in one table.(DLookup)

Then from the same value from the previous form fetches the record count of another table.

Not sure what I'm doing wrong here. Here is my button code and the DLookup and DCount that I have as the Control Source.

Private Sub Next_Click()

DoCmd.OpenForm "Edit_Existing_PlanwithStatus", acNormal
DoCmd.Maximize
DoCmd.Close acForm, "Edit_Existing_Plan"

End Sub​

=DLookUp("[FullName]","Standard_Actions","[UserID] =" & [Forms]![Edit_Existing_Plan]![SelectUser])

=DCount("*","Meal_Categories","UserId =" & [Forms]![Edit_Existing_Plan]![SelectUser])

Any help would greatly be appreciated.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:55
Joined
Aug 30, 2003
Messages
36,118
The functions need the first form to remain open, you're closing it. It can be hidden if you want.
 

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
I would like to set and use a Temporary Variable for this. Where would you suggest I do that?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:55
Joined
Aug 30, 2003
Messages
36,118
You can use a VBA variable declared in a standard module or TempVar, which I don't have experience with (old dog, new trick). A textbox on a form that always stays open would work too. As you're using it above, the variable wouldn't work directly, as it's only available in VBA. You could create a function that returned the value of the variable, a common workaround.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:55
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Next_Click()

DoCmd.OpenForm "Edit_Existing_PlanwithStatus", acNormal
DoCmd.Maximize

[COLOR="Blue"]If IsNull(Tempvars!tvarSelectedUser) Then
    Tempvars.Add "tvarSelectedUser", 0
End If
Tempvars!tvarSelectedUser = [Forms]![Edit_Existing_Plan]![SelectUser]
[/COLOR]
DoCmd.Close acForm, "Edit_Existing_Plan"

End Sub

=DLookUp("[FullName]","Standard_Actions","[UserID] =" & [COLOR="blue"][Tempvars]![tvarSelectedUser][/COLOR])

=DCount("*","Meal_Categories","UserId =" & [COLOR="blue"][Tempvars]![tvarSelectedUser][/COLOR])
 

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
I am convinced that my DLookup and DCount are not formatted correctly. I still can't get this to work.
 

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
arnelg - I got an error "Tempvars can only store data. They can not store objects"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:55
Joined
May 7, 2009
Messages
19,169
Tempvars!tvarSelectedUser = [Forms]![Edit_Existing_Plan]![SelectUser].Value
 

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
Well, no error, but I there is still nothing going into the textbox on the form. I don't get it, I have tried so many different things.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:55
Joined
May 7, 2009
Messages
19,169
you have two forms there?

1. Edit_Existing_PlanwithStatus
2. Edit_Existing_Plan

which one will the combo come from?
 

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
I'm not sure what I did, but I got it working, Thanks.

On another issue, I have a form with a tabbed subform that has three forms in it. I get an error.
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"

I want to be able to edit these subforms, any suggestions.
 

Lkwdmntr

Registered User.
Local time
Today, 16:55
Joined
Jul 10, 2019
Messages
277
nevermind, I got it. I had to toggle Allow Additions and Deletions to "No".
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:55
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted.
 

Users who are viewing this thread

Top Bottom