Using DLOOKUP in code entries

  • Thread starter Thread starter GD
  • Start date Start date
G

GD

I'm trying to make areas of my form visible, based on a check box, along with
a dollar amount limit that each user will have. How is my code wrong?
Nothing appears when I click the check box. Also does the code go in both
the AfterUpdate & FormCurrent?

chkPaybackApproved is check box
txtVPRA is text box containing the form's $ amount
tblUsers is table containing everyone's approval limit
UserID of that table should coincide with CurrentUser(), thus determining
the limit allowed

Private Sub chkPaybackApproved_AfterUpdate()

Me.cboApprBy.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.txtApprDate.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.lblPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.cboPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))

End Sub
 
I've run the DLOOKUP on a separate area of the form, and it returns my
current approval limit, according to tblUsers. I don't think that's the
problem.

--
GD


Dennis said:
DLOOKUP returns a NULL if nothing is found that matches.

GD said:
I'm trying to make areas of my form visible, based on a check box, along with
a dollar amount limit that each user will have. How is my code wrong?
Nothing appears when I click the check box. Also does the code go in both
the AfterUpdate & FormCurrent?

chkPaybackApproved is check box
txtVPRA is text box containing the form's $ amount
tblUsers is table containing everyone's approval limit
UserID of that table should coincide with CurrentUser(), thus determining
the limit allowed

Private Sub chkPaybackApproved_AfterUpdate()

Me.cboApprBy.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.txtApprDate.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.lblPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.cboPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))

End Sub
Click to expand...
Click to expand...
 
True = -1 in VB, and False = 0. That might be the issue as well.

GD said:
I've run the DLOOKUP on a separate area of the form, and it returns my
current approval limit, according to tblUsers. I don't think that's the
problem.

--
GD


Dennis said:
DLOOKUP returns a NULL if nothing is found that matches.

GD said:
I'm trying to make areas of my form visible, based on a check box, along with
a dollar amount limit that each user will have. How is my code wrong?
Nothing appears when I click the check box. Also does the code go in both
the AfterUpdate & FormCurrent?

chkPaybackApproved is check box
txtVPRA is text box containing the form's $ amount
tblUsers is table containing everyone's approval limit
UserID of that table should coincide with CurrentUser(), thus determining
the limit allowed

Private Sub chkPaybackApproved_AfterUpdate()

Me.cboApprBy.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.txtApprDate.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.lblPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.cboPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I'm trying to make areas of my form visible, based on a check box, along with
a dollar amount limit that each user will have. How is my code wrong?
Nothing appears when I click the check box. Also does the code go in both
the AfterUpdate & FormCurrent?

chkPaybackApproved is check box
txtVPRA is text box containing the form's $ amount
tblUsers is table containing everyone's approval limit
UserID of that table should coincide with CurrentUser(), thus determining
the limit allowed

Private Sub chkPaybackApproved_AfterUpdate()

Me.cboApprBy.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.txtApprDate.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.lblPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.cboPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))

End Sub
Click to expand...

Sometimes DLookUp will return a text value rather than a number. Try

Val(Me.txtVPRA) < Val(DLookup("[Limit]", "tblUsers", "[UserID]='" &
CurrentUser() & "'")))
 
" Nothing appears when I click the check box" but you are using the
afterupdate event rather than the OnClick event.

Since you will probably need to account for unchecking you will need
I would drop a rectangle or a large text box over the area the fields are in
and turn that on and off since the criteria for all the fields are the same
If checked then
Me!RectangleSameColorAsForm .Visible = (Me.chkPaybackApproved And
Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
else
Me!RectangleSameColorAsForm .Visible = False
end if.


I've run the DLOOKUP on a separate area of the form, and it returns my
current approval limit, according to tblUsers. I don't think that's
the problem.

DLOOKUP returns a NULL if nothing is found that matches.

GD said:
I'm trying to make areas of my form visible, based on a check box,
along with a dollar amount limit that each user will have. How is
my code wrong? Nothing appears when I click the check box. Also
does the code go in both the AfterUpdate & FormCurrent?

chkPaybackApproved is check box
txtVPRA is text box containing the form's $ amount
tblUsers is table containing everyone's approval limit
UserID of that table should coincide with CurrentUser(), thus
determining the limit allowed

Private Sub chkPaybackApproved_AfterUpdate()

Me.cboApprBy.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.txtApprDate.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.lblPaybackReason.Visible = (Me.chkPaybackApproved And
Me.txtVPRA < DLookup("[Limit]", "tblUsers", "[UserID]='" &
CurrentUser() & "'")) Me.cboPaybackReason.Visible =
(Me.chkPaybackApproved And Me.txtVPRA < DLookup("[Limit]",
"tblUsers", "[UserID]='" & CurrentUser() & "'"))

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks, John. That seems to work, except one thing. When I open the form
the area I wish to remain hidden until the check is selected is unhidden. I
have to check, then uncheck the box for it to hide.

Is Mike right? Should I be using On Click, instead of After Update? And I
need the same code in Form Current, right?
--
GD


John W. Vinson said:
I'm trying to make areas of my form visible, based on a check box, along with
a dollar amount limit that each user will have. How is my code wrong?
Nothing appears when I click the check box. Also does the code go in both
the AfterUpdate & FormCurrent?

chkPaybackApproved is check box
txtVPRA is text box containing the form's $ amount
tblUsers is table containing everyone's approval limit
UserID of that table should coincide with CurrentUser(), thus determining
the limit allowed

Private Sub chkPaybackApproved_AfterUpdate()

Me.cboApprBy.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.txtApprDate.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.lblPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))
Me.cboPaybackReason.Visible = (Me.chkPaybackApproved And Me.txtVPRA <
DLookup("[Limit]", "tblUsers", "[UserID]='" & CurrentUser() & "'"))

End Sub
Click to expand...

Sometimes DLookUp will return a text value rather than a number. Try

Val(Me.txtVPRA) < Val(DLookup("[Limit]", "tblUsers", "[UserID]='" &
CurrentUser() & "'")))
Click to expand...
 
Thanks, John. That seems to work, except one thing. When I open the form
the area I wish to remain hidden until the check is selected is unhidden. I
have to check, then uncheck the box for it to hide.

Is Mike right? Should I be using On Click, instead of After Update? And I
need the same code in Form Current, right?
Click to expand...

Certainly the AfterUpdate event of the checkbox (not its Click event); and
yes, you'll also need to use Current.
 
Back
Top