how do I set up a registration form

[Guest]

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!

 

[Guest]

Hilarys,

The issue is that there is a many-to-many relationship between
StudentDetails and Registration--students attend on multiple days; multiple
students attend on any given day.

To represent this relationship, you need an intermediate table, in a
one-to-many relationship with each of the others:

StudentDetails
--------------------
StudentID AutoNumber (PK)
FName Text
LName Text
....etc.

Sessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegisDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No

Base your main form on Sessions, and your continuous subform on
Registration, linked on SessionID. But, ideally, you'd like to populate the
subform with all of your students when you create a new Session record. One
way to do this is to run an insert query in the AfterUpdate event of the
SessionDate field on the main form and then requery your subform:

Dim strSQL As String

‘ Turn warnings off
DoCmd.SetWarnings False

‘ Assign SQL string
strSQL = “INSERT INTO Registration ( SessionID, StudentID ) “ & _
“SELECT Sessions.SessionID, [Student Details].StudentID “ & _
“FROM [Student Details], Sessions “ & _
“WHERE (((Sessions.SessionID)=[Forms]![YourMainForm]![SessionID]));â€

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

Hope that helps.
Sprinks

 

[Guest]

Probably being very thick as I don't know much about coding but when I cut
and pasted your code into the AfterUpdate event Visual basic box (after
putting in the correct form names etc) it came up with a "Compile
error:Expected: End of statement " and then "Compile error: Syntax error".
Help!! I think I need idiot proof instructions.

Hilarys,

The issue is that there is a many-to-many relationship between
StudentDetails and Registration--students attend on multiple days; multiple
students attend on any given day.

To represent this relationship, you need an intermediate table, in a
one-to-many relationship with each of the others:

StudentDetails
--------------------
StudentID AutoNumber (PK)
FName Text
LName Text
...etc.

Sessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegisDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No

Base your main form on Sessions, and your continuous subform on
Registration, linked on SessionID. But, ideally, you'd like to populate the
subform with all of your students when you create a new Session record. One
way to do this is to run an insert query in the AfterUpdate event of the
SessionDate field on the main form and then requery your subform:

Dim strSQL As String

‘ Turn warnings off
DoCmd.SetWarnings False

‘ Assign SQL string
strSQL = “INSERT INTO Registration ( SessionID, StudentID ) “ & _
“SELECT Sessions.SessionID, [Student Details].StudentID “ & _
“FROM [Student Details], Sessions “ & _
“WHERE (((Sessions.SessionID)=[Forms]![YourMainForm]![SessionID]));â€

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

Hope that helps.
Sprinks

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!

 

[Guest]

Hilarys,

My most likely candidate error would be that you accidentally deleted one of
the spaces at the end of one of the lines in the strSQL assignment statement.

The code is concatenating (joining) several substrings together with the &
operator. The _ character tells the compiler that this statement is being
continued on the next line.

Please post your AfterUpdate event procedure, including the Sub...End Sub
lines.

Sprinks

Probably being very thick as I don't know much about coding but when I cut
and pasted your code into the AfterUpdate event Visual basic box (after
putting in the correct form names etc) it came up with a "Compile
error:Expected: End of statement " and then "Compile error: Syntax error".
Help!! I think I need idiot proof instructions.

Hilarys,

The issue is that there is a many-to-many relationship between
StudentDetails and Registration--students attend on multiple days; multiple
students attend on any given day.

To represent this relationship, you need an intermediate table, in a
one-to-many relationship with each of the others:

StudentDetails
--------------------
StudentID AutoNumber (PK)
FName Text
LName Text
...etc.

Sessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegisDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No

Base your main form on Sessions, and your continuous subform on
Registration, linked on SessionID. But, ideally, you'd like to populate the
subform with all of your students when you create a new Session record. One
way to do this is to run an insert query in the AfterUpdate event of the
SessionDate field on the main form and then requery your subform:

Dim strSQL As String

‘ Turn warnings off
DoCmd.SetWarnings False

‘ Assign SQL string
strSQL = “INSERT INTO Registration ( SessionID, StudentID ) “ & _
“SELECT Sessions.SessionID, [Student Details].StudentID “ & _
“FROM [Student Details], Sessions “ & _
“WHERE (((Sessions.SessionID)=[Forms]![YourMainForm]![SessionID]));â€

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

Hope that helps.
Sprinks

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!

 

[Guest]

Hi
These are the details of my tables and coding.


Student Details
--------------------
PupilID AutoNumber (PK)
FName Text
LName Text
....etc.

RegistrationSessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegistrationDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No
I have created a main form based on RegistrationSessions table with a
subform called SessionsSubform based on Registration Sessions query.
In the AfterUpdate in the SessionsDate field in the main form I have done an
Event proceedure as follows

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]![RegistrationSessionsForm]![SessionID]));â€

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

but it just comes up with "compile errors" etc.
sorry if I am doing something very basic wrong!
Hilary

Hilarys,

My most likely candidate error would be that you accidentally deleted one of
the spaces at the end of one of the lines in the strSQL assignment statement.

The code is concatenating (joining) several substrings together with the &
operator. The _ character tells the compiler that this statement is being
continued on the next line.

Please post your AfterUpdate event procedure, including the Sub...End Sub
lines.

Sprinks

Probably being very thick as I don't know much about coding but when I cut
and pasted your code into the AfterUpdate event Visual basic box (after
putting in the correct form names etc) it came up with a "Compile
error:Expected: End of statement " and then "Compile error: Syntax error".
Help!! I think I need idiot proof instructions.

Hilarys,

The issue is that there is a many-to-many relationship between
StudentDetails and Registration--students attend on multiple days; multiple
students attend on any given day.

To represent this relationship, you need an intermediate table, in a
one-to-many relationship with each of the others:

StudentDetails
--------------------
StudentID AutoNumber (PK)
FName Text
LName Text
...etc.

Sessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegisDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No

Base your main form on Sessions, and your continuous subform on
Registration, linked on SessionID. But, ideally, you'd like to populate the
subform with all of your students when you create a new Session record. One
way to do this is to run an insert query in the AfterUpdate event of the
SessionDate field on the main form and then requery your subform:

Dim strSQL As String

‘ Turn warnings off
DoCmd.SetWarnings False

‘ Assign SQL string
strSQL = “INSERT INTO Registration ( SessionID, StudentID ) “ & _
“SELECT Sessions.SessionID, [Student Details].StudentID “ & _
“FROM [Student Details], Sessions “ & _
“WHERE (((Sessions.SessionID)=[Forms]![YourMainForm]![SessionID]));â€

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

Hope that helps.
Sprinks


:

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!

 

[Guest]

Hilarys,

My apologies--I've never seen this issue before. I cut and pasted your code
into a new module, and noticed the single and double quotation characters
were not correct. I think this happened because I edited the code I gave you
in MS Word.

To eliminate the compiler errors, replace all of the single and double quote
characters, and make sure any code comments are either on a single line or
have a single quote character preceding them.

Sprinks

Hi
These are the details of my tables and coding.


Student Details
--------------------
PupilID AutoNumber (PK)
FName Text
LName Text
...etc.

RegistrationSessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegistrationDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No
I have created a main form based on RegistrationSessions table with a
subform called SessionsSubform based on Registration Sessions query.
In the AfterUpdate in the SessionsDate field in the main form I have done an
Event proceedure as follows

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]![RegistrationSessionsForm]![SessionID]));â€

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

but it just comes up with "compile errors" etc.
sorry if I am doing something very basic wrong!
Hilary

Hilarys,

My most likely candidate error would be that you accidentally deleted one of
the spaces at the end of one of the lines in the strSQL assignment statement.

The code is concatenating (joining) several substrings together with the &
operator. The _ character tells the compiler that this statement is being
continued on the next line.

Please post your AfterUpdate event procedure, including the Sub...End Sub
lines.

Sprinks

Probably being very thick as I don't know much about coding but when I cut
and pasted your code into the AfterUpdate event Visual basic box (after
putting in the correct form names etc) it came up with a "Compile
error:Expected: End of statement " and then "Compile error: Syntax error".
Help!! I think I need idiot proof instructions.

:

Hilarys,

The issue is that there is a many-to-many relationship between
StudentDetails and Registration--students attend on multiple days; multiple
students attend on any given day.

To represent this relationship, you need an intermediate table, in a
one-to-many relationship with each of the others:

StudentDetails
--------------------
StudentID AutoNumber (PK)
FName Text
LName Text
...etc.

Sessions
--------------------
SessionID AutoNumber(PK)
SessionDate Date/Time

Registration
-------------------
RegisDateID AutoNumber (PK)
SessionID Integer (FK to Sessions)
PupilID Integer (FK to StudentDetails)
Present Yes/No

Base your main form on Sessions, and your continuous subform on
Registration, linked on SessionID. But, ideally, you'd like to populate the
subform with all of your students when you create a new Session record. One
way to do this is to run an insert query in the AfterUpdate event of the
SessionDate field on the main form and then requery your subform:

Dim strSQL As String

‘ Turn warnings off
DoCmd.SetWarnings False

‘ Assign SQL string
strSQL = “INSERT INTO Registration ( SessionID, StudentID ) “ & _
“SELECT Sessions.SessionID, [Student Details].StudentID “ & _
“FROM [Student Details], Sessions “ & _
“WHERE (((Sessions.SessionID)=[Forms]![YourMainForm]![SessionID]));â€

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

Hope that helps.
Sprinks


:

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!

 

[Guest]

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

 

[Guest]

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

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

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!

 

[Guest]

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

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

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

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!

 

[Guest]

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

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

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

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


:

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!

 

[Guest]

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

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

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

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




:

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


:

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!

 

[Guest]

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

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

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

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


:

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




:

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


:

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!