Change rowsource depending on option group;SQL conversion

[Access Greenhorn]

I would like to do a case statement, like the one below

Private Sub cboPersonID_BeforeUpdate(Cancel As Integer)

Select Case grpSelect.Value
Case 1
Me.cboItems.RowSource = "SELECT blah blah ORDER ON First Name;"
Case 2
Me.cboItems.RowSource = "SELECT blah blah ORDER ON Last Name;"
End Select

Exit Sub

but I don't know how to convert my query SQL statement to a VBA SQL
statement. Could someone pls convert or show me how to convert the following
statement?

SELECT tblPersonnel.PersonID, tblPersonnel.FirstName, tblPersonnel.LastName,
tblLabs.City
FROM tblLabs RIGHT JOIN (tblPersonnel LEFT JOIN tblCertification ON
tblPersonnel.PersonID = tblCertification.PersonID) ON tblLabs.LabCode =
tblPersonnel.LabCode
WHERE (((tblPersonnel.Postion) Like '*Technician*'))
ORDER BY tblPersonnel.LastName;

Thanks,
AG

 

[Jeff Boyce]

I'm not following. It sounds like you want to have set the RowSource, based
on an option group selection.

Your Select Case statement seems to be doing that.

If you substitute the more complete SQL statement for your "blah blah"
RowSource =, what happens?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[kingston via AccessMonster.com]

Your code and SQL string look correct. Did you insert the SQL string after
RowSource and get an error? If you want to break the SQL string up into
multiple lines, use & _ and be careful of the quotation marks. I did notice
that you did not requery the combo control after resetting the row source:

Me.cboItems.Requery