Use CurrentUser to look up ID

[Guest]

I know this should be easy enought but I can't seem to locate anything in the
discussion groups or make it work. I have a combo box that looks up "users"
from a table to be applied to a record. I would like to make the default
"user" populate the field based on their login via CurrentUser(). How do I
look up the ID in the associated table to populate the field and still allow
the combobox lookup to display the full listing to be able to reassign the
record to another "user"?

Thanks for your assistance.

 

[Guest]

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

 

[Guest]

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

 

[Guest]

This code grabs user information from Access' security model. The only other
value associated with a user in this model is the PID (personal identifier),
which can be a string as well. If this is the number you want, you can't get
there with DLookup, but you can get it when you populate the combobox:
Change your code to this:
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";" & usrNew.PID
& ";"

You'll have to change the ColumnCount property of the combobox to 2 and you
might want to hide the column with the ColumnWidths property (1", 0"). If
you also set the BoundColumn property to 2, you can access this value when
you refer to the combobox.

If you're not referring to the security model when you pick up the user name
(maybe you have a table with user id's?), the code I gave you would be very
different. Is this the case?

Barry

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

 

[Guest]

I am using the security model and also storing the user information in a
table (Users), primary key being numeric (UserID). That is why I was trying
to link CurrentUser() with the User table to retrieve the UserID for saving
in another table, populate the field with the current user when a new record
is created and still allow a lookup.

Terri

This code grabs user information from Access' security model. The only other
value associated with a user in this model is the PID (personal identifier),
which can be a string as well. If this is the number you want, you can't get
there with DLookup, but you can get it when you populate the combobox:
Change your code to this:
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";" & usrNew.PID
& ";"

You'll have to change the ColumnCount property of the combobox to 2 and you
might want to hide the column with the ColumnWidths property (1", 0"). If
you also set the BoundColumn property to 2, you can access this value when
you refer to the combobox.

If you're not referring to the security model when you pick up the user name
(maybe you have a table with user id's?), the code I gave you would be very
different. Is this the case?

Barry

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

:

I know this should be easy enought but I can't seem to locate anything in the
discussion groups or make it work. I have a combo box that looks up "users"
from a table to be applied to a record. I would like to make the default
"user" populate the field based on their login via CurrentUser(). How do I
look up the ID in the associated table to populate the field and still allow
the combobox lookup to display the full listing to be able to reassign the
record to another "user"?

Thanks for your assistance.

 

[Guest]

Ah, I understand.
In that case, try this:

Dim varX As Variant
varX = DLookup("[UserId]", "Users", "[UserLogin] = '" &
Forms![Tickets]![Tickets.AssignedTo] & "'")

You had it backwards.

Barry

I am using the security model and also storing the user information in a
table (Users), primary key being numeric (UserID). That is why I was trying
to link CurrentUser() with the User table to retrieve the UserID for saving
in another table, populate the field with the current user when a new record
is created and still allow a lookup.

Terri

This code grabs user information from Access' security model. The only other
value associated with a user in this model is the PID (personal identifier),
which can be a string as well. If this is the number you want, you can't get
there with DLookup, but you can get it when you populate the combobox:
Change your code to this:
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";" & usrNew.PID
& ";"

You'll have to change the ColumnCount property of the combobox to 2 and you
might want to hide the column with the ColumnWidths property (1", 0"). If
you also set the BoundColumn property to 2, you can access this value when
you refer to the combobox.

If you're not referring to the security model when you pick up the user name
(maybe you have a table with user id's?), the code I gave you would be very
different. Is this the case?

Barry

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

:

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

:

I know this should be easy enought but I can't seem to locate anything in the
discussion groups or make it work. I have a combo box that looks up "users"
from a table to be applied to a record. I would like to make the default
"user" populate the field based on their login via CurrentUser(). How do I
look up the ID in the associated table to populate the field and still allow
the combobox lookup to display the full listing to be able to reassign the
record to another "user"?

Thanks for your assistance.

 

[Guest]

Hi Barry,

I am sorry to keep bothering you but I am still having problems with this.
Could I not just use something like

= DLookup("[Users.UserID]", "Users", "[Users.UserLogin] = " & CurrentUser())

as the Default Value of the combo box?

Terri

Ah, I understand.
In that case, try this:

Dim varX As Variant
varX = DLookup("[UserId]", "Users", "[UserLogin] = '" &
Forms![Tickets]![Tickets.AssignedTo] & "'")

You had it backwards.

Barry

I am using the security model and also storing the user information in a
table (Users), primary key being numeric (UserID). That is why I was trying
to link CurrentUser() with the User table to retrieve the UserID for saving
in another table, populate the field with the current user when a new record
is created and still allow a lookup.

Terri

This code grabs user information from Access' security model. The only other
value associated with a user in this model is the PID (personal identifier),
which can be a string as well. If this is the number you want, you can't get
there with DLookup, but you can get it when you populate the combobox:
Change your code to this:
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";" & usrNew.PID
& ";"

You'll have to change the ColumnCount property of the combobox to 2 and you
might want to hide the column with the ColumnWidths property (1", 0"). If
you also set the BoundColumn property to 2, you can access this value when
you refer to the combobox.

If you're not referring to the security model when you pick up the user name
(maybe you have a table with user id's?), the code I gave you would be very
different. Is this the case?

Barry

:

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

:

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

:

I know this should be easy enought but I can't seem to locate anything in the
discussion groups or make it work. I have a combo box that looks up "users"
from a table to be applied to a record. I would like to make the default
"user" populate the field based on their login via CurrentUser(). How do I
look up the ID in the associated table to populate the field and still allow
the combobox lookup to display the full listing to be able to reassign the
record to another "user"?

Thanks for your assistance.

 

[Guest]

No bother.
You can use it as the default value in a combobox, but your example won't
work. It should be:
= DLookup("[UserID]", "Users", "[UserLogin] = '" & CurrentUser() & "'")

Barry

Hi Barry,

I am sorry to keep bothering you but I am still having problems with this.
Could I not just use something like

= DLookup("[Users.UserID]", "Users", "[Users.UserLogin] = " & CurrentUser())

as the Default Value of the combo box?

Terri

Ah, I understand.
In that case, try this:

Dim varX As Variant
varX = DLookup("[UserId]", "Users", "[UserLogin] = '" &
Forms![Tickets]![Tickets.AssignedTo] & "'")

You had it backwards.

Barry

I am using the security model and also storing the user information in a
table (Users), primary key being numeric (UserID). That is why I was trying
to link CurrentUser() with the User table to retrieve the UserID for saving
in another table, populate the field with the current user when a new record
is created and still allow a lookup.

Terri

:

This code grabs user information from Access' security model. The only other
value associated with a user in this model is the PID (personal identifier),
which can be a string as well. If this is the number you want, you can't get
there with DLookup, but you can get it when you populate the combobox:
Change your code to this:
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";" & usrNew.PID
& ";"

You'll have to change the ColumnCount property of the combobox to 2 and you
might want to hide the column with the ColumnWidths property (1", 0"). If
you also set the BoundColumn property to 2, you can access this value when
you refer to the combobox.

If you're not referring to the security model when you pick up the user name
(maybe you have a table with user id's?), the code I gave you would be very
different. Is this the case?

Barry

:

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

:

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

:

I know this should be easy enought but I can't seem to locate anything in the
discussion groups or make it work. I have a combo box that looks up "users"
from a table to be applied to a record. I would like to make the default
"user" populate the field based on their login via CurrentUser(). How do I
look up the ID in the associated table to populate the field and still allow
the combobox lookup to display the full listing to be able to reassign the
record to another "user"?

Thanks for your assistance.

 

[Guest]

That is exactly what I was looking for. Thank you so much!

No bother.
You can use it as the default value in a combobox, but your example won't
work. It should be:
= DLookup("[UserID]", "Users", "[UserLogin] = '" & CurrentUser() & "'")

Barry

Hi Barry,

I am sorry to keep bothering you but I am still having problems with this.
Could I not just use something like

= DLookup("[Users.UserID]", "Users", "[Users.UserLogin] = " & CurrentUser())

as the Default Value of the combo box?

Terri

Ah, I understand.
In that case, try this:

Dim varX As Variant
varX = DLookup("[UserId]", "Users", "[UserLogin] = '" &
Forms![Tickets]![Tickets.AssignedTo] & "'")

You had it backwards.

Barry

:

I am using the security model and also storing the user information in a
table (Users), primary key being numeric (UserID). That is why I was trying
to link CurrentUser() with the User table to retrieve the UserID for saving
in another table, populate the field with the current user when a new record
is created and still allow a lookup.

Terri

:

This code grabs user information from Access' security model. The only other
value associated with a user in this model is the PID (personal identifier),
which can be a string as well. If this is the number you want, you can't get
there with DLookup, but you can get it when you populate the combobox:
Change your code to this:
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";" & usrNew.PID
& ";"

You'll have to change the ColumnCount property of the combobox to 2 and you
might want to hide the column with the ColumnWidths property (1", 0"). If
you also set the BoundColumn property to 2, you can access this value when
you refer to the combobox.

If you're not referring to the security model when you pick up the user name
(maybe you have a table with user id's?), the code I gave you would be very
different. Is this the case?

Barry

:

Barry, thanks for your help in populating the list through code although I
still have an issue. I am a novice in programming and I guess the main
problem that I have is that the lookup is a text field but the field in my
other table stores the numeric ID for the "user" which is what I am trying to
access. It would appear that I need to use dlookup to retrieve this although
I am unclear how. I was trying to work with something like this...

Dim varX As Variant
varX = DLookup("[UserLogin]", "Users", "[UserID] = " _
& Forms![Tickets]![Tickets.AssignedTo])

:

You could use the Default Value property of the combobox to set the value. Put
=CurrentUser()
in this property.

To fill the combo, use:
Dim wrkDefault As Workspace
Dim usrNew As User
Set wrkDefault = DBEngine.Workspaces(0)
With wrkDefault
For Each usrNew In .Users
Me.MyCombo.RowSource = Me.MyCombo.RowSource & usrNew.Name & ";"
Next
End With
Set wrkDefault = Nothing

You'll need to set the combobox's RowSourceType property to Value List.

Barry

:

I know this should be easy enought but I can't seem to locate anything in the
discussion groups or make it work. I have a combo box that looks up "users"
from a table to be applied to a record. I would like to make the default
"user" populate the field based on their login via CurrentUser(). How do I
look up the ID in the associated table to populate the field and still allow
the combobox lookup to display the full listing to be able to reassign the
record to another "user"?

Thanks for your assistance.