Setting each query entry to separate strings

[gschimek]

Is there a way to take data from from individual records in a query and assign them to strings?

For example, I want to do something like:
Name1 = qryLeaders.Name.record_1
Name2 = qryLeaders.Name.record_2

Then I would be able to say something like "Please contact " Name1 " or " Name2 " if you have any questions."

I just don't know the proper VBA code to use to do that. Does anybody else know?

 

[gemma-the-husky]

if you know you will only have 2 leaders to name, then store them in a single record, and they will be together for your purposes.

If you have multiple records how do you know there will be only two, and if there are more, how do you know which two you want.

however, in your existing scenario you could open the query as a recordset, and read through the records, testing the value of the leader for each record

set rst = currentdb.openrecordset(myquery)
rst.movefirst
while not eof(rst)
thisleader = rst!leader
----- processs the leader
rst.movenext
wend
rst.close

the macrocode is ok here, but the syntax isnt spot on.

 

[gschimek]

I'll give that a try. But to clarify my setup per your questions, I know that there will always be 2 leaders. And each person has a related record with a Team Name. Those 2 leaders would have the same Team Name and nobody else will. So what I'd want to do is test the team name, and each name that corresponds to that I would want to be able to use.

If that helps to clarify what I need, let me know. Thanks.

 

[gemma-the-husky]

what i mean is

if a team has a leader_1 and a leader_2, then store both in a single team record. I know if you then want to list all team leaders its a bit harder though, as you have 2 columns

team1 - leader1 fred leader2 bill
team2 - leader1 jim leader2 mike

if you store a table of team leaders with eg

team1 - leader fred
team1 - leader bill
team2 - leader jim
team2 - leader mike

its harder to get what you need, and you will have to process a recordset or query line by line to assemble the result

i think its a matter for you to take the best design decision to suit your requirements