Help working with Option Groups, Command Buttons, and Combo Boxes

[beacon]

Hi,

I am creating a relatively simple database, but I've come across something that I haven't been able to find an answer for. I typically research until I'm blue in the face, so my frustration must be pretty high for me to actually post this.

I have a form, called frmCampusSelect, that has an option group with 2 options and has a submit and close button below it. I have written code to close this form and I have written code to submit that the user has chosen one of the 2 options, but I'm unable to take what the user has selected and have it alter the form that opens, frmEmployee, when the user selects submit.

On frmEmployee, there's a combo box that's row source queries a table I set up with one campus' programs. The problem is that there are 2 campus' and I'm trying to use the option group to determine whether the combo box is filled with the programs from the first campus or from the second campus.

The row source for the 'Programs' combo box is 'SELECT tblProgramV.ProgramV, tblProgramV.ProgramV FROM tblProgramV;

The control source is to the Programs field on the Employees table.

To reiterate, I want the user to select a campus, press submit, and have the program combo box populate with the programs that correspond to the user's selection.

I'm sure this is something easy, but I'm having the worst time with it today. Is it the weekend yet?

[Scott Price]

Sorry! It's still Tuesday :-)

In the Row Source of your combo box, you'll have to point to some kind of WHERE clause that differentiates between the two campuses. I assume you have some kind of a CampusID field?

So in the code for your submit command button you'll have to capture the value returned from the option group, translate it into which CampusID it looks up, and feed that into the combo box Row Source... With me so far?

The values returned by your option group will be 1 or 2. So write an If---Then statement into your command button's on click event setting the Row Source of your combo box. You'll have to make sure the form is open, of course, before setting the row source.
In your button's on click event:

DoCmd.OpenForm "frmEmployees"
 
If [OptionGroupName] = 1 Then
  Forms!frmEmployee![ComboBoxName].RowSourceType = "Table/Query"
  Forms!frmEmployee![ComboBoxName].RowSource = "SELECT ... FROM ... WHERE CampusID = ..."
If [OptionGroupName = 2 Then
  Etc...
End If
 
Me.Close

Give this a try and let me know how you get along.

Regards,
Scott

[beacon]

While I was waiting for Scott's reply I was able to get the Program combo box to print out all of the items in one of the Programs, but not just the ones specified by the users input.

Here's what I have:

Two different groups of Programs identified by the Campus (V or W)

tblEmployee
text field Program (control source for Program in frmEmployee)

tblProgramV
ProgramID (auto number)
Program (text field)
Program 1
Program 2
Program 3

tblProgramW
ProgramID (auto number)
Program (text field)
Program 1
Program 2
Program 3


frmCampusSelect
optionGroup
optionV with optionLabelV
optionW with optionLabelW
cmdCancel

Public Sub cmdCancel_Click()
               DoCmd.Close acForm, Me.Name
          End Sub

cmdSubmit

Private Sub cmdSubmit_Click()
               If optionGroup.Value > 1 Then
                   DoCmd.OpenForm "frmEmployee", acNormal, , , acFormAdd
                   DoCmd.Close acForm, Me.Name
               Else
                   MsgBox "Please select a Campus!", vbExclamation + vbOKOnly
               End If
          End Sub

frmEmployee
combo box Program
Row Source Type = "Table/Query"
Row Source = SELECT tblProgramV.ProgramV, tblProgramV.ProgramV FROM tblProgramV;

Scott's way sounds better and seems to be what I'm looking for, but I can't get the code he gave me to work. I'm sure it has to do with my row source being incorrect, but who knows?

[Scott Price]

Is the code you gave for the row source copy and pasted? If so, take a closer look at what you are asking the database to do! The Select statement contains duplicate values.

Just on a quick glance it looks like you have a design/normalization issue with your tables. Have a look at this tutorial and see if you can see what I mean: Database Normalisation and Table Structures

As for making the code I gave you work, can you paste in what you tried and mention the error message it gave?

Regards,
Scott

[beacon]

Is the code you gave for the row source copy and pasted? If so, take a closer look at what you are asking the database to do! The Select statement contains duplicate values.

Just on a quick glance it looks like you have a design/normalization issue with your tables. Have a look at this tutorial and see if you can see what I mean: Database Normalisation and Table Structures

As for making the code I gave you work, can you paste in what you tried and mention the error message it gave?

Regards,
Scott

I guess I did have a normalization problem with the Program tables. I should have one Program table with either a 1 or a 2 to distinguish between the campuses. This should match up with my optionGroup where the user selects which campus they want.

I changed the row source to read "SELECT tblprogram.Program FROM tblprogram WHERE (((tblprogram.ProgramID)=[optiongroup])) ORDER BY tblprogram.Program;

Then I went to the code for cmdSubmit and typed in the following:

Private Sub cmdSubmit_Click()
    DoCmd.OpenForm "frmEmployee", acNormal, , , acFormAdd
 
    If [optionGroup] = 1 Then
        Forms!frmEmployee![Program].RowSourceType = "Table/Query"
        Forms!frmEmployee![Program].RowSource = "SELECT tblprogram.program FROM tblprogram WHERE ProgramID = 1"
    ElseIf [optionGroup] = 2 Then
        Forms!frmEmployee![Program].RowSourceType = "Table/Query"
        Forms!frmEmployee![Program].RowSource = "SELECT tblprogram.program FROM tblprogram WHERE ProgramID = 2"
End If
    DoCmd.Close acForm, Me.Name
End Sub

This doesn't return any errors, but it also doesn't populate the Program combo box with any values. It's like I'm not getting my parameters passed. The same thing happened to me yesterday when I tried Scott's code.

I feel very close to the answer, but I'm still having trouble. I'm used to using C++ and being able to cout anything to the screen to test whether or not my values are correct before I pass them. Obviously, in this case, they aren't because they continue to return an empty, multiline combo box.

If anyone can help, I sure would appreciate it.

[cyberdwarf]

You should be able to pass the User's selection to the opened form by using the OpenArgs argument to DoCmd.OpenForm.

This value can be picked up in the OnLoad event of the opened form, eg:-

Private UserChoice As Variant
 
Private Sub Form_Load()
     UserChoice = Me.OpenArgs
End Sub

The UserChoice variable may now be used to build your controlsource, or for any other purposes.

HTH

Steve

[beacon]

You should be able to pass the User's selection to the opened form by using the OpenArgs argument to DoCmd.OpenForm.

This value can be picked up in the OnLoad event of the opened form, eg:-

Private UserChoice As Variant
 
Private Sub Form_Load()
     UserChoice = Me.OpenArgs
End Sub

The UserChoice variable may now be used to build your controlsource, or for any other purposes.

HTH

Steve

I finally was able to get it to work. When I defined my row source, I was including my openargs in the quotes with the select statement. Small oversight on my part that ended up causing me big headaches.

Thanks everyone for you help.

[Scott Price]

You're welcome and glad you got it working!

Thanks for posting back with what went wrong, and what went right ;-)

Regards,
Scott

[beacon]

Sure thing...I actually stumbled upon the Locals Window and that opened up Pandora's box for me. It made me wonder how I ever got around in Visual Basic without them.

It definitely made passing my values easier. I feel dumb because I probably wouldn't have had to post this if I had known about it before.

Anyway thanks again...