How to populate a combo box from a function

[Phil Hood]

Hi,

I'm hoping that someone can explain how to populate a
combo box list using a function rather than a query.

I want to do it this way so I can incorporate some if/then
arguments so that the combo list can vary according to the
circumstances when it is created.

I understand you can do it but I can't work out how.

I'm looking to create a function that looks something like:

Public Function RiderList()

Dim ???

if screen.activeform = "Heat" then
SELECT RiderMatch.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderMatch ON Riders.RiderID =
RiderMatch.RiderID
WHERE ((([Forms]![Heat]![MatchID])=[RiderMatch]!
[MatchID]));

if screen.activeform = "Heat edit" then
SELECT RiderMatch.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderMatch ON Riders.RiderID =
RiderMatch.RiderID
WHERE (((Forms![Heat edit]![MatchID])=[RiderMatch]!
[MatchID]));

I'm not sure how to construct this or indeed how to
specify this as the row source for the combo control.
Presumably the select statement has to be associated with
a variable of some kind?

Thanks in advance for any help you can offer.

Phil.

 

[Marshall Barton]

I'm hoping that someone can explain how to populate a
combo box list using a function rather than a query.

I want to do it this way so I can incorporate some if/then
arguments so that the combo list can vary according to the
circumstances when it is created.

I understand you can do it but I can't work out how.

I'm looking to create a function that looks something like:

Public Function RiderList()

Dim ???

if screen.activeform = "Heat" then
SELECT RiderMatch.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderMatch ON Riders.RiderID =
RiderMatch.RiderID
WHERE ((([Forms]![Heat]![MatchID])=[RiderMatch]!
[MatchID]));

if screen.activeform = "Heat edit" then
SELECT RiderMatch.RiderID, Riders.Full_Name
FROM Riders INNER JOIN RiderMatch ON Riders.RiderID =
RiderMatch.RiderID
WHERE (((Forms![Heat edit]![MatchID])=[RiderMatch]!
[MatchID]));

I'm not sure how to construct this or indeed how to
specify this as the row source for the combo control.
Presumably the select statement has to be associated with
a variable of some kind?

From what I can see here, you're making this way more
complicated than needed. It would really help clarify your
situation if you explained how the form containing the combo
box relates to the active form, whatever that is, along with
when/where the code resides.

Best I can suggest at this point is something along these
lines:

Dim strSQL As String
strSQL = "SELECT RiderMatch.RiderID, Riders.Full_Name " _
& "FROM Riders INNER JOIN RiderMatch : _
& "ON Riders.RiderID = RiderMatch.RiderID " _
& "WHERE RiderMatch.MatchID = " _
& Screen.ActiveForm.MatchID
Me.thecombobox.RowSource = strSQL