Returning data from table to text box based on user log on details

[smash]

OK - newbie alert!!

So far in my relatively short Access career i have managed to get by finding bits of code and hackign it around for my own purposes - thing is I can't necesarily write what I want but I can often understand what a finished code does when it's laid out in front of me if that makes sense! Kind of a "Oh I get it" thing.

Anyways, the problem I have at the moment is on a form. It has a function which returns the user log on details to a text box (named pid) via Dev Ashish's fosUser function code. What I need is for another box on the form (named expectation) to be populated by matching this user name to the "PID" column listed in tblstaff and returning the corresponding value in the "adjustment" column. I've got comboboxes working on other forms but I'm guessing this is going to be a dlookup function? Sounds simple and it must be but I'm stumped. Someone's told me i don't need code for this and it's simply a matter of using an underlying query on the control in question to return results based on the value in another control.

I've googled this to death but can't get anything that seems to help me out.

Could really do with a steer on this as I'm stuck - be gentle!

[ssanfu]

Welcome to the forum

You can use the DLookup() function. Or in the code that gets the user name, add code that creates a query to get the value from the "adjustment" field.
So the code might look like: (warning - untested - air code)

'need a reference set to Microsoft DAO 3.6 Object Library
Dim rs as DAO.Recordset
Dim sSQL as string

sSQL  = "SELECT tblstaff.adjustment FROM tblstaff WHERE "pid = '" & Me.pid & "'"

Set rs = Currentdb.OpenRecordset(ssql)
'check for records
If Not rs.BOF and Not rs.EOF Then
    Me.expectation = rs.("adjustment")
End if

'clean up
rs.close
Set rs = nothing

And I think the dlookup would look like:

Me.expectation = DLookup("adjustment", "tblstaff", "pid = '" & Me.PID & "'")

[smash]

Thanks for response Steve (sorry you've waited so long!).

I cannot get this to work at all - I've tried putting the dlookup in afterupdate event of text box and all sorts - I don't even seem to be able to get the adjustment figure pulled back even when I actually enter a value for the pid ratehr than referenceing the text box. I can't work out why this is so difficult! The form does take a couple of seconds to pull back the pid via the fOSuser function module code but even with dlookup in the after update event of that text box I still can't get the expectation text box to populate. Argh!!

[smash]

Sorry - that was a useless post wasn't it - just to be clear the pid text box content is =fOSUserName() and then expectation text box needs to pull back the "adjustment" field of stafftbl based on fOSUserName result (which is a 7 digit number)

surely fOSUSerName can be used in the dlookup function along the lines of pid = fOSUserName?

[ssanfu]

If "pid" is a number, don't use delimiters. Delimiters are only needed for text or dates
The code would be:

'need a reference set to Microsoft DAO 3.6 Object Library 
Dim rs as DAO.Recordset 
Dim sSQL as string  

sSQL  = "SELECT tblstaff.adjustment FROM tblstaff WHERE "pid = " & Me.pid
Set rs = Currentdb.OpenRecordset(ssql) 
'check for records 
If Not rs.BOF and Not rs.EOF Then
     Me.expectation = rs.("adjustment") 
End if  

'clean up 
rs.close 
Set rs = nothing

The DLookup() would be:

Me.expectation = DLookup("adjustment", "tblstaff", "pid = " & Me.PID )

[smash]

Hi Steve - many thanks - finally got dlookup working. Not sure why but I put the =fOSUserName to bring in the function result directly in the textbox rather than default value. Once I put it as default value the Dlookup started playing ball! Many thanks

[ssanfu]

Great!

FYI, the default value property only affects new records, which is why the text box value wouldn't change (update) when an older record was modified.