Form question

[belsha]

I have 3 tables, tblPhysician, tblMeeting, tblAttendance. tblAttendance has the joint primary keys of tblPhysician (physID) and tblMeeting (mtgID), along with a Y/N field for "in attendance". What the users want is a form that lists the date of the meeting,each physicians name (the names are static), and has a yes/no checkbox for "in attendance". (Which of course insinuates unchecked was not in attendance). Using the current table design, what is the best way to achieve this? Thanks!

 

[Uncle Gizmo]

Dear Pat,
Is it possible to have a command button that selects "all" the names in the list box, allowing you to "uncheck" those not there?

 

[Uncle Gizmo]

Long time since I worked with list boxes, but here it is:

Private Sub cmdSelectAll_Click()
On Error GoTo Err_cmdSelectAll_Click

Dim x As Integer
Dim intLstCount As Integer
    
    Let intLstCount = Me!lstMembers.ListCount - 1
        For x = 0 To intLstCount
        Me!lstMembers.Selected(x) = True
    Next x

Exit_cmdSelectAll_Click:
    
    Exit Sub

Err_cmdSelectAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelectAll_Click
    
End Sub

 

[belsha]

Thanks Pat, your solution was perfect and the additional aspects you addressed made for a great learning experience!

 

[belsha]

Next phase. I have a case table (PK case#). At each meeting, (PK MtgDate,MDID) several cases are reviewed (and a case can be reviewed at more than one meeting). Best way to get something like qAttendance filtered by a date the user enters that lists the cases the attending docs who reviewed them on that date? Looks like a many to many to me, and I'm a bit shaky on those. Thanks!

 

[accessdummy]

Error

Hi,
apparently your 97 version has some coding problem which I don't know how to rectify.

Help me pls.
:~~~~(
I'm on the verge of breakdown

 

[belsha]

Attendance percentage

Using this DB is there a way to incorporate a percentage attending into the qMembersAttending and accompanying frmMembersAttending? My equivalent of tblMailingList has a field for inactive date (but I want the record to remain, just show inactive), so I would like to also show a percentage attending each month but use as the denominator the total count of "members" excluding the inactive ones for that particular month? Thanks again for any help!

 

[belsha]

example

Am attaching an example of my problem. For the first meeting, 3 out of 4 active members (at the time) attended = 75%. Next meeting 2 out of 2 active (at that time) attended = 100%. Active Member query only addresses active now, not at a particular date in time. How to account for that ?(please see form). Also, a report similar to your form with graph is required tracking this percentage by month, as well as the N. How to do this with the varying total MD count active per month? Thanks!

 

[belsha]

Graph question

In this database, if I add a parameter in the meeting date field of the query to select dates in a period, then the attached graph starts asking for a parameter for each meeting date, and tries to give me a number of graphs, although the query itself shows the meeting date and number of attendees fine. Since the graph in the original design will get huge in time, is there any way to incorporate a date range for the graph to represent? Would this be code behind the form, or a query based off the first query (tried that, no luck)???Thanks!

 

[mbertuol]

Update records on same table

Pat,

I have used your example to set up a functionality that I need to implement. I have added a field "Status" to the tblMailingList and created a new form (frmStatus). I need to update multiple records with a given value.

Please take a look at the "frmStatus" and its coding: it is not working.

Thanks in advance for your help!

Mauro

 

[Uncle Gizmo]

The error appears to be here >>> Set qd = dbs.QueryDefs!qStatus <<<<<

Should you have a query called "qStatus" ?

 

[mbertuol]

The version I posted had the problem with the query name: should be "qMemberStatus". Nevertheless, I get error 3164 with the code the the DB attached. If I remove the line

rst!MailingListID = ctlRef.ItemData(i)

only the first record is updated, regardless it is selected or not or how many records are selected.

Any hint?

 

[cturner]

Createing seperate form from your selected list

This is almost like what I need for my DB with one difference. After you make the selection of the "members" I would like for thos you selected to display in a datasheet form with another field "Visitors" where I can enter the number of visitors they brought. Is this possible?

 

[MeJayne]

I have one question on this sample. The query "qAttendanceForMember" asked for the MailingListID, and returns AttendanceDates for that particular member. My question is this. How do I "reverse" this and have the query ask for a particular AttendanceDate, and return a list of the Members who attended that meeting.

 

[denileigh]

Thanks!

That is the exact DB I had a copy of and was playing with......it is not updating 2 tables though....this is frying my brain! Not that it wasn't already!

 

[willymec]

Hope this isn't to late? or I need to open a new thread?

Trying to build on this database example here. It will almost work for my needs.
Trying to build database to keep track of people that attend our multiple Sunday school classes.

There would be
People - Members, Guests and inactive - FirstName, LastName, DOB and PH#
Classes - New Members, Experienced Adults, Sr Adults, Jr High and etc
Teachers - Prefix, FirstName and LastName

Of each class attendance forms would pull people based on past and new attending each specific class, and mark as attended or not. and be able to add a new person (Guest). As the example database works almost perfect, except I need to add teachers and they can move to different classes, and the people can move to different classes. So I need to be able to choose the teacher and class for that date.

So I need this database to track each Class each Sunday, who attended and who the teacher was. Each class will have a front end db for there perspective class.

Thank you for the help

Bill