Incorporating my synchronised combo box in form 1

[ezlearning]

I created a form that contains a synchronized combo box (Access 2007), based on an example that Microsoft has and it works.

A user can Select Box 1 and in box 2 you'll only see the items that fall under that choice made in Box 1.

I am new to building advanced Forms so I don't know how to incorporate it so I can use it for updating my table. I think it has something to do with the field being "unbound" to the table?

Here is the detail:

I have 4 tables.
Applications = ApplicationID, Applications
Profiles = ProfileID, Applications, ProfilesNames
Departments = DeptID, DeptName
DeptProfiles (Juntion table) = ApplicationID, DeptID, ProfileID, ProfileID.value (this is a multi-selectable field)

This is the code I used to run it.
Private Sub cboCategories_AfterUpdate()

Me.cboProfiles.RowSource = "SELECT ProfileName FROM" & _
" Profiles WHERE ApplicationID = " & Me.cboApplication & _
" ORDER BY ProfileName"
Me.cboProfiles = Me.cboProfiles.ItemData(0)

End Sub

My goal: To have a Form that is populating the DeptProfiles (junction table). Right now I have a simple form, but as my profile table grows there will be too many profiles for a user to pick from.

 

[TheAceMan1]

How are ya ezlearning ... and welcome to [blue]Tek-Tips![/blue]

[blue] ... I don't know how to incorporate it so I can use it for [purple]updating[/purple] my table.[/blue]

You can append records to a table and call it updating!
You can modify existing records in a table and call it updating!
You can delete records from a table and call it updating!

You need to be more specific about this.

Try explaining what you want to do without refering to comboboxes! ...

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[ezlearning]

I appreciate your time!
I would like to create a form that will have three different combo boxes. The use of this database is so we can make sure a when someone starts in a department they get all the right applications and profiles the day they start without having to ask the department each time.

First selection which department they are in
Second selection which Application their department uses
Third selection is profiles for each department under that application.

An example, user selects:
1. Accounting (out of several depts)
2. MS Access 2007 (out of several Programs)
3. Developer, Query writing, Form building within Access 2007. User will see only the selections that fall within Access 2007 and can select several that apply to their dept.
I want this form to populate the table that I set up as a Junction table. After looking for while I am not sure how to take what I did for the synchronized combo boxes and have it populate the table with their choices.

 

[TheAceMan1]

ezlearning . . .

You'll need an [blue]Update[/blue] query that incorperates the values from the comboboxes.

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

ezlearning . . .

Sorry for the delay.

[blue] ... I am not sure how to take what I did for the synchronized combo boxes and have it populate the table with their choices.[/blue]

For starters be aware that [blue]ApplicationID[/blue], [blue]DeptID[/blue] and [blue]ProfileID[/blue] need to preexist in their parent tables or you'll get an error. This should be fine as the comboboxes are based on their parent tables.

What you are doing here is called cascading comboboxes. The technique involves a [blue]WHERE clause[/blue] ... whereby the criteria is dependent on data external to the combobox (another combobox in your case). In the example from your post origination ...

[blue]Me.cboProfiles.RowSource = "SELECT ProfileName " & _
                           "FROM Profiles " & _
                           "WHERE ApplicationID = " & Me.[purple][b]cboApplication[/b][/purple] & " " & _
                           "ORDER BY ProfileName"[/blue]

... [purple]cboApplication[/purple] determines whats returned from the SQL. Also note, in order to see the newly returned records cboApplication has to be requeried. This is typically done in the [blue]AfterUpdate[/blue] event of the controlling combobox (in this case cboApplication), with a line like:

[blue]   Me.ComboboxName.[purple][b]Requery[/b][/purple][/blue]

Take a little care in setting up the comboboxes (I'm assuming you want to bound/bind the form). The 1st column (column zero) of each combobox should hold the ID's [blue]ApplicationID[/blue], [blue]DeptID[/blue] and [blue]ProfileID[/blue], and the [blue]Bound Column[/blue] property should equal 1. Thats pretty much it for the comboboxes other than which column(s) you want to display.

I've never used a [blue]multi-valued[/blue] control and doubt I ever will (don't see the need) ... so I can help you there.

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[ezlearning]

Thank you for the reply TheAceMan1. I will be working on it tomorrow and let you know how I do. Just a quick question though. If you don't use the multi-valued control, how would you do it? Would you set up a another table for the profiles?

 

[TheAceMan1]

ezlearning . . .

I'm not sure about your table structure (particularly as you may have it in your mind). You have an [blue]Applications[/blue] table, yet there's [purple]Applications[/purple] in the [blue]Profiles[/blue] table, as well as [blue]ApplicationID[/blue] in your junction table.

To answer you question ... yes I would probably use another table but its hard to say with present structure. I suggest you head on over too Microsoft: Access Tables and Relationships and double check your structure there. If your tables are not right you could waste alot of time.

In either case could we see what some actual data looks like?

[blue]Your Thoughts? . . .[/blue]



See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[ezlearning]

I will read the link you posted and try to post my table relationships with some data. I see your point that I could drop the Applications from the profile table (which is a one-to-many relationship to the other tables), as long as I have the junction table there. I will also think about adding a new table. The reason I was going to go with the multiple selection field was I thought it would be easier, but maybe not.

 

[TheAceMan1]

ezlearning . . .

The link is to a forum here at Tek-Tips where you go over your table structure and make sure its right.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!