Formatting unbound textbox for Username registration

[drybone]

Hello all,
I've implemented the fOSUserame for the purpose of populating an unbound textbox in a form (ie [forms]![form1]![text0]). I have a table (TblUsers) that has ID (PrimaryK), EDIPI (which is the match to what fOSUserName populates in text0), LastName, and FirstName. What I would like to do is build an iif statement in text2 (another unbound textbox) that looks at text0 and verifies that the EDIPI is registered in the table. If yes then load the main menu, if no prompt "You're currently not registered, please contact the DBA to gain appropriate access."

fOSUserName populates into text0 correctly and the EDIPI's are finite. how would I go about making this work. Any suggestions would be greatly appreciated. Thanks in advance.


James

 

[drybone]

Sooo, After further looking into the thread "fOSUserName with dlookup in a combobox" by pauboy, I've realized this isn't what I need. It addresses a few of the things I would need to tackle. I was looking through a DB created by a predecessor of mine that did this through a iif statement and he did it by creating a unbound textbox =fOSUserName() was it's control source. below that textbox was a second textbox with an iif statement that read something like:

iif ([text0]="tblusers.EDIPI", then [text2]= "registered", isnull then [text2]= "Unregistered")

finally, the 3rd unboundtextbox had something like this:

iff ([text2]="registered", then show command4, iff([text2]="unregistered", then goto forms!regfrm))

Now I know this statement is a hot mess, I don't have a lot of experience and I don't have access to the DB that I'm trying to recall this info from. could some please help me out with this?

 

[drybone]

Thanks for your tutelage, no sarcasm intended. I have 1 table right now:

TblUsers:
ID= AutoNumber (Primary Key)
EDIPI= Text (Electronic Data Interchange Personal Identifier)
LastName= Text (User LastName)
FirstName= Text (User FirstName)

I'm using the EDIPI instead of last and first name as user name because of my network system requirements at my job. It also avoids me using more volatile PII of my employees.

Form1 is the form I'm using the unbound textbox in:

text0 is unbound textbox that has VB fOSUserName() Function as it's event.

So when I open Form1 in form view it shows my EDIPI in text0.

I would like to write an expression in another textbox (lets say text2) that looks at the EDIPI populated into text0 and see's if it is a record in TblUsers. If it is, textbox "text2" will display "registered" and if not "unregistered".

Lastly, as a part of form1 properties:
if text2 displays "registered" then show button (command0) to proceed to main menu
if text2 displays "unregistered" open registration request form (FrmRegReq)

I'm trying to figure out how to write expressions for the things in red above.

the event procedures I'm using are working fine, I just don't know how to write expressions that well and am trying to get help on how to do it.

I hope this makes more sense. Please let me know if more clarification is needed.

James

 

[sneuberg]

An expression might be:

If DCount("*", "[TblUsers]", "[EDIPI] = '" & Me.text0 & "'") > 0 Then
   Me.Text2 = "registered"
   command0.Visible = True
Else
    Me.Text2 = "unregistered"
    'Not sure what you mean by open registration request form (FrmRegReq)
End If

But since I don't know how fOSUserName() plays into this I don't have a clue as to where you would put this. I suspect you need to incorporate it into that function.

I'm going to bed now, so I won't be able to help until tomorrow but I suggest you post the code of this fOSUserName function. I think that would help other forum members help you.

 

[drybone]

 '******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 Function FosUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        FosUserName = Left$(strUserName, lngLen - 1)
    Else
        FosUserName = vbNullString
    End If
End Function
'******************** Code End **************************

This is from Access MVP's, I have it saved as a Module and call it in the control source of Form1.text0 (unbound textbox).

I actually plugged your code into the "On Load" event. it worked great. I had to do a little more to it to get it right for the registration form but it was spot on.

 Private Sub Form_Load()
 If DCount("*", "[TblUsers]", "[EDIPI] = '" & Me.Text0 & "'") > 0 Then
   Me.Text5 = "Registered"
   Command4.Visible = True
   Command11.Visible = False
   
Else
    Me.Text5 = "Unregistered"
    Command4.Visible = False
    Command11.Visible = True
    'Not sure what you mean by open registration request form (FrmRegReq)
End If
 End Sub

Command11 will take the person requesting access to a form that I've made to request access.

 

[drybone]

Thanks to the both of you for your help. It works great now!

 

[sneuberg]

I was afraid to suggest the On Load event as I didn't think the FosUserName would return a result in time for it to work there. Glad you figured it out.