assigning a unique query for each textbox on a form

[ArielZusya]

I've got a table (tblMain) with the following (relevant) fields:

tblMain
.FirstName
.LastName
.Seat
.RefNum

I've got a form that has a graphical display of the seats to which the
people listed in tblMain are assigned. I'd like to put a text box on each
seat corresponding to first and last name of the person assigned to that seat
(stored in tblMain.Seat). The same database is used for various seatings so
I'd like only those associated with the RefNum at hand to be called. I can
write a simple enough query which I can call from VBA and which relies on
atext box on the form that holds the refNum (txtRefNum) to accomplish this:

stSQLHolder = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat,
tblMain.RefNum FROM tblMain WHERE (((tblMain.Seat)=1") AND
((tblMain.RefNum)='" & Me.txtRefNum & "'));"

But I just don't know how to make use of this once I've done it. If it was
a combo box I would tell it to set RowSource to this query... but I don't
think I can do that with Control Source, right? So... I imagine there's an
expression I can write for the Control Source but I'm still unclear how to
rewrite queries as expressions. Is that how to proceed? If so, how do I do
that? If not, what should I do instead? Thanks!

 

[Klatuu]

Have you tried using the control source? I think it would work.

 

[ArielZusya]

I did... it returned one of those #Name? errors instead of populating
propery. The way I did that was in OnCurrent for the form. I tried the
following:

'**START CODE***

Private Sub Form_Current()
Dim stSQLHolder As String
stSQLHolder = "SELECT tblMain.FirstName FROM tblMain WHERE
(((tblMain.Seat)=1) AND ((tblMain.RefNum)='" & Me.txtRefNum & "'));"
Me.txtSeat1First.ControlSource = stSQLHolder
End Sub

'***END CODE***

I also tried:

stSQLHolder = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat,
tblMain.RefNum FROM tblMain WHERE (((tblMain.Seat)=1) AND
((tblMain.RefNum)='" & Me.txtRefNum & "'));"

and the above two with '" & Me.txtRefNum & "' replaced with '206CBR2985' to
see if hardcoding a RefNum would help. No luck.

I think part of the problem is that I want to query tblMain for FirstName,
LastName, Seat, and RefNum and then only call to one of those... so I was
thinking I could change the form's control source from tblMain to a query and
then in the query I could have subqueries for each seat... something like
SELECt tblMain.Seat As Seat1 from tblMain WHERE tblMain.Seat = 1 but I'm not
sure if that's the way to do this nor am I sure about how this is going to
work or if there is a way to loop through this so I don't have to write a
subquery for each seat or what. Anyhow... I'm open to any guidance you might
have for me. Thanks for your help!

Just in case this was about