DLookup help?

Hi there - im building a contact management system and require some help. I have previously had help with VBA in terms of defining what users can and cant do within the system when logging in via a username and password which was incredibly useful. Im now at further along the route and now want to incorporate an activity system that will record all hours that employees will have worked against clients. However we have a lot of clients! The users login name('login_name' which is held in 'tblusers') is also entered into a 'leadofficer' field in 'tblcompany' so that we know which user is responsible for managing that client - this is done through a combo box on the company entry form so it looks up the values from the table 'tblusers'. On the new form i am creating for the activity entry i would like access to check the user that is logged in and only display the relevant matching companies in the company drop down combo box i.e. only those for which the user that is logged in has his/her name against them in the 'leadofficer' field in tblcompany

Can this be done

Many thanks,

Rhys.

Microsoft ISV Community Center Forums2

Sure. If you're using a database to store the information, it's fairly easy. When you're populating the company dropdown combo box, instead of using all the records in tblcompany, build an SQL SELECT query which will select only the records from tblcompany which have the appropriate login_name in their leadofficer field. If you're working in DAO, you can do something like this:

Dim rsTemp As New DAO.RecordSet
Set rsTemp = dbMyDatabase.OpenRecordSet("SELECT [company_name] FROM [tblcompany] WHERE [leadofficer] = '" & login_name & "'"

Then just populate your combobox using the records in rsTemp. Does this help

Hi duck thing - im confusing myself i think - it might be easier if i explain what i have done so far and that might help both of us!

the users table 'tblusers' stores login and password information - 'login_name' , 'password'. the log in screen does a lookup on the 'tblusers' table and if the credentials match it lets the user in - certain command buttons within the system are then enabled or disabled depending on the rights of the user via tick boxes in 'tblusers'.

In addition, the users login name 'login_name' from 'tblusers' is looked up by 'leadofficer' in 'tblcompany' to allow users to enter the correct user against a company in 'frmcompany'.

i have now created a new table called 'tblactivity' and a form called 'frmactivity' and have only added 2 fields at the moment to try and make it as simple as possible for myself - 'activityID' and 'companyname' - the 'companyname' field is a lookup field in 'tblactivity' that looks up the 'companyname' field in 'tblcompany'.

now this is where im stuck - im a novice when it comes to VBA - i can do enough to get me by but now im confused how do i tell access who is currently logged in and how do i then tell the 'frmactivity' to only display the companies accordingly! Many thanks for your help, it is very much appreciated

Rhys.

Hi Rhys,

Duck things recommendation is the way to go. As to letting Access know who is logged in you'll have to store the logged in users name somewhere in your code, like in a global variable. For example in your login screen, if the user logs in ok then store the login name of the user in the global variable. Since it's global all forms in your Access project will have access to it, so you can then use it just like duck thing recommended.

To create a global variable create a new module in your VBA project, in there type

Public login_name as String

and thats it, one global variable, available in any form. Be careful when using global variables as they can let to some interesting bugs. In your case as long as you only write to this variable once (when the users credentials are authenticated) and then only read from that variable from that point on you should be ok.

Hope thats helpful.

Hi Derek & duck thing - thanks both for your help but im still a little stuck (surprise!).

Derek, you were a huge help in helping me to enable/disable certain functions for users and at that time you recommended a global variable for the userID field so that all objects in access would be able to access it and that worked perfectly.

Ive looked at duck things code and read a bit about DAO but i am unsure as to where to enter it within Access (aplogies for being such a novice - i know it must be frustrating for you)

if i crate login_name as a global variable would duck_things code then reference that variable and compare it to the leadofficer value i.e. [leadofficer] = '" & login_name & "'" in his code example

any help would be very much appreciated

thanks guys,

Rhys.

We were all novices once.

Once you start getting into limiting data access to users then you need to have a way of marking in your database what data to display for what users. So for example if user (id = a) can only view records 1, 3, 4, 5 then you need to mark those records as being accessable to user (id = a)... which I think you have done with the [leadofficer] field. So what your looking to do is filter the records based on this field and display the results in a combo box.

There is an easier method to do this that borrows from duckthings recommendation.

A combo box in Access has a RowSource property that you can set to a query or SQL statement. In duckthing's recommendation he fills a recordset with records from the following query/SQL statement.

SELECT [company_name] FROM [tblcompany] WHERE [leadofficer] = '" & login_name & "'"

If you set the RowSource property of the combo box to this statement then the combo box will display the filtered records that match the login_name i.e. user (a). Where you do this depends on when you want the information available. It's likely you want the data available when the form first opens. So in the Form_Load event place the following...

Me.MyCombo.RowSourceType = "Table/Query"

Me.MyCombo.RowSource = "SELECT [company_name] FROM [tblCompany] WHERE [leadofficer] = '" & login_name & "'"

Where login_name is your global variable containing the user ID.

Hi Derek - this is along the lines of how i was logically trying to do it

I entered the following syntax:

Me.companyname.RowSourceType = "Table/Query"

Me.companyname.RowSource = "SELECT [companyname] FROM [tblcompany] WHERE [leadofficer] ='" & userID

because leadofficer looks up login_name and enters the userID rather than the login_name into tblcompany and userID is already set up as a global variable from the help you gave me regarding permissions previously.

however the companyname combo box just comes up as empty - removing the code brings all the companies back up for selection again. i have populated the table with values for a couple of users to test but no joy - what am i doing wrong!

Hey man,

I'm getting a bit mixed up between login_name and userID. Not sure which one your storing in the [leadofficer] field, it should be your userID as that is what your holding in your gobal variable. So I'm going to presume your storing userID.

The first thing I see is a missing & "'" at the end of the SELECT statement. I think your userID is text and the [leadofficer] field is also a text field then you need to wrap the userID in quotes, like this...

Me.companyname.RowSource = "SELECT [companyname] FROM [tblcompany] WHERE [leadofficer] ='" & userID & "'"

That should be it. As long as your [leadofficer] field is storing values that will match a userID then your sorted.

Hi Derek - probably easier if i explain the setup of the tables:

'tblusers' holds the 'userID' which is the primary key for the table and it also holds the 'login_name'.

'tblcompany' holds the company name etc and has a field called 'leadofficer', where i will insert the name of the user who will manage the company - this leadofficer field looks up the 'login_name' field from 'tblusers'. However although the user sees the name on the form it stores the 'userID' in the field 'leadofficer' in 'tblcompany' rather than the name of the user, which im assuming is how look ups work to ensure that a unique entry is always entered.

therefore the entries in the 'leadofficer field' are '1' or '2' rather than 'rhys' or 'dave' etc.

i have the 'userID' as a global variable as a result of you helping me to create it so that lookups could access it for permission levels

therefore if the value in 'leadofficer' is '1' for a dozen companies and the global variable holds my userID of 1 when im logged in could i not enter the syntax:

Me.companyname.RowSource = "SELECT [companyname] FROM [tblcompany] WHERE [leadofficer] =" & userID

or am i completely wrong (probably!)

thanks mate,

Rhys.

Yeah you've got it...

Just watch out that you filter correctly based on the data type of the [leadofficer] field. If the field is number you don't need quotes, if the field is text then you will need quotes.

If you still can't get it working I'll do you an example.

Hi Derek, cheers for that - ive changed the tables around a bit so that loginname is now the primary key and this links with leadofficer and they are both text fields. Ive set up everything as you said and when i try and enter an activity after logging in i still get what looks like a blank drop down list on the company field - however ive found that it isnt actually blank - the size of the drop down is based on the number of companies related to the user logging in - the values are there but you just cant see the text!! - i know this because selecting what seems to be blank entry will actually write the name of the selected company into the back end table, therefore it actually works but you just cant see the text when selecting the company name on the drop down - any ideas as to why this is!

Thanks mate,

Rhys.

In the query builder there is a tick box called 'show' - do you think that this is not selected when entering the query in programmatically - if so how would i enable it in the statement

Thanks,

Rhys.

Hi Rhys,

Yip I know whats happening. Check the ColumnCount and ColumnWidth properties of the combo box. If you only select one field to display in the combo box and your column count is 2 and the column width is 0 you essentially get what your experiencing.

The field your displaying is hidden and the combo box displays a list of empty values.

Edit: The reason the hidden value is still selected in the list is because of the BoundColumn property. This will be set to the index of the hidden field, although the combo box displays the blank field, the bound column property makes the combo boxes value equal to the column thats hidden.

Derek - you are a top man - changing the column widths has resolved the problem - is there anything you don't know when it comes to VBA!

Cheers buddy, until the next time!

Rhys.