How to set the default for an option group

[RenaG]

I am working in Access 2007. And I am an extreme newbie to Access.

I have 3 tables:
User
ID FirstName LastName
Program
ID ProgramName
ProgramUserXRef
ID UserID ProgID

My form consists of an unbound ComboBox whose Row Source is the User table. I added an Option group that displays the Programs. I want the default for this group to come from the combobox when the user selects their name.

I tried putting this in the Default Value:

(SELECT DISTINCT ProgramUserXref .ProgramID FROM ProgramUserXref WHERE ((ProgramUserXref .UserID) = lUserID))

But that didn't work.

I also tried:

Private Sub cmbUser_AfterUpdate()
 
    lUserID = Me.cmbUser
 
    optProgramID.Requery
 
    If Not IsNull(lUserID) Then
        Dim MySQL As String
        MySQL = "SELECT DISTINCT ProgramID FROM ProgramUserXref "
        MySQL = MySQL & "WHERE UserID = lUserID"
        Me!ProgID.DefaultValue = MySQL
        Me!ProgID.Requery
 
     End If
 
End Sub

But that didn't work either.

Does anybody have any suggestions?

TIA
~RLG

 

[JamesMcS]

The defaultvalue property is looking for a number rather than an SQL statement.

When you say default value, do you mean that you want a particular option to be selected once a choice has been made in your combo box? What you need to do there is have a field in your user table which contains the program number, corresponding to the option group. Then, in the combo box afterupdate, just put

optiongroupname.value = comboname.column(x)

where x is the column number you want to pull back, starting from 0.

 

[RenaG]

Awesome, awesome. awesome!!!!! It worked. And it was so simple. I added the ProgramUserXRef table to the User table in the SQL stmt on the combobox so that I had available to me the programID. Put in the after Update event a variation of your code:

Me!optProgramID.DefaultValue = Me!cmbUser.Column(3)

Threw in a requery for good measure and wha-laa. Whoo-hoo. I am so happy .

Now on to the next hurdle!

Thank you so much.

 

[JamesMcS]

No worries! Let us know if you get stuck again

 

[RenaG]

Oh, be assured, I'll be back. I am an extreme newbie to Access and this project is very complex. But I am learning a lot and having fun, thanks to forums like this that help me work through my ignorance.

The next step is to take the ProgramID and have it drive what forms are displayed. That probably belongs on a new thread but do you have any hints? I have saved the value in a global variable.

Have a great day!

~RLG

 

[JamesMcS]

Yep - so depending on the programID, you'd have a number of different forms being opened? I would store the programIDs and form names in a table. Then, in the event you're going to use to open the forms, have a query like

"SELECT ProgramID, Formname from ProgramsAndForms WHERE [ProgramID]=" & gbl_ProgramID

as a recordset. Followed by

Do while not rst.eof
docmd.openform (rst!formname)
rst.movenext
Loop

Or something along those lines.

 

[RenaG]

Cool! I'll chew on that for a while and see what I come up with. If I get stuck, I'll start another thread.

Thanks again!!

~RLG