Create this query using code instead

[mafhobb]

Hi All,

I have this query in a combo box row source to populate it:

SELECT [RD Staff].[Name] FROM [RD Staff] WHERE ((([RD Staff].[Name])= Currentuser)) ORDER BY [RD Staff].[Name];

Right now, the user has to click on the combo box for the appropriate name to show up and then he needs to click on it to select it.

I would like to change this so the name automatically appears in the in the combo box when it gets the focus, so there is no need to click on it to select the name. I think that what I need to do is to have a "before update" event on the combo box and then add the appropriate code.

I have tried this and the code above does not work. How do I create the appropriate query (using code) to do the same as the above one?

Thanks

mafhobb

 

[pbaldy]

What's the point of the query, since it's just pulling the name equal to CurrentUser()? Why not just set the combo equal to CurrentUser()? In the appropriate event:

Me.ComboName = CurrentUser()

 

[mafhobb]

Sorry, I did not give all the info...

I was actually planning to modify the code so instead of the current user ("jsmith" for example), it would actually pull the complete name (john Smith) from the table based on a the currentuser field saved on that same table. BUT! if you know of a better way of doing that, let me know.

Mafhobb

 

[Kafrin]

Do you need this to be a combo box, since you're limiting it to one record anyway?

What I'd suggest is have an unbound (or bound to wherever your combo box is bound to) text box, possibly locked, which is populated with the current user in the OnOpen of the form. Even if you need it to be a combo box, you can still populate it in the Form_OnOpen, or possibly in the combo/text box OnEnter instead.

As long as you know that your CurrentUser is in the Staff table it's fairly straightforward, just use:
Me!MyBox = CurrentUser

If there's a chance that your user won't be in the Staff table, (and you're either using a combo box limited to list or you need them to be in the table later on) you need to add a check by opening up the staff recordset and finding the current user, and only setting the box if you find a match.

-----------

Two replies while I wrote this message!
To update, you will want a combo box, with the first (bound) column hidden (width=0) and the second column showing the full name. Then follow the above.

 

[pbaldy]

Two thoughts come immediately to mind. If the combo included both fields (and no criteria) and the first was the bound column, then the line above should still work. The combo would have the CurrentUser() value but display the full name. You could also use a DLookup:


Me.ComboName = DLookup("FullName", "TableName", "NameField = '" & CurrentUser() & "'")

 

[mafhobb]

Actually, the easiest way to do this would probably have something in the Combo box "default" that would pull the full name of the user based on the user name, and still have the combo box "row source" reffer to the full name field.

This way, under most circumstances the name will already be filled out but if someone is entering information using someone else computer, he can still select his name form the list.

Can this be done? Can the default for the combo box be set to show the full name of the current user based on the username? Remember that the full name and username are both stored in a table called RD Staff

Anyone knows how to do this?

Mafhobb

 

[mafhobb]

Would this be

Me.ComboName.default = DLookup("FullName", "TableName", "NameField = '" & CurrentUser() & "'")

??

 

[mafhobb]

Done.

The following is the code that worked for me. I added this to the afterupdate event for the previous control to create sort of a pre-entered value in the combo box, however if anyone wants to change the name, the combo box is still available.

Me.[comment by] = DLookup("Name", "RD Staff", "Username = '" & CurrentUser() & "'")
MsgBox [comment by]

Thanks everyone for the help!

mafhobb