Hi everyone,

How can I code the RowSource of a combo box given a selection from an option group?

I have attached a sample database. Obviously the code is not working. :( The latest version is also pasted below.

Any help you can provide is greatly appreciated. I have not been able to adapt any code I found on this site.

Thank you.

Janet

Private Sub grpTyp_AfterUpdate()

Select Case grpTyp
Case "CIL"
Me.cboVer.Enabled = True
Me.cboVer.RowSourceType = Table
' Me.cboVer.RowSource = "SELECT tblCilVer.CILVer" & "FROM tblCilVer" & "ORDER BY tblCilVer.CILVer;"
Me.cboVer.RowSource = "SELECT tblCilVer.CILID, tblCilVer.CILVer" & "FROM tblCilVer" & "ORDER BY [CILVer];"
Me.cboVer.SetFocus
Case "HIL"
Me.cboVer.Enabled = True
Me.cboVer.RowSource = "tblHilVer"
Me.cboVer.SetFocus
Case "INT"
Me.cboVer.Enabled = True
Me.cboVer.RowSource = "SELECT [INTVer]" & "FROM tblIntVer" & "ORDER BY tblIntVer.INTVer;"
Me.cboVer.SetFocus
End Select

End Sub

Janet,

The value of an Option Group is always a number. It is the Option Value of the selected option button.

Therefore this is not possible:
Case "CIL"

Instead, it is probably:
Case 1

Another point... The syntax of the SQL of the Row Source settings is incorrect, in that you have no spaces between the clauses. In fact, no need for the '&'s at all, e.g.
Me.cboVer.RowSource = "SELECT tblCilVer.CILID, tblCilVer.CILVer FROM tblCilVer ORDER BY [CILVer]"

See how you go with that.

Not to spoon feed, ... but I was working this out before Steve posted ...

Try:



I'll let you work out the combo box settings on your own.

Thank you Steve,

I made the changes you suggested but it still does not work.
Thank you for your time and instruction. Please feel free to throw any other ideas my way. I do not get any errors so I don't know where to look.

v/r,
Janet

Janet,

Just a hint... sorry not meaning to get at you. "Does not work" does not really give us a lot to go on. If possible, it helps if you can say exactly what results you get, and exactly how this is different from what you want. Also, re-posting your modified code would be a good idea. Thanks.

Anyway, Walter's suggestion any good?

Thank you Walter!

Your code gave me the PK autonumber values so it was easily changed to the other column.

I very much appreciate your help and time.

Thank you very much Walter and Steve!!

v/r,
Janet

Here's what worked.

Private Sub grpTyp_AfterUpdate()

Select Case grpTyp
Case 1
Me.cboVer.RowSource = "SELECT CILVer FROM tblCilVer ORDER BY CILVer"
Case 2
Me.cboVer.RowSource = "SELECT HILVer FROM tblHilVer ORDER BY HILVer"
Case 3
Me.cboVer.RowSource = "SELECT INTVer FROM tblIntVer ORDER BY INTVer"
End Select
Me.cboVer.SetFocus

End Sub

You're right Steve,

I should not have posted that way. I should have posted the results, compared them to what I needed, and the modified code, as you suggest.

Thank you for the input. I hope to do better in the future.

Yes, thanks, Walter's suggestion was good. I posted the modified code in my response to him in this thread.

Thank you for your help and patience.

v/r,
Janet

Very good, Janet. Thanks for letting us know. Best wishes for the rest of the project.