There has to be a way - to iterate 1

[telephoto]

This has been bugging me for days now, I have forgotten half (at least) of all I thought I knew....

I am attempting to open a DAO recordset [rst] and write the contents into textboxes. The size of the recordset can vary, so:

moveFirst

For i = 1 To totalrecords 'from rst.RecordCount
Me.txt(i).visible = True
Me.txt(i).Caption = rst.Fields(i).Value
rst.MoveNext
Next i


This is not working as it will not compile, the report is "item not found in this collection.

I know it should be simple but please would some-one tell/show me where I am going wrong?

Telephoto

 

[dhookom]

I would typically expect code like this to fill controls for one record, not loop through records filling multiple copies of controls.

Consider naming the text boxes like txt0, txt1, txt2, txt3, etc. Then for one record your code might look like:

Dim intI as integer
For intI = 0 to rst.Fields.Count-1
   Me("txt" & intI) = rst(intI)
Next

If you want to display multiple records, then why not just bind the form to the table/query?


Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

Hi Duane, thankyou, I have now managed to set up the header of the report.

It goes back a while - and you responded to some of the first part in thread181-1639083 (that should be Club not clud) much earlier this year(a bowls database). flyboytim analysed what I wanted very well, and I've altered the tables to be in line with his recommendations.
Drawback comes when using a Yes/No checkbox to record availability, once I make the tables and link them I can't amend them.
Making one large table I can, but I finish up with a record for each person, for each date, where what I need is a record for each person and all their dates in one line.

It sounds like a crosstab query, but that won't accept a Yes/No as the "value" cell.
So I'm trying to build the report from scratch, but have it able to use four different sets of records.

Thanks for your help - I hope you receive as much as you give.

(Don't go away - I might be back!!)

T

 

[dhookom]

What's wrong with using a yes/no field as the value cell? You can sum, count, min, max, etc. A yes/no field is basically a very limited numeric field.

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

The query design window won't accept it.
T

 

[dhookom]

The query window won't accept what? I wouldn't have suggested you could use a yes/no field in the Value of a crosstab if I hadn't first tested it.

Can you at least describe your table(s) and fields?

It would really help if you provided some background regarding your question.

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

OK Duane, here goes:

First the crosstab, with names as row headings, and dates as column headings I put the True/False Availability field as the value. The Access response was:
"You tried to execute a query that does not include the specified expression "Availability" as part of an aggregate function."

Not seeing how I could get round this I tried to sidestep it - and trod straight in it.

BACKGROUND

The database lists all matches played in a season at the club.
There are tables for:
1. tblMembers holds recordID, names, telephone nos, and check box field for each of the competitions members play in.
2. tblDates holds each day of the season (late April - early Sept) with an ID
3. tblOpposition holds an ID field, a linked field to tblDates, opposition team, name of competition/league

From these tables it is easy to set up reports showing when teams play.
Having got that far I wanted to assemble a system to show who could play in specific competitions on any date. The report format would have dates across the top, names down the left and check boxes under each date. (This is the one giving trouble)

The way that I was suggested earlier this year was to set up a linking table between tblmembers and tblOpposition, a linking field to each, with a true/false field to show availability. (This was the original tblComposite, with link fields to tblMembers and tblOpposition, and an Availability True/False field)

So far so good.

When I attempted to produce a form based on those tables it would show me the availability boxes - but not let me amend them.
I finished up with a composite table with each record having complete details of one person and one date. This allowed me to enter their availabilites into the true/false field. This is the one I am trying to use as the report data. and the one the crosstab failed on.

The previous system had each date as a field for each member in tblmembers, this needed massive maintenance each year but was easy to use. Form and report control datasources and labels had to be set up afresh.
I am trying to cut down the maintenance, but the setup is really pushing me.

Any advice? If it would help can I post linking diagrams anywhere?

Telephoto

 

[dhookom]

Did you attempt to change the Total to Sum or Min or Max in the Availability column in the crosstab query design?

You mention the table "composite" as the possible source in your crosstab but you didn't mention it with your tables.

What is the exact table(s) that store the availability and how do you expect to display this in a report? Are you looking for 1 week or 2 or more? Should the results be every date or week or what?

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

Crosstab - no attempt to change anything

I am attempting to cut down annual setup labour
tblComposite is a bastard table easily set up by query maketable from tblmembers and tblopposition - no links to anything else.
It holds all information, but only one date's availability per record. This was the only way I could make a form to allow me to enter each member's availability. Believe me, I know it's horrible, but I couldn't think of any other way.


The records I have on tblComposite look like

Name Availability Date Competition
Alan tick 30 Apr BHP
Alan 2 May BHP
Alan tick 9 May BHP
Alan tick 14 May BHP
Alan 17 May BHP
Alan tick 22 May BHP
Brian tick 30 Apr BHP
Brian tick 2 May BHP
Brian tick 9 May BHP
Brian 14 May BHP
Brian tick 17 May BHP
Brian 22 May BHP
Chris 30 Apr BHP
Chris tick 2 May BHP
Chris tick 9 May BHP
Chris 14 May BHP
Chris 17 May BHP
Chris 22 May BHP



What I'm looking for is

name\date 30 Apr 2 May 9 May 14 May 17 May 22 May
Alan tick tick tick tick

Brian tick tick tick tick

Chris tick tick



There are four different competitions, with anywhere between 11 and 29 different dates for each one. (Obviously SQL or queries can be used to filter data so that only one competition is on one report.

Hope that clears up the detail

T

 

[dhookom]

I couldn't force myself to type in "Name" and "Date" as field names. It's against my principles to use function names and other reserved words as object names.

This crosstab worked great for me:

TRANSFORM Min(tblComposite.Availability) AS MinOfAvailability
SELECT tblComposite.PersonName
FROM tblComposite
GROUP BY tblComposite.PersonName
PIVOT tblComposite.TheDate

[tt][blue]
PersonName 4/30/2011 5/2/2011 5/9/2011 5/14/2011 5/17/2011 5/22/2011
Alan -1 0 -1 -1 0 -1
Brian -1 -1 -1 0 -1 0
Chris 0 -1 -1 0 0 0
[/blue][/tt]
I don't think you need the composite table. I would actually use the solution described in faq703-5466. You would need to change the monthly interval to daily.

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

Thanks for your attention

I'll check these out tomorrow.

T

PS Please. name is not the actual field name! try firstname and surname, so the crosstab row heading would have to be a concatenated expression

 

[dhookom]

Please... when someone asks for table and field names, don't make these up. It's especially troubling/frustrating when you make up names that are reserved words.

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

Your criticism is accepted. I now know better. In mitigation I was just trying to make it easier to follow.

Crosstab works fine, thank you, I didn't know a "min" was required.
There are several different competitions, and query filters work as expected.

I looked at your referred link, but I am looking at up to 29 events (dates). (BHP this year was 11 dates, and the evening competition was 29 - two others in the middle). The spacing of the dates is irregular, does this compromise its use?
I can (and will) make use of the first part of this thread in setting dates in vertical format label, to maximise use of the page with check boxes.

Drawback: the checkboxes have to have their datasource set to the "field" of the query, so use of the one report for all competitions is compromised. This also increases annual maintenance - tho' not as much as before.
I had hoped to amend the recordsource in the report open event.
You are going to tell me I'm wrong - and I am open to comment.
Am I going to have to set the datasource per record per field for each checkbox as the report opens?

I can do a recordcount and restrict the visibilty of column labels and extra checkboxes, but do I need an "On error resume next" somewhere?
I could accept that the report could be produced completely programmatically each time it is called - but that is beyond me.

For what you have done so far thank you, and please accept the star, do you have any other comments while I still have some hair to pull out?

T

 

[dhookom]

From your specifics, your dates aren't consecutive and you don't want to display consecutive dates across the top of the report. It also sounds like you might expect to display up to 29 dates across the top of your report.

There is a solution for this at crosstab reports. It's a bit complex but there is a table or something in the demo that explains how it works.

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

From your first line, yes, yes and yes.

The solution works a treat (after a bit of juggling!) I wish I had thought of this 3 years ago.

I am now a happy bunny - to make me an ecstatic bunny one more thing.

telephone numbers are listed separately in tblMembers. Is there any way I can add them after the row name from the crosstab?


Date1 date2 date3
fullname1
teleno1

fullname2
teleno2


or is this going to need a sub-form after each record?

Many thanks again for your Access97 answer - if I knew where you were I'd buy you a drink!

T

 

[dhookom]

If there is only one telephone number per fullname, you should be able to add the field to your crosstab as a row heading.

I don't expect you are anywhere near Minnesota so you may have to make a small contribution to your favorite charitable organization. Cheers...

Duane
Hook'D on Access
MS Access MVP