Cascading Combo's then opening a form!

[andy_dyer]

Hi,

I'm trying to get my main menu to have four combo's three of which cascade;

Programme Name > Provider Name > Intake Group Name then Reporting Term (school term) which isn't cascaded.

My problem is that I have a table of provider names with an ID and then a table of programme names and ID and then a table of Intake Group Names and ID...

I need to get these linked somehow so that providers are linked with the programmes and the intakes are linked to the particular provider on the particular programme.

There is added complexity as some of the providers operate in more than one programme.

So once I've chosen my programme I want only to see the providers for the programme, and then once I've chosen a provider I want to only see the intake groups for that provider on that programme...

Then once I've chosen my my reporting term I want the record to open that matches (there should be only one)...

Is this possible?

 

[dreamdelerium]

hey, whats up. first, youll want to make sure your tables are set up properly. im not so great at this part, but i believe youl want three table as follows (if im wrong, someone chime in):

tblProgram (with ProgramID (autonumber) column and ProgramName column)

tblProvider(ProvidorId (autonumber) column, ProvidorName column, and ProviderName column)

tblIntakeGroup (IntakeID(autoNumber) column, IntakeName column, ProgramName Column, and ProviderName Column)

now that thats done, on the forms onOpen event youll need to fill the first combo with all Program names ( or you can use the wizard for this one)

Next, youll need to fill the second (provider combo box). first create the combo box on your form (we'll call it ProviderCombo). dont run through the wizard when prompted. just click cancel. now, in design view, open the ProviderCombo's Properties. make sure row source type is table/query and row source is empty. now edit the ProgramNames properties. on the OnChange events, add an event procedure. your procedure will look like this:

Private Sub ProgramName_Change()
On Error GoTo errorhandler
 
Dim SelectString As String
Dim ProgramName As String
 
ProgramName = Me.ProgramCombo.Column(0)  
' this is the value of program combo
 
SelectString = "Select ProviderName FROM tblProvider where ProgramName=" & ProgramName & ""
' this build your select string
 
Me.ProvidorCombo.RowSource = SelectString
'assigns the string to the combos rowsource

Me.ProvidorCombo.SetFocus
'set focus to that combobox

Me.ProvidorCombo.Text = Me.ProvidorCombo.ItemData(0)
'makes the combobox's default value the first in the list
 
 
 
errorhandler:
If Err.Number = 0 Then
Else
MsgBox (Err.Description)
'if there is an error, pop it in a msgox
End If
End Sub

now youll do the same thing for the intake combo. on the Providor combo, when the change event fires, go to a new event procedure. this procedure should look similar except the combo box names will change and the selectString will be altered. that should do it. let me know if you have any problems or if i screwed it up

 

[andy_dyer]

Hi,

Thanks for your help...

A couple of problems...

1. One Provider may provide for 3,4 or even 5 programmes how I can fix this? Someone has suggested some kind of combined table but I can't gte this working either...

2. I can't get the provider box to display anything...

I've used the following code:

Private Sub cboProvider_Change()
On Error GoTo errorhandler

Dim SelectString As String
Dim ProgrammeName As String

ProgrammeName = Me.cboProgrammeName.Column(0)
' this is the value of program combo

SelectString = "Select ProviderName FROM tblProvider where ProgrammeName=" & ProgrammeName & ""
' this build your select string

Me.cboProvider.RowSource = SelectString
'assigns the string to the combos rowsource

Me.cboProvider.SetFocus
'set focus to that combobox

Me.cboProvider.Text = Me.cboProvidor.ItemData(0)
'makes the combobox's default value the first in the list



errorhandler:
If Err.Number = 0 Then
Else
MsgBox (Err.Description)
'if there is an error, pop it in a msgox
End If
End Sub

I've set the tables as you suggested but I just get a blank return in the combo...

 

[wiklendt]

regarding the 'combined table', i have done this this way.

if i have two tables: Individuals and Classes. obviously, an individual can attend many classes, so create another table "Roll" with these columns:

RollID (PK)
IndividualID (FK; i make this a combo lookup with the wizard to source from the Individuals table)
ClassID (FK; i make this a combo lookup with the wizard to source form the Classes table)

then, in your Roll table (in datasheet view), you drop-down an individual, and then the first class they attend. then, if that individual attends more classes, just repeat this step for the following records, so you can have something like this:

adam chemistry
adam biology
adam physics
christy biology
christy graphic arts
bob computers
bob physics


etc...

i find it's easier to make a form with the individuals as the source, then a subform with the roll table as source, and IndividualID as the link... then for each individual all you need to do is drop in the classes, as opposed to having to drop in their names as you do in the raw table.

 

[dreamdelerium]

to fill the provider box: youll want to check to make sure everything is working as follows:
after building the SelectString, either print it out or pop it in a message box to make sure the string is built properly. if it looks good, try testing it in the querries tab. (to do this, in the database window, click querries, then create a querry in design mode. when the show table dialog pops, just close it. in your tool bar, you can change the queries views. select SQL view. now enter the string and hit the run botton. if there is an error, find out what it is and make your string look like that. if there are no errors, goto data sheet view. it should be filled with the data. if not that means the string is technically written correctly but has nothing to pick (ie, you asked it to pick all red apples from the red apple column, which exists, but there are only green ones)

anyway, if this doesnt help your select string should look like this:
Select ProviderName FROM tblProvider where ProgrammeName= 'SomeValueFromTheOtherComboBox'.

let me know what happens