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!

 

[Pat Hartman]

As it happens, I was just making such a form for the Connecticut Access Users Group. See if you like the way I did it. This db has several interesting bits of example code.

1. A form that uses a multi-select list box to show a list of members. The members who are present are selected. A button is pressed and a code loop reads the selected items from the listbox and creates attendance records.
2. The form that shows the most frequently changed data contains a subform in datasheet view. This datasheet has a colored background and is resized as the form is resized. There is a dbl-click event associated with the last name that opens a form that contains all the columns of the members table.
3. There is a form that contains a graph that shows attendance from month to month.

OLD version - look below for a newer one.

 

[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?

 

[Pat Hartman]

This is a work in progress so I'll post the latest version. In this copy, I changed the MultiSelect type from Simple to Extended for you. The Extended property works more like what you are probably used to. Use
cntl-clk to select separate single items or shft-clk to select a range of items. So, to satisfy your requirement, use shft-clk and select all the items. Followed by cntl-click to de-select some items.

Or, you can of course write your own code to select all the rows with a button.

Bug fix.

 

[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!

 

[Pat Hartman]

Glad it worked for you Here's the latest version.

 

[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!

 

[Pat Hartman]

Here'a an A97 version.

 

[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

 

[Pat Hartman]

I just converted it using the menu options and since I don't have 97 loaded, I can't determine what the problem is precicsely but I'm going to guess that the problem is with the switchboard which was probably created with A2K. To fix the problem you'll need to delete the switchboard form and force Access to build a new one.

1. Rename the table [Switchboard Items] to [Switchboard Items Keep]
2. Delete the form named [Switchboard]
3. Open the Switchboard manager. Since it won't find the table or the form, it will create new ones. Just put one item in the menu. It doesn't matter what it is and close the Switchboard manager.
4. Delete the [Switchboard Items] table.
6. Rename the [Switchboard Items Keep] to [Switchboard Items].

There is code in the system generated switchboard for A2k that will not run in A97. Sorry.

If that fixes the problem for you, please upload the "fixed" version if you would.

 

[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!

 

[Pat Hartman]

Create a query that selects "active" members. Add a textbox that calculates the percentage by dividing the results of one DCount() into another.

=DCount("*", "tblAttendance", "MeetingDate = Date()")/Dcount("*","qryActiveMembers")

You can't calculate a percent in a detail query. Queries either produce summarized results or individual records but NEVER both.

 

[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!

 

[Pat Hartman]

Your active members query needs to include criteria that compares the TerminationDate (or whatever you call it) to the meeting date to determine status rather than using a status code.

 

[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!

 

[Pat Hartman]

You need to use a form to supply the parameters rather than prompting for them. So use:

Where SomeField = Forms!YourForm!SomeField;

rather than

Where SomeField = [Enter some value]

 

[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?