automatically populating subform

[Mary]

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions 8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber" and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

[Queezy]

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions 8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber" and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub

[Mary]

On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions 8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber" and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigation bar
and then come back to it. What am I missing?

Mary

[Mary]

On Apr 10, 6:43 pm, "Mary" <[e-mail]:

On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions 8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber" and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigation bar
and then come back to it. What am I missing?

Mary- Hide quoted text -

- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!

[Don Leverton]

Hmmm...
My thoughts are that you are working with the recordset of the subform
already ... so why bother to insert records using an "INSERT INTO" SQL
statement. I also think that using the .LastModified and Bookmarking will
cause the subform to show the progress of the inserted records.

I'm going to use " '///Rev/// " on the lines that I have revised or added.
(Be advised that this has NOT been tested.)
============================

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone =
Forms![frmStudentSurvey]![tblStudentSurveyAnswersSubform].Form.RecordsetClone

Dim MyID '///Rev///
MyID = Me![RespondentID] '///Rev///

With rsClone '///Rev///
If rsClone.RecordCount = 0 Then
For i = 8 To 40

'///Rev/// db.Execute "Insert Into
tblStudentSurveyAnswers([RespondentID], [questionnumber]) Values('" &
Me![RespondentID] & "', '" & i & "')"

.AddNew '///Rev///
!RespondentID = MyID '///Rev///
!QuestionNumber = i '///Rev///
.Update '///Rev///
.Bookmark = .LastModified '///Rev///
Me![tblStudentSurveyAnswersSubform].Form.Bookmark = .Bookmark
'///Rev///

Next i
End If
.Close '///Rev///
End With
Set rsClone = Nothing
Set db = Nothing '///Rev///
Me.Refresh
End Sub
============================

--
--
HTH,
Don
=============================
E-Mail (if you must) My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

"Mary" <mm**********@msn.comwrote in message
news:11*********************@o5g2000hsb.googlegrou ps.com...

On Apr 10, 6:43 pm, "Mary" <[e-mail]:

>On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions
8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber"
and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigation bar
and then come back to it. What am I missing?

Mary- Hide quoted text -

- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!

[Queezy]

On Apr 10, 6:54 pm, "Mary" <[e-mail]:

On Apr 10, 6:43 pm, "Mary" <[e-mail]:

On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions 8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber" and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigation bar
and then come back to it. What am I missing?

Mary- Hide quoted text -

- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!- Hide quoted text -

- Show quoted text -

Hmm, thats odd. The me.refresh should take care of that.

[Mary]

On Apr 11, 12:34 am, "Don Leverton"
<[e-mail]:

Hmmm...
My thoughts are that you are working with the recordset of the subform
already ... so why bother to insert records using an "INSERT INTO" SQL
statement. I also think that using the .LastModified and Bookmarking will
cause the subform to show the progress of the inserted records.

I'm going to use " '///Rev/// " on the lines that I have revised or added.
(Be advised that this has NOT been tested.)
============================

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone =
Forms![frmStudentSurvey]![tblStudentSurveyAnswersSubform].Form.RecordsetClo*ne

Dim MyID '///Rev///
MyID = Me![RespondentID] '///Rev///

With rsClone '///Rev///
If rsClone.RecordCount = 0 Then
For i = 8 To 40

'///Rev/// db.Execute "Insert Into
tblStudentSurveyAnswers([RespondentID], [questionnumber]) Values('" &
Me![RespondentID] & "', '" & i & "')"

.AddNew '///Rev///
!RespondentID = MyID '///Rev///
!QuestionNumber = i '///Rev///
.Update '///Rev///
.Bookmark = .LastModified '///Rev///
Me![tblStudentSurveyAnswersSubform].Form.Bookmark = .Bookmark
'///Rev///

Next i
End If
.Close '///Rev///
End With
Set rsClone = Nothing
Set db = Nothing '///Rev///
Me.Refresh
End Sub
============================

--
--
HTH,
Don
=============================
E-Mail (if you must) [e-mail]

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

"Mary" <[e-mail] in message

news:11*********************@o5g2000hsb.googlegrou ps.com...

On Apr 10, 6:43 pm, "Mary" <[e-mail]:

On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions
8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber"
and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid=
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigation bar
and then come back to it. What am I missing?

Mary- Hide quoted text -

- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!- Hide quoted text -

- Show quoted text -

Don,

Thanks for taking the time to help me with this.

Here is what my code looks like now:

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]!
[fsubStudentSurvey].Form.RecordsetClone
Dim MyID
MyID = Me![RespondentId]
With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40

.AddNew
!RespondentId = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![fsubStudentSurvey].Form.Bookmark = .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh
End Sub

It errors out as soon soon as I try to add a record. It errors on the
".Update" line; run-time error '3058', index or primary key cannot
contain a null value, which makes sense since the autonumbered
"repondentid" on the main form has not been populated yet. Should I
put this code somewhere else?

Mary

[Queezy]

On Apr 11, 8:49 am, "Mary" <[e-mail]:

On Apr 11, 12:34 am, "Don Leverton"

<[e-mail]:

Hmmm...
My thoughts are that you are working with the recordset of the subform
already ... so why bother to insert records using an "INSERT INTO" SQL
statement. I also think that using the .LastModified and Bookmarking will
cause the subform to show the progress of the inserted records.

I'm going to use " '///Rev/// " on the lines that I have revised or added.
(Be advised that this has NOT been tested.)
============================

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone =
Forms![frmStudentSurvey]![tblStudentSurveyAnswersSubform].Form.RecordsetClo**ne

Dim MyID '///Rev///
MyID = Me![RespondentID] '///Rev///

With rsClone '///Rev///
If rsClone.RecordCount = 0 Then
For i = 8 To 40

'///Rev/// db.Execute "Insert Into
tblStudentSurveyAnswers([RespondentID], [questionnumber]) Values('" &
Me![RespondentID] & "', '" & i & "')"

.AddNew '///Rev///
!RespondentID = MyID '///Rev///
!QuestionNumber = i '///Rev///
.Update '///Rev///
.Bookmark = .LastModified '///Rev///
Me![tblStudentSurveyAnswersSubform].Form.Bookmark = .Bookmark
'///Rev///

Next i
End If
.Close '///Rev///
End With
Set rsClone = Nothing
Set db = Nothing '///Rev///
Me.Refresh
End Sub
============================

--
--
HTH,
Don
=============================
E-Mail (if you must) [e-mail]

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

"Mary" <[e-mail] in message

news:11*********************@o5g2000hsb.googlegrou ps.com...

On Apr 10, 6:43 pm, "Mary" <[e-mail]:
>On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions
8-40
all have the same format. For these questions I have a table setup
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber"
and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] &"',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

>Thank you so much for taking the time to help me with this!

>I've pasted your code and I'm almost there. The subform is not
>populating until I advance to the next record using the navigation bar
>and then come back to it. What am I missing?

>Mary- Hide quoted text -

>- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!- Hide quoted text -

- Show quoted text -

Don,

Thanks for taking the time to help me with this.

Here is what my code looks like now:

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]!
[fsubStudentSurvey].Form.RecordsetClone

Dim MyID
MyID = Me![RespondentId]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40

.AddNew
!RespondentId = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![fsubStudentSurvey].Form.Bookmark = .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh
End Sub

It errors out as soon soon as I try to add a record. It errors on the
".Update" line; run-time error '3058', index or primary key cannot
contain a null value, which makes sense since the autonumbered
"repondentid" on the main form has not been populated yet. Should I
put this code somewhere else?

Mary- Hide quoted text -

- Show quoted text -

Mary,

Don's code works for me, as does my original code.
You do have this code in your main form (frmStudentSurvey) OnCurrent
event, right?

[Mary]

On Apr 11, 9:12 am, "Queezy" <[e-mail]:

On Apr 11, 8:49 am, "Mary" <[e-mail]:

On Apr 11, 12:34 am, "Don Leverton"

<[e-mail]:

Hmmm...
My thoughts are that you are working with the recordset of the subform
already ... so why bother to insert records using an "INSERT INTO" SQL
statement. I also think that using the .LastModified and Bookmarking will
cause the subform to show the progress of the inserted records.

I'm going to use " '///Rev/// " on the lines that I have revised or added.
(Be advised that this has NOT been tested.)
============================

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone =
Forms![frmStudentSurvey]![tblStudentSurveyAnswersSubform].Form.RecordsetClo***ne

Dim MyID '///Rev///
MyID = Me![RespondentID] '///Rev///

With rsClone '///Rev///
If rsClone.RecordCount = 0 Then
For i = 8 To 40

'///Rev/// db.Execute "Insert Into
tblStudentSurveyAnswers([RespondentID], [questionnumber]) Values('" &
Me![RespondentID] & "', '" & i & "')"

.AddNew '///Rev///
!RespondentID = MyID '///Rev///
!QuestionNumber = i '///Rev///
.Update '///Rev///
.Bookmark = .LastModified '///Rev///
Me![tblStudentSurveyAnswersSubform].Form.Bookmark = .Bookmark
'///Rev///

Next i
End If
.Close '///Rev///
End With
Set rsClone = Nothing
Set db = Nothing '///Rev///
Me.Refresh
End Sub
============================

--
--
HTH,
Don
=============================
E-Mail (if you must) [e-mail]

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

"Mary" <[e-mail] in message

>news:11*********************@o5g2000hsb.googlegro ups.com...

On Apr 10, 6:43 pm, "Mary" <[e-mail]:
On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have thosein a
table with a PK of RespondentId, which is autonumbered. Questions
8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber"
and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yoursmay
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID]& "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigationbar
and then come back to it. What am I missing?

Mary- Hide quoted text -

- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!- Hide quoted text -

- Show quoted text -

Don,

Thanks for taking the time to help me with this.

Here is what my code looks like now:

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]!
[fsubStudentSurvey].Form.RecordsetClone

Dim MyID
MyID = Me![RespondentId]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40

.AddNew
!RespondentId = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![fsubStudentSurvey].Form.Bookmark = .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh
End Sub

It errors out as soon soon as I try to add a record. It errors on the
".Update" line; run-time error '3058', index or primary key cannot
contain a null value, which makes sense since the autonumbered
"repondentid" on the main form has not been populated yet. Should I
put this code somewhere else?

Mary- Hide quoted text -

- Show quoted text -

Mary,

Don's code works for me, as does my original code.
You do have this code in your main form (frmStudentSurvey) OnCurrent
event, right?- Hide quoted text -

- Show quoted text -

yes

[Queezy]

On Apr 11, 9:20 am, "Mary" <[e-mail]:

On Apr 11, 9:12 am, "Queezy" <[e-mail]:

On Apr 11, 8:49 am, "Mary" <[e-mail]:

On Apr 11, 12:34 am, "Don Leverton"

<[e-mail]:
Hmmm...
My thoughts are that you are working with the recordset of the subform
already ... so why bother to insert records using an "INSERT INTO" SQL
statement. I also think that using the .LastModified and Bookmarking will
cause the subform to show the progress of the inserted records.

I'm going to use " '///Rev/// " on the lines that I have revised oradded.
(Be advised that this has NOT been tested.)
============================

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone =
Forms![frmStudentSurvey]![tblStudentSurveyAnswersSubform].Form.RecordsetClo****ne

Dim MyID '///Rev///
MyID = Me![RespondentID] '///Rev///

With rsClone '///Rev///
If rsClone.RecordCount = 0 Then
For i = 8 To 40

'///Rev/// db.Execute "Insert Into
tblStudentSurveyAnswers([RespondentID], [questionnumber]) Values('"&
Me![RespondentID] & "', '" & i & "')"

.AddNew '///Rev///
!RespondentID = MyID '///Rev///
!QuestionNumber = i '///Rev///
.Update '///Rev///
.Bookmark = .LastModified '///Rev///
Me![tblStudentSurveyAnswersSubform].Form.Bookmark = .Bookmark
'///Rev///

Next i
End If
.Close '///Rev///
End With
Set rsClone = Nothing
Set db = Nothing '///Rev///
Me.Refresh
End Sub
============================

--
--
HTH,
Don
=============================
E-Mail (if you must) [e-mail]

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

"Mary" <[e-mail] in message

news:11*********************@o5g2000hsb.googlegrou ps.com...

On Apr 10, 6:43 pm, "Mary" <[e-mail]:
>On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to dowith
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions
8-40
all have the same format. For these questions I have a tableset up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabularsubform
for the remaining questions, with only the fields "questionnumber"
and
"answer" on it. Here's my problem: I want the numbers 8 thru40 to
automatically populate on this form, so only the answer to the
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" field to:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

>Thank you so much for taking the time to help me with this!

>I've pasted your code and I'm almost there. The subform is not
>populating until I advance to the next record using the navigation bar
>and then come back to it. What am I missing?

>Mary- Hide quoted text -

>- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!- Hide quoted text -

- Show quoted text -

Don,

Thanks for taking the time to help me with this.

Here is what my code looks like now:

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]!
[fsubStudentSurvey].Form.RecordsetClone

Dim MyID
MyID = Me![RespondentId]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40

.AddNew
!RespondentId = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![fsubStudentSurvey].Form.Bookmark = .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh
End Sub

It errors out as soon soon as I try to add a record. It errors on the
".Update" line; run-time error '3058', index or primary key cannot
contain a null value, which makes sense since the autonumbered
"repondentid" on the main form has not been populated yet. Should I
put this code somewhere else?

Mary- Hide quoted text -

- Show quoted text -

Mary,

Don's code works for me, as does my original code.
You do have this code in your main form (frmStudentSurvey) OnCurrent
event, right?- Hide quoted text -

- Show quoted text -

yes- Hide quoted text -

- Show quoted text -

Mary,

I just read your one post about the respondentid not being populated.
I didn't even think about the record not existing in the main table
that holds the respondent's info. You need the respondent populated
in your respondent info table before this can run. Off the top of my
head, you could make a button, and you would have to enter the the
respondent's info first, then click this button, which will add the
info to your respondents table, then execute this new code.
Here's what it would look like- * note the me.refresh is the only
addition
Private Sub Command10_Click()

Me.Refresh

Dim db As Database
Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
Dim MyID
MyID = Me![RespondentID]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
.AddNew
!RespondentID = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![tblStudentSurveyAnswers Subform].Form.Bookmark
= .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh

End Sub

[Mary]

On Apr 11, 10:07 am, "Queezy" <[e-mail]:

On Apr 11, 9:20 am, "Mary" <[e-mail]:

On Apr 11, 9:12 am, "Queezy" <[e-mail]:

On Apr 11, 8:49 am, "Mary" <[e-mail]:

On Apr 11, 12:34 am, "Don Leverton"

<[e-mail]:
Hmmm...
My thoughts are that you are working with the recordset of the subform
already ... so why bother to insert records using an "INSERT INTO" SQL
statement. I also think that using the .LastModified and Bookmarking will
cause the subform to show the progress of the inserted records.

I'm going to use " '///Rev/// " on the lines that I have revised or added.
(Be advised that this has NOT been tested.)
============================

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone =
Forms![frmStudentSurvey]![tblStudentSurveyAnswersSubform].Form.RecordsetClo*****ne

Dim MyID '///Rev///
MyID = Me![RespondentID] '///Rev///

With rsClone '///Rev///
If rsClone.RecordCount = 0 Then
For i = 8 To 40

'///Rev/// db.Execute "Insert Into
tblStudentSurveyAnswers([RespondentID], [questionnumber]) Values('" &
Me![RespondentID] & "', '" & i & "')"

.AddNew '///Rev///
!RespondentID = MyID '///Rev///
!QuestionNumber = i '///Rev///
.Update '///Rev///
.Bookmark = .LastModified '///Rev///
Me![tblStudentSurveyAnswersSubform].Form.Bookmark =.Bookmark
'///Rev///

Next i
End If
.Close '///Rev///
End With
Set rsClone = Nothing
Set db = Nothing '///Rev///
Me.Refresh
End Sub
============================

--
--
HTH,
Don
=============================
E-Mail (if you must) [e-mail]

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================

"Mary" <[e-mail] in message

>news:11*********************@o5g2000hsb.googlegro ups.com...

On Apr 10, 6:43 pm, "Mary" <[e-mail]:
On Apr 10, 5:23 pm, "Queezy" <[e-mail]:

On Apr 10, 3:28 pm, "Mary" <[e-mail]:

I'll pulling my hair out on this one and would be so appreciative of
any help.

I am creating a data entry form to enter results of a student survey.

There are 40 questions on the survey. The first 7 have to do with
respondent information like grade, class and age. I have those in a
table with a PK of RespondentId, which is autonumbered. Questions
8-40
all have the same format. For these questions I have a table set up
with the respondentID as the FK, and a "questionnumber" and "answer"
field.

My main form has the respondent info on it. I have a tabular subform
for the remaining questions, with only the fields "questionnumber"
and
"answer" on it. Here's my problem: I want the numbers 8 thru 40 to
automatically populate on this form, so only the answer tothe
question needs to be input.How do I best go about doing so? I tried
setting up the default value of the "questionnumber" fieldto:

=1+DMax("questionnumber","tblStudentSurveyAnswers" ,"respondentid =
forms!frmStudentSurvey!respondentID"). I got close, but the number
repeats once before incrementing.

Any ideas?

TIA,
Mary

Took me longer than I thought to come up with this, sorry.
Put this in your forms On Current. And to clarify
tblStudentSurveyAnswers Subform is the name of my subform, yours may
be different. Hope it works for you :)

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()

Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
db.Execute "Insert Into tblStudentSurveyAnswers
([RespondentID], [questionnumber]) Values('" & Me![RespondentID] & "',
'" & i & "')"
Next i
End If
Set rsClone = Nothing
Me.Refresh
End Sub- Hide quoted text -

- Show quoted text -

Thank you so much for taking the time to help me with this!

I've pasted your code and I'm almost there. The subform is not
populating until I advance to the next record using the navigation bar
and then come back to it. What am I missing?

Mary- Hide quoted text -

- Show quoted text -

To clarify my last post - The subform is not populating until I
advance the main form to the next record and come back to it. Then it
works great!- Hide quoted text -

- Show quoted text -

Don,

Thanks for taking the time to help me with this.

Here is what my code looks like now:

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database

Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]!
[fsubStudentSurvey].Form.RecordsetClone

Dim MyID
MyID = Me![RespondentId]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40

.AddNew
!RespondentId = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![fsubStudentSurvey].Form.Bookmark = .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh
End Sub

It errors out as soon soon as I try to add a record. It errors on the
".Update" line; run-time error '3058', index or primary key cannot
contain a null value, which makes sense since the autonumbered
"repondentid" on the main form has not been populated yet. Should I
put this code somewhere else?

Mary- Hide quoted text -

- Show quoted text -

Mary,

Don's code works for me, as does my original code.
You do have this code in your main form (frmStudentSurvey) OnCurrent
event, right?- Hide quoted text -

- Show quoted text -

yes- Hide quoted text -

- Show quoted text -

Mary,

I just read your one post about the respondentid not being populated.
I didn't even think about the record not existing in the main table
that holds the respondent's info. You need the respondent populated
in your respondent info table before this can run. Off the top of my
head, you could make a button, and you would have to enter the the
respondent's info first, then click this button, which will add the
info to your respondents table, then execute this new code.
Here's what it would look like- * note the me.refresh is the only
addition

Private Sub Command10_Click()

Me.Refresh

Dim db As Database
Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone

Dim MyID
MyID = Me![RespondentID]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
.AddNew
!RespondentID = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![tblStudentSurveyAnswers Subform].Form.Bookmark
= .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh

End Sub- Hide quoted text -

- Show quoted text -

Thanks guys! It works!

I moved Don's code to the after-update event of question number 7, so
that as soon as question 7 is entered the subform populates. I also
added a button next to question 7 with the same code in case the
respondent didn't answer number 7.

I REALLY appreciate the help.:)

Mary

[Don Leverton]

I hadn't understood the part about the Repondents not being pre-existant
either.
Is this always the case, or do you need to add these questions for existing
respondents as well?
If you have a mix of new and existing, you could test to see if it is a new
or existing record.

I think that the best way to handle this is to define the procedure that
adds these records as an external procedure, then CALL it from within
different events.
Something like this:

==========================================
Private Sub sFillQuestions

Me.Dirty = False 'Save the current form record in order to avoid error
#3058 . IMHO, this a better way of doing that than "Me.Refresh"

Private Sub Form_Current()
Dim rsClone As Recordset
Dim db As Database
Set db = CurrentDb()
Set rsClone = Me![fsubStudentSurvey].Form.RecordsetClone
Dim MyID
MyID = Me![RespondentId]
With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40

.AddNew
!RespondentId = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![fsubStudentSurvey].Form.Bookmark = .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh
End Sub
==========================================
Private Sub Form_Current()
'This step should look after the pre-existing respondents that DO NOT have
survey questions yet ...
If Me.NewRecord = False And
Me![fsubStudentSurvey].Form.RecordsetClone.RecordCount = 0 Then
sFillQuestions
Else
Exit Sub
End If

Private Sub Form_BeforeInsert()
'We know that .NewRecord HAS to be true for this event to "fire" ... so...
If Me![fsubStudentSurvey].Form.RecordsetClone.RecordCount = 0 Then
sFillQuestions
End If
EndSub

--
HTH,
(Let us know)
Don
"Queezy" <qu****@gmail.comwrote in message
news:11**********************@d57g2000hsg.googlegr oups.com...

<previous threads snipped>

Mary,

I just read your one post about the respondentid not being populated.
I didn't even think about the record not existing in the main table
that holds the respondent's info. You need the respondent populated
in your respondent info table before this can run. Off the top of my
head, you could make a button, and you would have to enter the the
respondent's info first, then click this button, which will add the
info to your respondents table, then execute this new code.
Here's what it would look like- * note the me.refresh is the only
addition
Private Sub Command10_Click()

Me.Refresh

Dim db As Database
Set db = CurrentDb()
Set rsClone = Forms![frmStudentSurvey]![tblStudentSurveyAnswers
Subform].Form.RecordsetClone
Dim MyID
MyID = Me![RespondentID]

With rsClone
If rsClone.RecordCount = 0 Then
For i = 8 To 40
.AddNew
!RespondentID = MyID
!questionnumber = i
.Update
.Bookmark = .LastModified
Me![tblStudentSurveyAnswers Subform].Form.Bookmark
= .Bookmark
Next i
End If
.Close
End With
Set rsClone = Nothing
Set db = Nothing
Me.Refresh

End Sub

[Don Leverton]

Hi Mary,
I didn't see this until after my reply.
"Queezy" deserves most of the credit, but I'm glad to hear that you got it
sorted out.

My suggestion to write the code as an external sub and call it from differnt
events would also work for the method you describe here.
I do think that taking the decision out of the hands of the user (using the
OnCurrent and BeforeInsert methods in my last post might still be a good
idea.
You just never know what a user will or will not do. :)

Don

.... "Mary" <mm**********@msn.comwrote in message
news:11*********************@e65g2000hsc.googlegro ups.com...
On Apr 11, 10:07 am, "Queezy" <[e-mail]:

On Apr 11, 9:20 am, "Mary" <[e-mail]:

<snip>

Thanks guys! It works!

I moved Don's code to the after-update event of question number 7, so
that as soon as question 7 is entered the subform populates. I also
added a button next to question 7 with the same code in case the
respondent didn't answer number 7.

I REALLY appreciate the help.:)

Mary

[Mary]

On Apr 11, 12:33 pm, "Don Leverton" <[e-mail]:

Hi Mary,
I didn't see this until after my reply.
"Queezy" deserves most of the credit, but I'm glad to hear that you got it
sorted out.

My suggestion to write the code as an external sub and call it from differnt
events would also work for the method you describe here.
I do think that taking the decision out of the hands of the user (using the
OnCurrent and BeforeInsert methods in my last post might still be a good
idea.
You just never know what a user will or will not do. :)

Don

... "Mary" <[e-mail] in messagenews:11*********************@e65g2000hsc.go oglegroups.com...
On Apr 11, 10:07 am, "Queezy" <[e-mail]:On Apr 11, 9:20 am, "Mary" <[e-mail]:

<snip>

Thanks guys! It works!

I moved Don's code to the after-update event of question number 7, so
that as soon as question 7 is entered the subform populates. I also
added a button next to question 7 with the same code in case the
respondent didn't answer number 7.

I REALLY appreciate the help.:)

Mary

Don,

I had a nasty migraine so it's taken awhile to get back to you...

I appreciate what you're saying about the users. In this case it's
just one user entering one batch of surveys and that's it. All the
records will always be new. The responders of the survey are actually
anonymous - the autonumer id is just so I can relate them to the
answers table. So I thing I'm good with the way I have it now.

Thanks again for your help.

Mary