• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 733
  • Last Modified:

Select value from table using fOSUserName

What is the best way to populate a bound textbox using fOSUserName. I already tried the following unsuccessfully:

Control Source was set to:
= DLookup("TeamMember", "tblSAC", "CorpId = '" & fOSUserName & "'")

Option Compare Database
 
'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 = ""
    End If
End Function

Open in new window

0
LewJak
Asked:
LewJak
  • 12
  • 12
  • 11
1 Solution
 
rockiroadsCommented:
So does CorpId represent the user's logon id?
0
 
milduraitCommented:
The control source needs to be bound to the appropriate column in the forms recordset.
I'd be looking at setting the default value property of the textbox.

Is the DLookup function returning the correct value or an error?
0
 
LewJakAuthor Commented:
Yes. CorpID is primary key of tblSAC that corresponds with user logon id captured by fOSUserName function. I want to pull TeamMember from table using CorpId.  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
milduraitCommented:
Why not store the CorpID value itself?

This will cover you if a person's name changes by reason of mis-spelling or a legal name change.
You will be able to create a joining query later if you need to associate the team-member's name with the data you are storing through this form.
0
 
LewJakAuthor Commented:
Mildurait,

DLookup function results in error.
0
 
LewJakAuthor Commented:
mildurait,

Form is currently set-up to store CorpID but TeamMember would be more meaningful to rest of team using database. Team interaction would be more cohesive & coordinated if we knew instantly who created a record without looking-up TeamMember asscociated with a particular CorpID.
0
 
rockiroadsCommented:
Possibly messing up due to null being returns. DLOOKUp returns null if no record found

you could try wrapping it with NZ
eg

= NZ(DLookup("TeamMember", "tblSAC", "CorpId = '" & fOSUserName & "'"),"No Record Found")

or maybe in code, using form_current, calld each time new record displayed

private sub form_current()
    Me.mytextbox = NZ(DLookup("TeamMember", "tblSAC", "CorpId = '" & fOSUserName & "'"),"No Record Found")
end sub

Now you can specify a default value here, you could use "" instead of "No Record Found"

Do you always expect a value to exist?

Did you verify that fOSUserName returns a value? Normally it shouldnt fail but I have similar code, and I use Environ as my backup
eg

If lngx <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = Environ("Username")
    End If



Might be worth putting a msgbox to verify the value returned by fOSUsername
0
 
milduraitCommented:
<Form is currently set-up to store CorpID but TeamMember would be more meaningful>
I would still suggest leaving it this way, but adding an unbound textbox to display the calculated value.
0
 
LewJakAuthor Commented:
mildurait,

Okay, an unbound textbox with calculated value would be great. How is it done.
0
 
milduraitCommented:
public sub Form_Current()
       me.txtTeamMember.value = getTeamMember(fOSUserName())    
end sub

public function getTeamMember(ByVal CorpID as string) as string

     dim rs as dao.recordset
     set rs = CurrentDB.OpenRecordset("SELECT TeamMember FROM tblSAC WHERE CorpID='" & CorpID & "'")
     if rs.eof = false then getTeamMember = NZ(rs.fields("TeamMember").value,"") else getTeamMember = ""
     rs.close
     set rs = nothing

End function
0
 
milduraitCommented:
a) add code above to your form
b) add a Textbox called txtTeamMember
c) make sure the control source value of your textbox has no value in it.
0
 
rockiroadsCommented:
did u try using dlookup wrapped with NZ? did u verify getting the username worked? I dont know your textbox name so called it mytextbox
0
 
LewJakAuthor Commented:
rockiroads,
fOSUserName is returning proper value & I have limited table thus far to the single corresponding CorpID.
0
 
milduraitCommented:
sorry following code needs changing

public sub Form_Current()
       me.txtTeamMember.value = getTeamMember(NZ(me.[NameOfYourCorpIDField],""))    
end sub
0
 
LewJakAuthor Commented:
mildurait,

Where should I add code in form.
0
 
rockiroadsCommented:
So question remains, did u try wrapping DLOOKUP with NZ?

What was the exact error msg you got anyway from doing DLOOKUP

silly question but these column names for the table usd are correct right? no spaces or anything in your table def right? otherwise use [] to wrap if there is
no single quotes in name as well right. dont think u can have them anyway, otherwise use chr$(34) instead of single quotes
DLookup("TeamMember", "tblSAC", "CorpId = '" & fOSUserName & "'")
0
 
milduraitCommented:
1. Open form in design view.
2. Right click the form detail selection.
3. In the combobox at the top of the dialog select form
4. In the event tab select [event procedure] from current and click the box with ... (3 dots) to the right.
5. Replace
           Private Sub Form_Current()

           End Sub
       .. with the code I have given..
0
 
LewJakAuthor Commented:
Mildurait,

Code stops at:  {Dim rs As dao.Recordset}
I get a {Compile error: User defined type not defined} after pasting the following:

Public Sub Form_Current()
Me.txtTeamMember.Value = getTeamMember(Nz(Me.[CorpID], ""))
End Sub

Public Function getTeamMember(ByVal CorpID As String) As String

Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT TeamMember FROM tblSAC WHERE CorpID='" & CorpID & "'")
If rs.EOF = False Then getTeamMember = Nz(rs.Fields("TeamMember").Value, "") Else getTeamMember = ""
rs.Close
Set rs = Nothing

End Function

0
 
milduraitCommented:
Sounds like there may be a missing reference.
In the Visual Basic Editor Select tools / References.
You will need microsoft access 12.0 access database engine object to be checked.


0
 
rockiroadsCommented:
No offence to anyone but I see no difference between using a recordset and using DLOOKUP since you are only interested in getting one column value

I posted sample DLOOKUP wrappd with NZ code in form_current much earlier on  (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24334287.html?cid=1066#a24175853) but Im guessing this wasnt used.

Got a couple of suggestions since I dont know your DLOOKUP error, and got no response since I asked you, but I think mildurat will get you there since your using his/her code (sorry mildurat, dont know your gender!)

Good luck with this, your in capable hands.
0
 
rockiroadsCommented:
ah yes mildurat, references. That was going to be one of my suggestons. I was going to suggest to rollback to original code then do a debug/compile as there should be no reason why dlookup fails bar a compilation issue elsewhere, which could be caused by "MISSING" references.

But you got it sorted, by the way, chances are you need Microsoft DAO Object Library for use of DAO.Recordsets, unless they have changd it for 2007
0
 
milduraitCommented:
Rocki, Male
DAO would expose better error descriptors (if we can get it going).
Missing reference could also be the reason DLOOKUP is failing.

0
 
milduraitCommented:
Rocki, looks like 12.0 database objects encapsulates DAO, for when I try to add, it generates a conflict.
Sounds like you and I are yet to move to 2007.
0
 
rockiroadsCommented:
I just have the portable edition, not the full blown. havent messed too much with it yet. And if you have tried it then I would agree with you.
0
 
LewJakAuthor Commented:
mildurait & rockiroads,

You both far exceed my talents. Bare with me. Your patience is appreciated.

rockiroads,

I get #Name? with the following pasted in Control Source:

=NZ(DLookUp("TeamMember","tblSAC","CorpId = '" & [fOSUserName] & "'"),"No Record Found")
0
 
rockiroadsCommented:
thats why you should use form_current

I have just re-read your question and noticed you said controlsource

Right, control source is used to identify the field that the control is based on. This field is the one from the table that the form might be on.
If unbounded, then you dont set it.

You got to set the value

See how to use this dlookup in form_current, sample posted much earlier on

0
 
rockiroadsCommented:
LewJak, you would get #Name if controlsource used incorrectly
0
 
LewJakAuthor Commented:
rockiroads & mildrait,

Got it! Sorry it took so long. The following worked. Thanks to both of you for your patience.

private sub form_current()
    Me.txtTeamMember = NZ(DLookup("TeamMember", "tblSAC", "CorpId = '" & fOSUserName & "'"),"No Record Found")
end sub
0
 
rockiroadsCommented:
Remember what I said about "No Record Found". You dont have to use No Record Found as default text but it doesnt find an entry

0
 
milduraitCommented:
LewJak, so long as you are happy with your solution all good.

Still I'd be storing the CorpID value, not the team member, and using DLOOKUP or what ever function to calculate the team member name into an unbound textbox.


private sub form_current()
    me.txtCorpID.DefaultValue = fOSUserName()
    Me.txtTeamMember = NZ(DLookup("TeamMember", "tblSAC", "CorpId = '" & me.txtCorpID & "'"),"No Record Found")
end sub

0
 
LewJakAuthor Commented:
It will take me a while to fully comprehend this exchange but you solved my problem nonetheless. It was a great first experience with this service. Thank you.
0
 
rockiroadsCommented:
LewJak, glad that both myself and mildurait were of useful assistance.

Since your new, may I suggest you also display any error messages as well. Your question was top nevetheless, I just missed out the control source bit, doh!
0
 
rockiroadsCommented:
damn cant even spell!
and I meant top explanation
with "nevertheless" spelt correctly this time! :)
0
 
LewJakAuthor Commented:
Mildurait,

I would like to use this On Dirty as oposed to On Current but, when I close record & re-open at later date, TeamMember is no longer displayed in unbound textbox.
0
 
milduraitCommented:
Lewjak,

A bit cheeky to ask me this when you have accepted Rocki's comment as the solution.  As far as I can see, my first comment as below would have been the most appropriate solution here.

<The control source needs to be bound to the appropriate column in the forms recordset.
I'd be looking at setting the default value property of the textbox.>
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now