Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

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

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
excelsupport
Asked:
excelsupport
  • 4
  • 3
3 Solutions
 
excelsupportAuthor Commented:
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
 
peter57rCommented:
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
 
als315Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
excelsupportAuthor Commented:
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
 
peter57rCommented:
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
 
excelsupportAuthor Commented:
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
 
peter57rCommented:
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
 
excelsupportAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now