assigning people for different functions (1 Viewer)

[rainbows]

i have a table with every office employee on it and in it i have a field called codes

in my sales form i have a dropdown box that selects the people who i have assigned a " 1" to
i also have a dropdown box for purchasing of which i have assigned a " 2" to
the same as above for finance but i have assigned a "3" to
all works ok

but the problem i have is. lets say 1 want to use the same person to do any other the 2 or 3 things how could i do that in my table
or would i have to 3 tables for each function with thier names in each on and ref that table

thanks
steve

 

[plog]

Throw away all form talk if the question is about data, you only conflate the issue. Even better, when talking about data, demonstrate your issue with a tablular data set we can actually get our minds around.

As it is, the only thing I understand is that you want to assign multiple numbers to people for some reason. The solution to your situation depends on if those numbers are foreign keys to another table (like Departments). I don't know if that's true, so I am going to give you the simplest solution. You create a new table that holds the Employee primary key and the number assigned:

tblNumberAssignments
na_ID, autonumber, primary key
ID_Employee, number, foreign key back to Employee table
na_Number, number, the number you are assigning that employee

You just fill that table up with the employee id and the numbers you want to assign them--a new record for each number assignment. Again, though this gets a little more complicated if the number you are assigning relates to a whole diferent table.

If this doesn't help, demonstrate your issue with data.

 

[theDBguy]

What do those assignments signify? Are they individual tasks?

 

[rainbows]

this is the table with the people in it i have assigned a code to to see if can get it to work rather than create 3 tables to ref them employees

this sales form has a drop down box that at present just looks at list box with thier names in it as does the second form and third form which is poor really

i form is the sales form asking for the sales person name another form is for purchasing form asking for the buyers name and so on with other forms can i get it all one table so each form just looks at that table to highlight who has permission to sale, buy , accounts etc but some of the same people could do all 3 tasks

drop down box for buyer

order acknowledge sales person couod be the same or different people



hope this helps

steve

 

[gemma-the-husky]

There are a several of approaches. One way is to use a mvf so you can store all the different options. That is an access only solution. You can build this functionality yourself quite easily, but you need a second table.

The other option is more complex. You put your users in user groups, and assign the functions to members of user groups. it's a better solution if you have a lot of users. You take on a new member of staff, put him in "general admin", and "purchasing", and that gives him.all the functions those users get.

 

[The_Doc_Man]

Since Access long ago dropped "user-level security" from its supported features, you have to make your forms sensitive to the user's roles OR permissions. Or both. You start with a list of roles and a separate list of users. Now create a junction table that has the user ID and the role ID. (And possibly nothing else.) This junction table is the link between multiple people and multiple roles. You can join the three tables to form a query where you can filter for a person and see their roles, or for a role and see who has it. From there you have to make every form sensitive to ROLE, not to PERSON. Odds are you will have fewer roles than people so this is actually simpler.

 

[rainbows]

i did try this but it just shows me numbers not names

query in dropdown box shows this

but the drop box shows numbers not names

so dont know how to get the names

thanks steve

 

[Pat Hartman]

If you use the wizard to build the combo, this is usually taken care for you if you understand how to answer the questions. To build a combo manually or modify it after it is built, there are several important parts you need to understand.

Data Tab
1. RowSourceType - this can be one of three options - a Table/Query (most frequent and what you need), a Value list (you hard code the values you want as the choices - limited in its usefulness to a small number of items), a Field List (little used option that gives you the list of fields in a table or query. See next item)
2. RowSource - respectively the name of the TableQuery, the value list, or the name of the table or query for which you want the field list.
3. Bound Column - This is the value that will be saved to the table the control is bound to (the ControlSource field) if it is bound. Defaults to 1 but can be any column of the RowSource. It is the unique identifier. Usually the primary key of a table. Combos only work correctly if the RowSource, how ever it is made, has one single field that uniquely identifies a row.
4. Limit to List - defaults to no but change it to use yes. This prevents the user from entering an item that is not in the list.
5. AllowValueListEdits - defaults to Yes. safer to use no to prevent users from adding values to lists on the fly.

Format Tab
1. Column Count - the number of columns in the RowSource
2. Column Widths - This allows you to show or hide columns of the RowSource. Usually when the Bound Column is the PK of a table, we don't want to show this value, instead we want to show a text value. So, set the first column width to 0, then use a positive number for any subsequent fields. When the list is dropped down, you will see all the columns that have a non-0 width. When the list closes, the textbox will show ONLY the first column with a non-0 width.

There are a number of other useful properties that you might want to familiarize yourself with. but #2 on the format Tab is how you solve your problem

 

[rainbows]

i have tried to change the data as you suggested but i only get numbers as shown below

i even tried the wizard to do it but again it only shows me numbers

thanks for your help

steve

 

[Gasman]

 

[arnelgp]

give some sample db.
only you know your tables and relations.

 

[Pat Hartman]

We can't see the RowSource query so I can't tell what you have selected as the second column. Looks like it is a numeric field. Maybe you selected the ID twice instead of the ID and the name.

Gasman showed you a picture from his application. The first column width is 0, which i took great care to explain to you. That hides the first column. Then each subsequent column needs an appropriate width so it is not truncated in the drop down. Look again at the Format Tab/ 2. Column Widths description.

 

[rainbows]

it undone it all and started again and yes there was errors, ie i did use a numeric field (stupid mistake) and wen i followed Gasman and Pats instructions all worked great

i want to thank you all for helping me

steve

 

[Pat Hartman]

You're welcome You might want to print out my explanation of the arguments you always need to set when making a combo or listbox your self.

 

[rainbows]

hi Pat , i already have but i have just been doing the reports for sales and purchasing and hit a problem
see table with all the names in and the rows ID in it. so in my combo boxes , i ust the ID to ref the to that form /reports and each list shows as required to select from

now please see the second screen shot these shows the names under ID no 1 which is correct and other areas like purchasing are also ok

now becomes the problem please screen shot no 3 it is giving me 3 records. i think this is because we have used Rauf 3 times for other areas's if i remove rauf from on of the other lists. is will give me 2 records on the report

SELECT IIf([Schedule date] Is Null,[Forms]![sales orders]![NavigationSubform].[Form]![scheduled date],[Schedule date]) AS [date], [Order Details].Sellprice, [Acknwoledge order].id, [Order Details].BatchNo, Orders.[Exchange rate2], [Acknwoledge order].[scheduled date], [buyers name].Tittle, Customers.Currency, [Acknwoledge order].Terms, Orders.CustomerName, [Acknwoledge order].[Exchange Rate 1], [Acknwoledge order].[Sales person], [Acknwoledge order].intcoterms, [FirstName] & " " & [lastname] AS fullname, [Order Details].[Schedule date], [buyers name].[e-mail], Customers.FirstName, [Order Details].OrderQty, [Order Details].Required, Orders.OrderNo, Orders.NCONo, Orders.Received, [Order Details].ProductNo, Products.Pdescription, [Order Details].Price, [Order Details].[Exchange Rate], [price]/[exchange rate2] AS [Unit price], Products.Pdescription, Customers.[E-Mail address]
FROM [buyers name] INNER JOIN (([Acknwoledge order] INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerName) INNER JOIN [Order Details] ON (Orders.OrderID = [Order Details].OrderID) AND (Orders.OrderID = [Order Details].OrderID) AND (Orders.OrderID = [Order Details].OrderID)) ON [Acknwoledge order].[NCO No] = Orders.NCONo) LEFT JOIN Products ON [Order Details].ProductNo = Products.ProductID) ON [buyers name].[buyers name].Value = [Acknwoledge order].[Sales person]
WHERE ((([Acknwoledge order].id)=[Forms]![sales orders]![NavigationSubform].[Form]![id]));

 

[Pat Hartman]

1. You do not have RI enforced anywhere.
2. You are probably using multi-value fields and that is what is causing the problem.
3. In the future, please start new thread for new question.