update file for current record only

[append records to subfile current record]

I have a database. My main table is a STUDENTS file. Each week I run an
update query to add a new record to the ATTEND file for each student, adding
in a week no (WEEKNO) and a date(EMADATE). I pick up the values I want from a
table tblDATEFILE

When students enter the system after the start of term I would like to be
able to add in N records, ie. W01, W02, W03 … WN (WEEKNO & EDATE) for the
current student only.

On the STUDENTS form I have a button ‘Insert Weeks’ and I have set an Event
Procedure that only does
DoCmd.OpenQuery “qryADD NEW WEEKâ€, acViewNormal, acEdit

Here is the SQL from my update query:
INSERT INTO tblEMAATTEND ( STID, WEEKNO, EMADATE )
SELECT [tblEMA STUDENTSFILE].STID, tblDATEFILE.WEEKNO, tblDATEFILE.EMADATE
FROM tblDATEFILE, [tblEMA STUDENTSFILE]
WHERE (((tblDATEFILE.WEEKNO)<=[ENTER WEEK NO EG W20]));

But it will add new weeks into all of the students file. How can I get it
just to do it for the current student? How can I get say the unique students
name, SFNAME (or even the unique ID), in as a criteria into the query?

I know that this is simple but I am just learning from reading books. I
really don’t know any VBA.

Patricia Lennon

 

[Ken Snell \(MVP\)]

Assuming that your STUDENTS form has the STID field in its RecordSource
query, you can add a criterion to your update query:

INSERT INTO tblEMAATTEND ( STID, WEEKNO, EMADATE )
SELECT [tblEMA STUDENTSFILE].STID, tblDATEFILE.WEEKNO, tblDATEFILE.EMADATE
FROM tblDATEFILE, [tblEMA STUDENTSFILE]
WHERE (((tblDATEFILE.WEEKNO)<=[ENTER WEEK NO EG W20]) AND
(([tblEMA STUDENTSFILE].STID) = Forms!STUDENTS!STID));

--

Ken Snell
<MS ACCESS MVP>




"append records to subfile current record" <append records to subfile
current (e-mail address removed)> wrote in message
news:[email protected]...

 

[append records to subfile current record]

That was great - sorry for not replying on Friday - just very busy. Thanks
again