how do I set up a registration form

Hilarys,

I think the problem is in the reference to your form control. To test it,
open your form to a certain record. For sake of example, let's assume that
the SessionID equals 15.

Replace the reference to the :

....
WHERE (((RegistrationSessions.SessionID)=15));"
....

Return to your form after saving the code to the same record and run the code.

If it inserts records for each pupil, you've found the problem. Check the
name of the main form and the name of the main form *control* that is bound
to SessionID.

I also think that since you might make a mistake in typing the date, you
might want to move the code to a command button rather than the SessionDate
AfterUpdate event.

Hope that helps.
Sprinks

"Hilarys" wrote:

Sorry meant to say the warning message says 0 records are bing appended not 1.

"Hilarys" wrote:

I'm getting excited ( sad isn't it!) because I think we may be getting there
but it still doesn't work...sorry I must be driving you mad!
I checked all my spellings etc and they are OK and I set up the append
query as sugested and it worked!!! Although it did ask me to enter a
parameter value for Forms!Cont_Regist_Form!SessionID rather than taking the
value from the open form.Could this be the problem?
I then cut and pasted the SQL statement into the code adding quotation marks
etc as instructed . I left the warning messages on to see what wa being
appended and it said only one record was being appended each time I updated
the form.
Thank you so much for your help with this.
Hilary


"Sprinks" wrote:

Hilarys,

The one-to-many relationship between RegistrationSessions and Registration
is correct. For each date, there will be many students that attend.

I tested the code I posted. It works correctly, so I think it could be:

- The name of the control that contains the SessionID in the Where clause
is named something differently, such as txtSessionID

- The name of the form is misspelled or incorrect. Its name implies that
it is a continuous form, whereas since there are no detail records when you
press the button, you must get the value of SessionID from a main form control

If neither of these solves the problem, I have to believe there is some
other misspelling. You could try building the SQL string from scratch, using
Query By Design view. Create the Select statement, test it, change the query
type to Append, typing in Registration as the table. Since you have the same
field names, Access should figure out which fields you want to Append to.
Then type in the selection criteria, referencing your form control.

With the form open so that a value exists in the main form's SessionID
control, execute the query. Open the Registration table to verify that
records were inserted for each pupil with that SessionID. If the query
executes correctly, switch to SQL view and cut and paste the SQL statement
into your code. Add quotation marks around each substring, and
concantenation and line-continuation characters after each but the last line.
Be careful that you include a space at the end of each substring but the
last, e.g.:

strSQL = "INSERT INTO Registration (SessionID, PupilID) " & _
NOT
strSQL = "INSERT INTO Registration (SessionID, PupilID)" & _

Hope that helps.
Sprinks




"Hilarys" wrote:

Still not working!
I have changed the coding as you said (see below) and I now no longer get
the error messages but on putting a new date in the main form the subform
does not change and only shows a single un- updated record.
Could it be that my joins between the tables are incorrect? I have a one to
many with enforced integrety and Cascade updated fields and Cascade deleted
records ticked between Student Details and Registration PupilID field and a
one to many between RegistrationSessions and Registration via SessionID. I
feel the link between RegistrationSessions and Rgistration should be Many to
One but it won't let me link it in this way as the SessionID in
RegistraionSessions is the primary key.
Sorry if this is very basic suff but I have got in a real muddle!
Hilary

Private Sub SessionDate_AfterUpdate()
Dim strSQL As String

'Turn warnings off
DoCmd.SetWarnings False

'Assign SQL string
strSQL = "INSERT INTO Registration (SessionID, PupilID) " & _
"SELECT RegistrationSessions.SessionID, [Student Details].PupilID " & _
"FROM [Student Details], RegistrationSessions " & _
"WHERE
(((RegistrationSessions.SessionID)=[Forms]![Cont_Regist_Form]![SessionID]));"

'Run query & requery the subform, display new records, and turn warnings
back on
DoCmd.RunSQL strSQL
Me![Cont_Regist_Subform].Requery
DoCmd.SetWarnings True
End Sub


"Hilarys" wrote:

I want to set up a registraion form where you can see all the pupils in a
class listed on the form so that you can go down the list ticking off whether
they are present or away on a particular date.
I have 2 tables STUDENT DETAILS with PupilID, Name, etc and REGISTRATION
with RegisDateID,RegisDate, PupilID, Absent_Present linked via the PupilID
field.
I presume I need something like a form with a date field which when fillled
in will automatically update the RegisDate for all the students records and
then a contiuous subform with all the students names with a tick box next to
each name to check off if they are present or not but I need help!

Brilliant, I've attached it to a command button and it all works now! Many
thanks for yuor patience.
Hilary

"Sprinks" wrote:

Hilarys,

I think the problem is in the reference to your form control. To test it,
open your form to a certain record. For sake of example, let's assume that
the SessionID equals 15.

Replace the reference to the :

...
WHERE (((RegistrationSessions.SessionID)=15));"
...

Return to your form after saving the code to the same record and run the code.

If it inserts records for each pupil, you've found the problem. Check the
name of the main form and the name of the main form *control* that is bound
to SessionID.

I also think that since you might make a mistake in typing the date, you
might want to move the code to a command button rather than the SessionDate
AfterUpdate event.

Hope that helps.
Sprinks

"Hilarys" wrote:

Sorry meant to say the warning message says 0 records are bing appended not 1.

"Hilarys" wrote:

I'm getting excited ( sad isn't it!) because I think we may be getting there
but it still doesn't work...sorry I must be driving you mad!
I checked all my spellings etc and they are OK and I set up the append
query as sugested and it worked!!! Although it did ask me to enter a
parameter value for Forms!Cont_Regist_Form!SessionID rather than taking the
value from the open form.Could this be the problem?
I then cut and pasted the SQL statement into the code adding quotation marks
etc as instructed . I left the warning messages on to see what wa being
appended and it said only one record was being appended each time I updated
the form.
Thank you so much for your help with this.
Hilary


"Sprinks" wrote:

Hilarys,

The one-to-many relationship between RegistrationSessions and Registration
is correct. For each date, there will be many students that attend.

I tested the code I posted. It works correctly, so I think it could be:

- The name of the control that contains the SessionID in the Where clause
is named something differently, such as txtSessionID

- The name of the form is misspelled or incorrect. Its name implies that
it is a continuous form, whereas since there are no detail records when you
press the button, you must get the value of SessionID from a main form control

If neither of these solves the problem, I have to believe there is some
other misspelling. You could try building the SQL string from scratch, using
Query By Design view. Create the Select statement, test it, change the query
type to Append, typing in Registration as the table. Since you have the same
field names, Access should figure out which fields you want to Append to.
Then type in the selection criteria, referencing your form control.

With the form open so that a value exists in the main form's SessionID
control, execute the query. Open the Registration table to verify that
records were inserted for each pupil with that SessionID. If the query
executes correctly, switch to SQL view and cut and paste the SQL statement
into your code. Add quotation marks around each substring, and
concantenation and line-continuation characters after each but the last line.
Be careful that you include a space at the end of each substring but the
last, e.g.:

strSQL = "INSERT INTO Registration (SessionID, PupilID) " & _
NOT
strSQL = "INSERT INTO Registration (SessionID, PupilID)" & _

Hope that helps.
Sprinks




"Hilarys" wrote:

Still not working!
I have changed the coding as you said (see below) and I now no longer get
the error messages but on putting a new date in the main form the subform
does not change and only shows a single un- updated record.
Could it be that my joins between the tables are incorrect? I have a one to
many with enforced integrety and Cascade updated fields and Cascade deleted
records ticked between Student Details and Registration PupilID field and a
one to many between RegistrationSessions and Registration via SessionID. I
feel the link between RegistrationSessions and Rgistration should be Many to
One but it won't let me link it in this way as the SessionID in
RegistraionSessions is the primary key.
Sorry if this is very basic suff but I have got in a real muddle!
Hilary

Private Sub SessionDate_AfterUpdate()
Dim strSQL As String

'Turn warnings off
DoCmd.SetWarnings False

'Assign SQL string
strSQL = "INSERT INTO Registration (SessionID, PupilID) " & _
"SELECT RegistrationSessions.SessionID, [Student Details].PupilID " & _
"FROM [Student Details], RegistrationSessions " & _
"WHERE
(((RegistrationSessions.SessionID)=[Forms]![Cont_Regist_Form]![SessionID]));"

'Run query & requery the subform, display new records, and turn warnings
back on
DoCmd.RunSQL strSQL
Me![Cont_Regist_Subform].Requery
DoCmd.SetWarnings True
End Sub


"Hilarys" wrote:

I want to set up a registraion form where you can see all the pupils in a
class listed on the form so that you can go down the list ticking off whether
they are present or away on a particular date.
I have 2 tables STUDENT DETAILS with PupilID, Name, etc and REGISTRATION
with RegisDateID,RegisDate, PupilID, Absent_Present linked via the PupilID
field.
I presume I need something like a form with a date field which when fillled
in will automatically update the RegisDate for all the students records and
then a contiuous subform with all the students names with a tick box next to
each name to check off if they are present or not but I need help!