call all values from a query

[scooble]

I have designed a query that returns appointment data for 'n' date and for 'n' team member. Each line of the query has a start time and end time.

Each 'time' is represented as one of 37 small boxes on a form.

I would like to colour all the boxes between each start and end time for each appointment.

The query would look something like this;
Date____Team__ Start__ End
12/05/10 Bob. ..... 9:00... 9:30
12/05/10 Bob..... 10.30.. 12:00
12/05/10 Bob..... 14:30.. 16:00

How dow I loop through each record in the query, first time round the loop will colour boxes between 9:00 and 9:30, second time round loop will colour boxes from 10:30 to 12:00 and third time (and final time) will colour boxes between 14:30 and 16:00

 

[PNGBill]

Try and do the colour boxes with Conditional Formatting.
In Design View, click on one control and then click on the menu bar Conditional Formatting.
You should be able to set the colour to change on the value in the control.

Otherwise the Control's Property event may do this rather then require a loop.

 

[scooble]

thanks for the response, but I believe that would only highlight the first record, i.e values 9:00 and 9:30 and not all of the other times too. What I require is a method to either step through each row of a query or somehow load the entire results of the query into an array which I can then iterrorgate with a loop

 

[PNGBill]

Of course you are not colouring a query but controls on a form - I assume.

Conditional Format allows more then one condition.

 

[scooble]

for the sample query I submitted, how would it be possible for it to colour boxes on a form from 9:00 through to 9:30 as well as 10:30 through to 12:00 and from14:30 through to 16:00. Normally the form only looks at one row at a time, I want to look at all rows. Thats why I think if there was a way of loading all of the rows of the query into an array would then make it possible to use a VBA loop to colour each box each time it exectued a loop. - Hope this is clear

 

[Brianwarnock]

Nope, how does the form look at only one row of the query it is based on at a time?, if it does, why?, and what is the point of colour coding that row?

Brian

 

[PNGBill]

Think of the query as the raw data and the form, or report, as the display of this data.
It is the Form you would Colour, not the query.

Conditional Formatting will colour the Text Box Control depending on what value is in the control at the time.
You can set as many Rules as you like which would cater for your different times and colours.

Otherwise, a longer way would be to use the Form's Control Properties and have code to check what value is int he control and change the colour accordingly.

The Controls can only be Coloured after it is known what value it has and this value will change at any time.

Each query field can only hold one value.

Each control can only hold one value as it's record source.

from this record source, you can manipulate the control's properties and one way to do that is with Conditional Formatting.

 

[scooble]

Not sure if conditional formatting is the way to go.
Here;s the scenario; On a particular day, a salon has has lets say 12 appointments with lets say 4 team members, so for example, to keep things equal, each team member has 3 appointments. A query would create 4 records (one for each team member created by filtering on unique name). A form will display these records on a continuous form. For each record on the form, I would like to show all appointments for that particular team member visually represented as a bar (the bar consists of 37 small boxes all in line that represent 15 minute intervals between 9:00 and 18:00).
For this example, I would like to colour some of the boxes for team member Bob, therefore boxes that represent the times between 9:00 and 9:30 would be shaded as well as boxes between 10:30 and 12:00 and so on.

Like this, (X=Booked time, o=Free time)

Bob XXXoooXXXXXXXoooooooXXXXXXXXXoooooooo

Next Record

Team Member 'n' oooooo.....etc

 

[Brianwarnock]

So the 37 text boxes exist and have a value of X or o sounds ideal for conditional formatting.

Brian

 

[scooble]

where I'm stuck is converting the query above (3 appointments) and visually representing it on one row of 37 boxes. So far, I have only been able to represent one appointment on one row of boxes for each appontment.

Record #1 9:00-930
Record #2 10:30-12:00
Record #3 14:30-16:00

So what I want is;
Bob 111ooo2222222ooooooo333333333oooooooo - 3 appointments on one row

and not
Bob 111oooooooooooooooooooooooooooooooooo

Bob ooooooo2222222ooooooooooooooooooooooo

Bob oooooooooooooooooooo333333333oooooooo

 

[PNGBill]

I am a little confused.
Does your query return 37 fields?

A query has fields.

A form does not have fields. It has Controls and you can use your queries fields to be the Record Source of some of these controls.

What is the problem??

The query doesn't return 37 fields or you are not able to display these in your form's controls?

Maybe the real problem is your underlying data. Table /s.

The query should return something and then what the form control displays can be worried about after that.

 

[PNGBill]

Another option is that if an appointment is always 3 x 15 minute controls then just use the query to return Appointments and the start time.

The other two controls can get their value from the first of the three.

Bit of head scratching may be needed.

 

[Brianwarnock]

I am a little confused.
.

Only a little ? !!

I am totally confused as to what he has actually got.
Maybe he should post his DB though if its 2007 I wont be able to see it.

Brian