Fosusername()

[Guest]

I have a form(frmDetAccount) with a subform. The Main form's row source
comes from a query which contains emplyoee information. The subform contains
account information whose row source comes from a table. The link between
the 2 tables is employeeID.
I have a table named tblLoan which only contains loan number, date, and a
question(does it require a detailed account information). frmLoan is used to
enter the loan information. If the user says yes, the frmDetAccount is
opened and the employee enters more information about that Account. When
opening the frmDetAccount, the loan number is copied to a field in the
subform.

When I open the frmDetAccount directly, its able to capture only the
accounts I have entered in the database, which is what I want. But when I
use the tblLoan to open the frmDetAccount, the EmployeeID column is left
blank.

So the probably what I should ask is how comes its not detecting me and how
should I take care of that?

The following is the structure of the tables I have:

tblEmployee(EmployeeID, UserID, EmployeeName)
tblDetAccount(DetAccountID, BorrowerName,DetAccountDate, EmployeeID, etc)
tblLoan(LoanID, LoanNumber, EmployeeID,LoanDate)

I have the fosusername() function from the following site,
http://www.mvps.org/access/api/api0008.htm

 

[Douglas J. Steele]

How are you using fOSUserName?

Typically, you'd need to explicitly set the EmployeeID column in the form's
BeforeUpdate event.

 

[Barry Gilbert]

I infer from your post that the issue relates to picking up the network
user name and pasting it into the EmployeeId field. Is this correct?

Does this function work on some users, but not you? Do you get a result
from fosusername directly? Are you logged into your network?

 

[Guest]

Barry, Well I think I might be having design or opening the form issues.
Because if I open the frmDetAccount, directly, it recognizes me, but when i
open the frmDetAccount using the frmLoan, the form does not recognize
me...... I am developing this db and testing it and this is the unit that is
not working well for me...

 

[Guest]

The following is the sql statement I have for frmDetAccount
SELECT employee.UserID, employee.* FROM employee WHERE
(((employee.UserID)=fosusername()));

the code that I have on my loan form that opens the frmDetAccount is as
follows..

Private Sub RqdDetailAcct_AfterUpdate()
Dim frm As String

frm = "frmDetAccounts"

If Me.RqdDetailAcct.Column(0) = "yes" Then
DoCmd.OpenForm frm, , , , acFormAdd,
Forms!frmDetAccounts!frmDetAcctSubform!LoanNum = Me!LoanNum
ElseIf Me.RqdDetailAcct.Column(0) = "No" Then
DoCmd.GoToRecord , , acNext
Me.LoanNum.SetFocus
End If

End Sub

 

[Douglas J. Steele]

I was more concerned about how you were populating the UserID field in the
table.

 

[Guest]

Doug, the userID is the employee's LAN's login that they use

 

[Barry Gilbert]

I don't know if this is related to your problem, but the reference to
the subform is missing a .Form property. It should read:
Forms!frmDetAccounts!frmDetAcctSubform.Form!LoanNum = Me!LoanNum

Barry

 

[Guest]

Barry, thanks, but my problem is still pending.....

 

[Douglas J. Steele]

I realize that. How are you ensuing that the Id field is being populated
with whatever value is being returned by fOSUserName?

 

[Guest]

Doug, am not sure i understand the question, but I will try to answer it....

The Employee table contains the employee names, userID etc. So
Employee name = susan Smith,
UserID = x123456z

tblDetAccount(DetAccountID, BorrowerName,DetAccountDate, EmployeeID etc)
where employeeId = FK, row source is SELECT employee.EmployeeID,
employee!EmplLastName & " " & employee!EmplFirstName AS EName FROM employee
ORDER BY employee!EmplLastName & " " & employee!EmplFirstName;

 

[Douglas J. Steele]

How are you populating the field in the table?

You're showing me how you're retrieving it once it's in the table.

Your original complaint is that EmployeeID is showing up as blank. That
implies to me that you're not getting the value of the function into it.

Let me take a guess. Have you set the control source for a text box on your
form to =fOSUserName? If so, that value is not going to get back into the
table. As I said earlier, you need to put code into the form's BeforeInsert
event. Have you?

 

[Guest]

Doug,

Looks like am not getting the value as needed, I do have employee
information on my form including the UserID field, I have set my form to be
SELECT employee.UserID, employee.* FROM employee WHERE
(((employee.UserID)=fosusername()));
To say the truth I am lost completely, What should I add to my beforeupdate
procedure?

 

[Douglas J Steele]

You should add code that sets UserID to the value:

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.UserID = fOSUserName()

End Sub

 

[Guest]

Doug I think there is something wrong I have done somehwere, there are 2 ways
to open frmDetAccount, either open it directly (which by the way works
great), or when a user select yes from a different combobox then the form
opens. this is where the problem is....

Is it possible if I could send you what am talking about maybe there would
be a better picture.....

 

[Douglas J Steele]

Sorry, no. I don't want you to send me your database.

Often the process of describing in detail what the setup will help you to
discover the problem on your own.

 

[Guest]

Thanks Doug, I will try to describe in more dtail what my db is doing, maybe
i was not clear/detailed enough.....

there are 3 tables, tblLoan,tblEmployee,tblDetAccount
tblLoan=LoanID,LoanNumber,LoanQuestion,EmployeeID
tblEmployee=EmployeeID, EmployeeName,UserID
tblDetAccount=DetAcctID, BorrowerName,BorrowerDate,EmployeeID

using a wizard to create the form and subform for frmDetAccount
Mainform rowsource I have it as SELECT employee.UserID, employee.* FROM
employee WHERE
(((employee.UserID)=fosusername()));
The subform -frmDetAcctSubform
rowsource information contains
DetAcctID, BorrowerName,BorrowerDate,EmployeeID (where DetAcctID and
EmployeeID are hidden)
When i open this form directly, I am ale to see only my stuff and when i add
stuff into it, it saves under me.

Problem is with the frmLoan which contains
LoanNumber,LoanQuestion
So the purpose of the frmloan is supposed to open the frmDetAccount and
require me to enter more information about the accout.

The loanQuestion is of type yes/no and contains the following information
Private Sub RqdDetailAcct_AfterUpdate()
Dim frm As String

frm = "frmDetAccounts"

If Me.RqdDetailAcct.Column(0) = "yes" Then
DoCmd.OpenForm frm, , , , acFormAdd,
Forms!frmDetAccounts!frmDetAcctSubform!LoanNum = Me!LoanNum
ElseIf Me.RqdDetailAcct.Column(0) = "No" Then
DoCmd.GoToRecord , , acNext
Me.LoanNum.SetFocus
End If

End Sub

So with this code, I able to open the frmDetAccount, it pastes the
loannumber where its supposed to be but, the form does not identify me as the
person entering the information....
So when i add the data, and go back to the tables, the information does
not have an employee attached to it....

 

[Douglas J Steele]

What fields are specified for the Link Child Fields and Link Master Fields
between the form and subform?

I'm actually surprised that you're not getting an error when you open the
form from frmLoan. The reference to the LoadNum on the subform should
actually be

Forms!frmDetAccounts!frmDetAcctSubform.Form!LoanNum

 

[Guest]

The link fields for both is employeeID

***the paragraph with my tables contains the names they are supposed to
mean, I wrote them out completely to make sense.....
so for Loannumber for real its LoanNum, LoanQuestion is RqdDetailAcct

 

[Douglas J Steele]

Have you put the code into the BeforeInsert event of any of the forms?