Run an append query and iterating down a listbox (Access2K)

Topic

New Reply

I have a form called frmScheduledMeetings. On this form are three listboxes. lstMeetings is a list of meetings that have been scheduled for the year. lstMeetingDate is the the list of dates that the meeting will take place (some meetings are scheduled a few times a year). lstAttendees lists all clients who will be attending the meeting. When a user click on a meeting in lstMeetings, all the dates that this meeting is scheduled to take place will appear in the lstMeetingDate listbox. When a user select a date from the lstMeetingDate listbox, the lstAttendees will get filled in with clients (clientid) who has signed up to attend meeting XYZ on date mm/dd/yyyy. For each meeting, a staff maintains separate notes for the group as a whole as well as for each individual client. Occasionally though, there is a group note that the staff would like to be shared by all who attended that session. When a user double clicked on a selected date on lstMeetingDate, a blank frmGroupNote form will open. What I would like to know is, what is the best way to copy a group note which has been deemed as

Reply | Quote

Replies

Do you really need to copy the group note to all individual client notes? It would mean a lot of redundant information storage, unless you need the copies of the group note to be edited separately afterwards.

Reply | Quote

i have wondered about this too. but the the users are of the opinion that it is important to them to be able to copy the notes. for e.g. sometimes a participant will say something that the clinician think it is important for all attendees to share and keep in their notes. and you are right, the group note as well as the client note will be edited separately afterwards too.

Reply | Quote

You can create a query based on TblGroupNotes and on the record source of lstAttendees. Set the criteria for the NoteID field from TblGroupNotes to the NoteID from frmGroupNote, and make sure that you have the same criteria for the attendees as those used in the record source of lstAttendees.
Change this query into an append query, and specify TblClientNotes as target.
Add Acct, MeetDate, Notes, NoteDate and StaffID from TblGroupNotes to the query grid, as well as ClientID from the record source of lstAttendees. Specify the corresponding fields as target (you only need to specify Note as target for Notes). I assume that NoteID is an AutoNumber field, so you should not include that in the query – Access will populate it automatically.

Reply | Quote

Hello Hans,
I took up your suggestion. It works, somewhat when there is only one person attending a session. If there is more than one person attending the same meeting I get an error message when trying to copy the notes to all clients attending the session. How can run the append query for as many times as there are attendees in the lstAttendees listbox i.e. how can enumerate through the listbox? Am I making sense? I hope I am.

Reply | Quote

When you get an error message and ask help about it, it is useful to quote the error message in your post, so that we don’t have to guess.
– What is the error message you get?
– What is the Row Source Type of lstAttendees?
– What is the Row Source of lstAttendees?

Reply | Quote

Hello Hans,

The row source type for lstAttendees is Table/Query

The row source is:

SELECT dsdtcmas.clientid, Trim([lastname]) & “, ” & Trim([firstName]) AS ClientName
FROM dsdtcmas INNER JOIN dsdtchgp ON dsdtcmas.clientid = dsdtchgp.clientid
WHERE (((dsdtchgp.meeting)=[Forms]![frmScheduledMeetings]![lstMeetings]) AND ((dsdtchgp.MeetingDate)=[Forms]![frmScheduledMeetings]![lstMeetingDate]))
ORDER BY Trim([lastname]) & “, ” & Trim([firstName]);

I was trying to run the append query from the query design grid with both frmScheduledMeetings and frmGroupNotes open. The append query works really well when there is only one person is listed as attending the meeting on the specified date in lstAttendees. When there are more than one attendees, the error message that I get is

Reply | Quote

Try this as criteria for the ClientID field in tblClientNotes:

In (SELECT dsdtcmas.clientid FROM dsdtcmas INNER JOIN dsdtchgp ON dsdtcmas.clientid = dsdtchgp.clientid WHERE (((dsdtchgp.meeting)=[Forms]![frmScheduledMeetings]![lstMeetings]) AND ((dsdtchgp.MeetingDate)=[Forms]![frmScheduledMeetings]![lstMeetingDate])))

Reply | Quote