Feedback on Prototype Attendance Database, please

B

Banana

split with a cherry atop.
Local time
Today, 11:57
Joined
Sep 1, 2005
Messages
6,318
I had trouble figuring out how to 1) register a group of people for a class, 2) create a list of dates to meet for a class (which I call sessions), 3) track attendance per sessions.

Having looked for templates at Microsoft, I found plenty of Excel templates, but no Access. The closest is Student Registration template, which doesn't track attendance at all.

So I figured I'd make a prototype to help me understand how everything works. Since I got so much input from everyone, I figure I'd return the favor and perhap make it a sample database, especially for those who desire a spreadsheet-like data entry while maintaining a properly normalized data structure.

But I would prefer that other has tested and given feedbacks on the prototype before I put this in sample database forum (if that's okay with you admins) as this is my first time and I don't want to give others bad template.

So anyway, here's the prototype.

Note: The database is 100% undocumented, 100% error-handling free, and 100% unsecured. Use it wisely. :)

PS: The attachment will reference a extraneous library. If you are getting an error, clear the reference for MS Office 11 Web Components.

PSS: I knew I forgot something: There is still unsolved problem of correcting sorting the columns in datasheet view. While the underlying query correctly sorts the recordset, it seems to be ignored entirely in datasheet. If anyone has a solution, I'm all eyes here.
 

Attachments

Last edited:
Hi Banana, FYI I get an error on opening the frmClassSetup form; on the line:

Me.Controls(("Box" + Format$(IntX))).ColumnHidden = True

in Public Sub CreateRecordSource() where it can't find the project or library and it's highlighting the Format$ bit. I'm using A2K, and it a citrix setup, so maybe that's the problem.
 
Hmm. Although I'm using 2003 here, the database is saved in 2000 format, so you shouldn't be getting this error.

Maybe you need to check your library references? I know that I'm referencing DAO 3.6...
 
I checked references and DAO is checked along with some others (see attachment)

Any more ideas? Incidentally, thanks for posting this....I'm learning a lot from reading through your code. I was grappling with how to display crosstab results dynamically in a form for a not-too-dissimilar problem a while ago for a kennel/dog training database and gave up on that approach. Wish I'd seen this then...;)
 

Attachments

  • refs.JPG
    refs.JPG
    24.5 KB · Views: 520
Hmm... Your snapshot shows a Missing reference: Office Component 11.... Maybe if you cleared that library.

I only have five libraries:

Visual Basic for Applications
Mircosoft Office 11 Object Library
OLE Automation
Mircosoft DAO 3.6 Object Library
Mircosoft ActiveX Data Objects 2.1 Library

WRT your earlier search; this is precisely why I decided there had to be a template, as reading other MVP's posts left me stumped though I got the gist of what they suggested, but had trouble conceptizing the specifics. So.. here you go. :)


Silly me. I was looking at wrong Access database. This ONE has seven references, and it does reference Web Component that you're missing... I can't imagine why we would need to reference this... I just removed the two other references, and it worked just fine without those.... :confused:
 
Last edited:
With due respect to the patriarch of your attendee family...D'OH!

Good spotting...fixed the problem :)

And yes, I read a lot of MVP stuff that left me very confused in my earlier search (hence my change of direction). Heck, I'm still struggling to understand everything going on in this working example but given time and effort on my part, at least it's more achievable. Thanks again :)
 
No problem. I had a D'OH moment myself just earlier in another thread when my VBA wasn't working the way I expected it but after seeing the code in post, it hit me that I wasn't using my XSQL at all!

Yes, I should really document my code, but didn't for two reasons: 1) I don't want anybody get too excited and implement a piss-poor template without at least a couple of reviews (and critique) by other posters, especially when the author (e.g. me) has a vague idea of what he's doing, and 2) I was focusing more on getting everything working right and didn't want to lose my train of thought.

Once I know others thinks it's workable, I'll document it for sure and place it in sample forum. :)
 
Perhaps anyone can provide suggestions on how to get the datasheet to correctly sort by dates every time a new session is added?

Any other suggestions will be much appreciated as well.
 
I see that some has looked at the database already.

Does anybody have suggestion how it can be done better?
 
Interesting design! I love the two listboxes with transferring buttons; I use it literally in my database.

It seems to work well, as long you're satisfied with keeping the list of absences. I designed my database thus only because my users wanted attendance sheet in same fashion as one you see in Excel... Boo hoo, boy was it a tall order! I'd have been just content with such design as yours otherwise. Less work on my hands. :)

DOES anybody knows how to get the datasheet view's columns sorted in correct order? :confused:
 
Finally found something that could help with ordering the columns according to dates (which could be dynamic). The article exposes all hidden properties of datasheet.

Will need to create a query of the Session Dates all ordered, then use it as base to feed a given column's order property so it'll appear perfectly in orders date-wise.

Now where did I put that creeper...
 
I'm glad you found something for your issue. I was doing some research, but had found nothing. Thanks for the link. I'm sure it will come in handy at one point.

René
 
Banana, I have also spent a fair bit of time trying to figure that out for you. (Don't tell my boss!) ;) I even got as far as the ordered query you mentioned etc. I had figured out that the box sources were being set in the correct order but just not being 'displayed' in the naming sequence. Altering the tab order made no difference either.

My problem was finding a way to set the box'#' ordinal position in the columns. Your link gave me what I needed to make that final step.

I made some changes to your code and queries while initally trying to figure it out. But you could probably change some of it back as the key bit is just the line:

Me("Box" + Format$(IntX)).ColumnOrder = IntX - 1
 

Attachments

The problem is if you check on the checkbox, you will get error "Type Mismatch".

See, in the junction table for SessionAttendance, we have three fields, SessionID, AttendeeID and Attended. In order for me to find or add correct record, I need two pieces of information; SessionID and AttendeeID. Which is why my crosstab query used SessionID instead of SessionDate.

You changed the SessionID to SessionDate in the crosstab query, so the code no longer can find the SessionID that was 'hidden' behind the scenes in order to complete the junction record.

This is why I have this block:
Code: 
    With lrst
        qdf2![LSessionID] = rst(IntX - 1).Name
        Set lrst = qdf2.OpenRecordset
        Me("Label" + Format$(IntX)).Caption = lrst![SessionDate]
    End With

Note that the only thing being modified is the caption of the label associated with the control. But the controlsource refers to SessionID.

I'm getting confused with my own solution so I'm going to do something else and come back to it later.

Nonetheless, thanks so much for your kind effort!
 
As I said earlier, you might want to undo some of the code changes. ;) All that you need to make yours work is to take your unmodified prototype and insert the line:

Me("Box" + Format$(IntX)).ColumnOrder = IntX - 1
immediately after the existing line:
Me("Box" + Format$(IntX)).ControlSource = rst(IntX - 1).Name

I just put mine up to show I had spent some time monkeying around with (and oviously breaking) your cool example db :)
 
Okay. I lied. I got it to work, however.

CraigDolphin- thanks again; if not for you, I wouldn't have had solved this just recently. I initially was going to create *another* variables :eek: just to keep track of column's order but you showed that it didn't have to be so.

I then changed the query so it sorts based on the dates, assigns the SessionID while displaying sessiondate in correct order...

Here....
 

Attachments

CraigDolphin said:
Just to make you feel better, here's yours with only the only line added ;)
Click to expand...

It works because the SessionID are in order and synchronized to SessionDate. But suppose someone delete a Session? Or change the date? The IDs would be no longer synchronized and would thus show columns according to ID, but not dates (again because of underlying query).

That's one of other reason I asked for feedback; as I'm sure this could be done a bit more neatly. ;)
 
Ah....right you are. :) Good job! I think that the correct sorting issue definitely was worth the time investment though :)
 
Still, if not for you, I would have made it 5x more complicated than it should be! You helped me realize that I could simply modify the query so it returns a set of sessions for a class rather than one record for a specific sessionID, simplifying the process.
 
You must log in or register to reply here.

Similar threads

H
Beginner Seeking Help: Building an Access Database for Volunteer Fire Department
Replies
9
Views
487
arnelgp
arnelgp
D
Relationships
2 3 4
Replies
69
Views
1,561
LarryE
LarryE
G
Recommendation for MS Access Admin Database for SQL Server User Management
Replies
3
Views
396
GPGeorge
GPGeorge
T
please help me with db, stuck with option group
2 3 4
Replies
64
Views
1,611
TipsyWolf
T
E
Emmanuel Katto from Dubai : Seeking Advice on Front-End vs. Back-End Development for Cross-Platform Mobile Apps
Replies
8
Views
543
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom