Linking combobox

Mabuya

Registered User.
Local time
Tomorrow, 00:07
Joined
Nov 21, 2006
Messages
12
Good day all,

I got a quick question..

I got 2 tables

Departments:
ID
Department

Functions:
ID
Function
Department_ID

They are linked with an one to many relation.

Now when i'm making my form i want to have 2 combo boxes, 1 listing the results found in Departments.Department and another listing the results of Functions.Function where the Functions.Department_ID is equal to Departments.ID.

When i put this in an sql query it doesnt work, at best it asks me to put in the Departments.ID manualy tho i want the functions combobox updated when i pick something from Department.

Thanks in advance
 
I understand you have two combo boxes on a form, a Department Combo box and then a Function Combo Box. After the Department is selected in the Department Combo you want to display only the Functions linked to the Department. You need to add code on the the Department Combo's AfterUpdate event to update the the Function Combo's rowsource property.

What you need to do is set the Function Combo Box's Row Source to the SQL you generate.

FunctionCombo.RowSource = "SELECT WHERE (DepartmentID = " & DeparmentCombo & ")"

Where DepartmentCombo is the DepartmentID

If you are familar with using VBA Code this fairly easy. We use a public function to to generate the SQL as we typically will use this capability on multiple forms in an application.

FunctionCombo.RowSource = MakeFunctionComboRowSource(DepartmentID)

Good luck. If you have any questions, let me know.
 
Hi Guys,

I'm having a similar problem to this one. I have a text box (called 'ID') on my main form (called 'Questions' in this case) that contains the ID of that particular record, and a combobox ('Answers') on a subform called 'Answers Subform'. I'd like the combo box to pick up the record ID from the text box on the main form and use it with a SELECT statement to filter the results accordingly, before updating the rowsource. Is it even possible using this method? I can't seem to get the syntax right at all. Any help would be really useful! Thanks
 
As this is thread about Combobox, please allow me to post this question which I presume is somehow related.
I have the source property of a combo only used for
entering new Names and finding the last record like this:

SELECT DISTINCT Miracle_Cloth_Main.RecordNum, Miracle_Cloth_Main.Name FROM Miracle_Cloth_Main ORDER BY [Name] DESC;

I need to copy the Name to a second field Cust_ID
for the combo to work as a data entry as well as select
mechanism, leaving the Cust_ID as the basis for each record.

Currently the combo search and find the last record from the Name field. However it doesn't save the name value when a new record is created. Consequently empty fields are created resulting in the search always coming with the first record only.
So is it possible to force the
search to search the Cust_ID field insted of the Combo114 field modifying the code below(after_update) :

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindLast "[RecordNum] = " & Str(Nz(Me![Combo114], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
I have no clue if this is the same as the question of valley, it's to early.

Ok here comes,

My department and function select their info from the tables noted under here and store info in the form to the table computer.

I succeeded to link two comboboxes
---------------
Tables:

Departments:
ID
Department

Functions:
ID
Function
Department_ID
----------------

I did this by (looking on the internet) making a query containing;
Functions.Function Functions.ID and Department_ID wich has the criteria: Forms![Inventory]![Department]

In the department combobox i have an afterupdate linking to a macro that does a Requery.function.

Well so far so good, it all works ets, but now i want to create a report of all that info, but when i checked my report a saw that my field department had stored the ID number and not the value in Department.

I tried to fix it by changing the order of selecting in the rowsource in the department combobox to:
SELECT Department.Department, Department.ID FROM Department;

But when i did that the Function combobox didnt work correctly annymore..
Also tried making a query that would do about the same, that also didnt work.

Thanks!!
 

Users who are viewing this thread

Back
Top Bottom