Prev: How to get data out of Access MDE files?
Next: IP-based viewer for any access mdb database
From: Patrick A on 23 Apr 2010 17:35
All,

I have a formless db with a table containing a myLoginID column.

I would like to use the currently logged-in user's ID as the default
value for said column when my (outside) VB app writes a record to the
DB.

I would like to avoid doing this in VB code if possible.

I understand Access did not used to support the use of a custom
function a Default Value - is that still true?

If so, is there another way to do this?

If not, what am I doing wrong? My function code is below.

I can't save the table when I use UserName() as the default.

Thanks,

Patrick

Function UserName() 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
UserName = Left$(strUserName, lngLen - 1)
Else
UserName = vbNullString
End If
End Function

If not,
From: Salad on 23 Apr 2010 19:19
Patrick A wrote:

> All,
>
> I have a formless db with a table containing a myLoginID column.
>
> I would like to use the currently logged-in user's ID as the default
> value for said column when my (outside) VB app writes a record to the
> DB.
>
> I would like to avoid doing this in VB code if possible.
>
> I understand Access did not used to support the use of a custom
> function a Default Value - is that still true?
>
> If so, is there another way to do this?
>
> If not, what am I doing wrong? My function code is below.
>
> I can't save the table when I use UserName() as the default.
>
> Thanks,
>
> Patrick
>
> Function UserName() 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
> UserName = Left$(strUserName, lngLen - 1)
> Else
> UserName = vbNullString
> End If
> End Function
>
> If not,

I created a code module and entered the following BEFORE the function,
at the top of the code module. Did you compile your app?

Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

See http://www.mvps.org/access/api/api0008.htm

From: Patrick A on 26 Apr 2010 18:32
Salad,

Thanks, yes, I did. The function returns the username to a Query, I
just can't get it to return the username as a default in a table.

Here's my compelte code, which I compiled...

Option Compare Database
Option Explicit
Public Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As
Long

Function UserName() 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
UserName = Left$(strUserName, lngLen - 1)
Else
UserName = vbNullString
End If
End Function

On Apr 23, 7:19 pm, Salad <sa...(a)oilandvinegar.com> wrote:
> Patrick A wrote:
> > All,
>
> > I have a formless db with a table containing a myLoginID column.
>
> > I would like to use the currently logged-in user's ID as the default
> > value for said column when my (outside) VB app writes a record to the
> > DB.
>
> > I would like to avoid doing this in VB code if possible.
>
> > I understand Access did not used to support the use of a custom
> > function a Default Value - is that still true?
>
> > If so, is there another way to do this?
>
> > If not, what am I doing wrong?  My function code is below.
>
> > I can't save the table when I use UserName() as the default.
>
> > Thanks,
>
> > Patrick
>
> >     Function UserName() 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
> >             UserName = Left$(strUserName, lngLen - 1)
> >         Else
> >             UserName = vbNullString
> >         End If
> >     End Function
>
> > If not,
>
> I created a code module and entered the following BEFORE the function,
> at the top of the code module.  Did you compile your app?
>
> Option Compare Database
> Option Explicit
> Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
>      "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
>
> Seehttp://www.mvps.org/access/api/api0008.htm- Hide quoted text -
>
> - Show quoted text -

From: Salad on 26 Apr 2010 18:59
Patrick A wrote:
> Salad,
>
> Thanks, yes, I did. The function returns the username to a Query, I
> just can't get it to return the username as a default in a table.

Is there a need to set the default value using a UDF in the table? If
you can't use a form, use a query.

Create Table1
Create Query1 (Select F1, F2, GetUserName() AS UserN From Table1)
Use Query1 for input or as recordsource for forms/reports.

Use Query1. You can't use UDFs as the default values of a table.

From: Patrick A on 28 Apr 2010 16:46
Thanks Salad, I'll add it via the query. Just trying to avoid any
unnecessary code.

On Apr 26, 6:59 pm, Salad <sa...(a)oilandvinegar.com> wrote:
> Patrick A wrote:
> > Salad,
>
> > Thanks, yes, I did.  The function returns the username to a Query, I
> > just can't get it to return the username as  a default in a table.
>
> Is there a need to set the default value using a UDF in the table?  If
> you can't use a form, use a query.
>
> Create Table1
> Create Query1 (Select F1, F2, GetUserName() AS UserN From  Table1)
> Use Query1 for input or as recordsource for forms/reports.
>
> Use Query1.  You can't use UDFs as the default values of a table.

 | 
Pages: 1
Prev: How to get data out of Access MDE files?
Next: IP-based viewer for any access mdb database