Solved

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

Posted on 2011-02-21
8
521 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 39

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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now