Solved

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

Posted on 2011-02-21
8
530 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

679 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