Solved

Update combobox content and table when user makes a choice from another combobox Access 2010

Posted on 2011-02-21
8
529 Views
Last Modified: 2012-05-11
I'm struggling to get my head around access. I'm trying to create a purchase order form but in trying to automate as much of it as possible I have hit a problem I could do with some help on.

The form pulls its data from the purchaseOrder table (thats all fine) and it also allows the user to select who they are from a combo box (in a related table userList). Again that works ok. The problem is I want to update the Department comboBox (its a comboBox right now, but it doesn't have to be, its just where I started from) on the form automatically when the user selects who they are. After all the information is already in the database so why should the user have to fill that in again every time they create a purchase order? The department the user belongs to is in another related table (related to the userList table)

I've tried various things but I'm just not sure how I should do this in access... any help appreciated.


Database is something like this:

purchaseOrders:

poNumber,... a whole bunch of fields ..., raisedBy
1              ,..............................................., excel support

userList:

id, lastName, firstName, jobTitle       , fullName        , department, etc etc
1 , support  , excel       , IT Support , excel support,  IT              ,

(Note: fullName is a calculated field of firstName + lastName)

departments:

id, department
1 , IT

0
Comment
Question by:excelsupport
  • 4
  • 3
8 Comments
 

Author Comment

by:excelsupport
ID: 34943815
Hmmm... the relationships aren't obvious reading that back...

purchaseOrders.raisedBy would actually be = userList.ID, userList.fullName with the ID hidden (done through the lookup wizzard if I remember correctly). Hence purchaseOrders.raisedBy = excel support

userList.department would be = departments.ID, departments.department (again the ID hidden).

I think for me some of the confusion I am suffering is the fact that the related fields (both in the table view and in the form) show the value I want rather than the ID value that I would expect. Plus querying the form fields using VB in events returns the index or ID values!
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 34943829
You appear to be showing that the user department is held in the userList table?
Is this so or have you used a lookup field in userlist?

In either case you need to modify your 'user' combo to include the department field as well.
If you have used a lookup field then you will have to add the departments table to the rowsource query of the combo so that you can include the department name in columns of the combo.

So if we assume that you now have 2 columns in your rowsource, fullname and Departmentname then you would use a line of code in the combo afterupdate event procedure to display the department on its own.

me.txtDepartment = me.usercombonname.column(1)
0
 
LVL 40

Expert Comment

by:als315
ID: 34943842
If you have domain, you can get user name from API and fill it's id automatically.
Is your DB splitted to frontend and backend?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:excelsupport
ID: 34943901
peter57r, I have tried, as a temporary measure, putting the department field in the purchaseOrders table as a lookup to the department table but it didn't feel right. I am doing a lookup or a relationship or whatever you would call it in access from the purchaseOrder table to the userList table to get the fullName. The userList table contains a lookup for the department the user belongs to in the departments table. So I guess that is a lookup within a lookup.

als315, the database is currently one file.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 34944048
So you are using a lookup field it seems.
You need to build a query to act as your rowsource from the twoo tables and then you can re-create the combo box on your form. It looks like there will be a hidden userid field so you would have 3 columns in the combo.  This means that the code would be..(assuming the dept name is in column 3)

me.txtDepartment = me.usercomboname.column(2)
0
 

Author Comment

by:excelsupport
ID: 34950712
I tried a few queries for the row source of the department combo box...

If I run this SQL in the SQL design view

SELECT departments.department
FROM departments
WHERE (((departments.ID)=(SELECT userList.department FROM userList WHERE userList.ID = me.raisedBy )));

Then it prompts me for the value me.raisedBy (which I thought would get the user ID value from the combo box the user uses to select their name. If I add .column(n) then I get undefined function). If I fill in a valid value then it gets me a valid department name, but I still don't see how to integrate that into the form...
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 34950776
You don't need a departments combo.  Just a textbox to show the name.
You need the User combo rowsource query modified to contain user and department.  But you can't use the department field from the user table because it is a lookup field and is really just a number.  So you have to add the departments table to the rowsource query and get the department from that.
0
 

Author Comment

by:excelsupport
ID: 34950841
Ahh right - I see (I think!)

So the row source of the user combo box is now this (with userList.ID and departments.department hidden in the combo box)

SELECT userList.ID, userList.fullName, departments.department FROM userList, departments WHERE userList.department = departments.ID ORDER BY userList.fullName;

and the update event for the same combo box now updates department textBox (Changed from a combo box, yes there was no reason for it to be a combo box, it was just where I started from - trying to get my head around the way Access does things) is this

Private Sub raisedBy_AfterUpdate()
        Me.departmentTextBox = Me.raisedBy.Column(2)
End Sub

That seems to do the trick thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question