UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup But With Bit Field From Table, Access 2013    
 
   
Sweetu
post Mar 4 2017, 10:45 AM
Post#1



Posts: 66
Joined: 20-September 16



Hey Guys,

I'm stuck in dlookup with TempVars & one bit(yes/No) feild one of table,Table name is "tblUser" & UserID first field for dlookup while "Allowed" is Yes/No field for second dlookup .

CODE
If DLookup("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & "' And [Allowed]= -1" & "'") Then
        MsgBox "Not Allowed", vbInformation, "Not Authorized"
Else
     'Do something
End If


Thanks for Suggestions & help
Go to the top of the page
 
cheekybuddha
post Mar 4 2017, 11:02 AM
Post#2


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Hi,

I think you have an extraneous trailing quote mark. Also, if UserID is a number, then you don't need quotes around that either. Only string (text) values need be quoted.
Try:
CODE
If DLookup("[UserID]", "tblUser", "[UserID]=" & TempVars!TempLoginID & " And [Allowed]= True") Then
    MsgBox "Not Allowed", vbInformation, "Not Authorized"
Else
    'Do something
End If


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Mar 4 2017, 11:02 AM
Post#3


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


It looks like you have an extra single quote.

If DLookup("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & "' And [Allowed]= -1" & "'") Then
MsgBox "Not Allowed", vbInformation, "Not Authorized"
Else
'Do something
End If

Try this instead:

If DLookup("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & "' And [Allowed]= -1" ) Then
MsgBox "Not Allowed", vbInformation, "Not Authorized"
Else
'Do something
End If

--------------------
Go to the top of the page
 
gemmathehusky
post Mar 4 2017, 11:04 AM
Post#4


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


what exactly does the dlookup return?
I note you aren't testing for a NULL.

Also, you may not be masking the loginid, and the yes/no filed correctly. What data types are they in the table?

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
cheekybuddha
post Mar 4 2017, 11:58 AM
Post#5


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


>> I note you aren't testing for a NULL. <<

Very well spotted!

Perhaps DCount() might be more appropriate here.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Sweetu
post Mar 5 2017, 12:22 AM
Post#6



Posts: 66
Joined: 20-September 16



I tried both as @cheekybuddha & @GroverParkGeorge suggested but not working,

@gemmathehusky , tblUser is table where I'm trying to dlookup ,UserID is text field & Allowed is bit field (Yes/No),
To get which user is logged in I set Temp-vars and using as dlookup,

CODE
Dim TempLoginID As TempVars

TempVars!TempLoginID = Me.txtUserID.Value


this for dlookup
CODE
If DLookup("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & "' And [Allowed]= -1") Then
    MsgBox "Not Allowed", vbInformation, "Not Authorized"
Else
    MsgBox "Allowed", vbInformation, "Authorized"
End If


but this is not working
Go to the top of the page
 
Sweetu
post Mar 5 2017, 12:28 AM
Post#7



Posts: 66
Joined: 20-September 16



or should I use this

CODE
    If (IsNull(DLookup("UserID", "tblUser", "UserID='" & TempVars!TempLoginID  & "'"))) Or _
    (IsNull(DLookup("Allowed", "tblUser", "Allowed=-1"))) Then



Above is working Fine Thanks guys for your Time & Advise
Go to the top of the page
 
Sweetu
post Mar 5 2017, 04:50 AM
Post#8



Posts: 66
Joined: 20-September 16



not working again
Go to the top of the page
 
cheekybuddha
post Mar 5 2017, 06:12 AM
Post#9


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Good morning Sweetu,

Since you are checking whether a record exists with the criteria you specify you would be better off using DCount():
CODE
Dim TempLoginID As TempVars

TempVars!TempLoginID = Me.txtUserID.Value
If DCount("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & "' And [Allowed]= -1") = 1 Then
    MsgBox "Allowed", vbInformation, "Not Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
    MsgBox "Not Allowed", vbInformation, "Authorized"
End If


If you want to use the DLookup() then check for the stored value of Allowed, based on the UserID:
CODE
Dim TempLoginID As TempVars

TempVars!TempLoginID = Me.txtUserID.Value
If Nz(DLookup("Allowed", "tblUser", "UserID = '" & TempVars!TempLoginID & "'"), 0) = -1 Then
    MsgBox "Allowed", vbInformation, "Not Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
    MsgBox "Not Allowed", vbInformation, "Authorized"
End If

The Nz() function will handle a null being returned.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Sweetu
post Mar 5 2017, 07:08 AM
Post#10



Posts: 66
Joined: 20-September 16



@cheekybuddha dear thanks for your input,but still not working,maybe i'm missing some reference to TempVars.

this TempVar is set on login form,while I'm trying to lookup value from table on another form's button & there I'm referencing that Tempvar.

both suggestions not working,we are using wrong refrence somewhere, however the else statement is working fine don't know why its not checking for first statement.

CODE
If DCount("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & "' And [Allowed]= -1") = 1 Then
    MsgBox "Allowed", vbInformation, "Not Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
    MsgBox "Not Allowed", vbInformation, "Authorized"
End If


CODE
If Nz(DLookup("Allowed", "tblUser", "UserID = '" & TempVars!TempLoginID & "'"), 0) = -1 Then
    MsgBox "Allowed", vbInformation, "Not Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
    MsgBox "Not Allowed", vbInformation, "Authorized"
Go to the top of the page
 
cheekybuddha
post Mar 5 2017, 07:13 AM
Post#11


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


>> both suggestions not working <<

What actually happens? Please explain exactly. Is there an error? Do you just get the wrong message?

Note, I got the messages in the above code a bit mixed up (I only changed the message, not the title blush.gif)

Try:
CODE
If DCount("[UserID]", "tblUser", "[UserID]='" & Me.txtUserID & vbNullString & "' And [Allowed]= -1") = 1 Then
    MsgBox "Allowed", vbInformation, "Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
    MsgBox "Not Allowed", vbInformation, "Not Authorized"
End If



hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Sweetu
post Mar 5 2017, 07:19 AM
Post#12



Posts: 66
Joined: 20-September 16



no errors at all,I tried dcount in your last post but nothing
Note : in my all database wherever i refer the TempVars!TemLoginID its working fine giving me the same value which users put in startup login form its holding the exact value
just when I click on the button a message box appear "not allowed" i double check the UserID which I'm login from is set to allowed but message box appear "not Allowed"
iconfused.gif

Tried again : giving solution if others can find,this is working properly werird but don't know how

CODE
If DCount("[UserID]", "tblUser", "[UserID]='" & TempVars!TempLoginID & vbNullString & "' And [Allowed]= -1") = 0 Then
    MsgBox "Allowed", vbInformation, "Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
If DCount("[UserID]", "tblUser", "[UserID]='" &TempVars!TempLoginID  & vbNullString & "' And [Allowed]= 0") = 1 Then
    MsgBox "Not Allowed", vbInformation, "Not Authorized"
End If
Go to the top of the page
 
cheekybuddha
post Mar 5 2017, 08:24 AM
Post#13


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Haha! I'm so sorry!

I didn't realise that Allowed = -1 means NOT Authorised!

I thought if Allowed = True (-1) then the user would be authorised!!

So, more simply it should work like:
CODE
If DCount("[UserID]", "tblUser", "[UserID]='" & Me.txtUserID & vbNullString & "' And [Allowed]= -1") = 1 Then
    MsgBox "Not Allowed", vbInformation, "Not Authorized"    ' A record exists with the passed UserID and has Allowed set to True
Else
    MsgBox "Allowed", vbInformation, "Authorized"
End If


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 5 2017, 08:41 AM
Post#14


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Actually, I need more coffee!

Your statement in Post#12 is confusing.

You are saying that:

If a UserID does NOT have Allowed = -1 then they ARE authorised

If a UserID has Allowed = 0 then they are NOT authorised

What happens if they DO have Allowed = -1 ?

Neither of your DCount() statements will be be true dazed.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
Sweetu
post Mar 5 2017, 12:28 PM
Post#15



Posts: 66
Joined: 20-September 16



Yeah I figured this out, thanks dear cheers.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:15 PM