Numbering records on a continuous form

[mattieflo]

Hello,

I have a continuous form where people can enter in questions for a survey.
This survey should have 18 questions, no more no less. In any case, I need to
store the order in which these questions were asked. Is there anyway I can
number these questions on the continuous form itself (From 1-18)?

 

[John W. Vinson]

Hello,

I have a continuous form where people can enter in questions for a survey.
This survey should have 18 questions, no more no less. In any case, I need to
store the order in which these questions were asked. Is there anyway I can
number these questions on the continuous form itself (From 1-18)?

Yes, you can, with a little bit of code. What's the structure of your Tables
though? You can't store data in a form... what is the structure of the
underlying tables?

 

[mattieflo]

Hi John,

Its writing back to tblQuestions. Tblquestions consists of QuestionID,
Question, Month, Year. QuestionID is the primary key, question is what the
actual question is, month and year are integer datatypes which store when the
question was asked. I was going to make a 5th field that stored QuestionOrder
which would store the numbers 1-18 for each question.

 

[John W. Vinson]

Hi John,

Its writing back to tblQuestions. Tblquestions consists of QuestionID,
Question, Month, Year. QuestionID is the primary key, question is what the
actual question is, month and year are integer datatypes which store when the
question was asked. I was going to make a 5th field that stored QuestionOrder
which would store the numbers 1-18 for each question.

Your table structure appears to be wrong. If QuestionID is the primary key
then you can have each question *ONLY ONCE* in the table; it seems you want to
ask the same question multiple times in different months and years. Are you
trying to take a record from the existing (18 row??) Question table and create
a new duplicate record??? I really think you need a second table!

In addition, the words Month and Year are reserved terms and should not be
used as fieldnames; Access can and will get them confused with the builtin
functions Month() and Year().

 

[mattieflo]

Sorry John...instead of Question, the field that's actually on that table is
QuestionAskedID, which is a foreign key from the QuestionAsked table. That
table consists of QuestionAskedID as a primary key and Question as the actual
Question.

What i'm trying to do is remember the order in which the questions were
asked for a particular month. You are correct in saying that im trying to use
the same questions over for the most part for different months. Each month
should have no more or less than 18 questions and I would like to number
those questions from 1-18 for each month.

 

[John W. Vinson]

Sorry John...instead of Question, the field that's actually on that table is
QuestionAskedID, which is a foreign key from the QuestionAsked table. That
table consists of QuestionAskedID as a primary key and Question as the actual
Question.

What i'm trying to do is remember the order in which the questions were
asked for a particular month. You are correct in saying that im trying to use
the same questions over for the most part for different months. Each month
should have no more or less than 18 questions and I would like to number
those questions from 1-18 for each month.

Ok... you can use code in the Form (or Subform's) BeforeInsert event like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!QuestionOrder = NZ(DMax("[QuestionOrder]", "tblQuestions", _
"[Month] = " & Month(Date()) & " AND [Year] = Year(Date())")) + 1
If Me!QuestionOrder > 18 Then
MsgBox "Only 18 questions please", vbOKOnly
Cancel = True
End If
End Sub

to get the current month and year's largest existing QuestionOrder (if any);
add 1 to it (using 0 if there are none yet); and cancelling the insert with
the 19th question.

 

[mattieflo]

Thanks John! Code worked like a charm...

Sorry John...instead of Question, the field that's actually on that table is
QuestionAskedID, which is a foreign key from the QuestionAsked table. That
table consists of QuestionAskedID as a primary key and Question as the actual
Question.

What i'm trying to do is remember the order in which the questions were
asked for a particular month. You are correct in saying that im trying to use
the same questions over for the most part for different months. Each month
should have no more or less than 18 questions and I would like to number
those questions from 1-18 for each month.

Ok... you can use code in the Form (or Subform's) BeforeInsert event like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!QuestionOrder = NZ(DMax("[QuestionOrder]", "tblQuestions", _
"[Month] = " & Month(Date()) & " AND [Year] = Year(Date())")) + 1
If Me!QuestionOrder > 18 Then
MsgBox "Only 18 questions please", vbOKOnly
Cancel = True
End If
End Sub

to get the current month and year's largest existing QuestionOrder (if any);
add 1 to it (using 0 if there are none yet); and cancelling the insert with
the 19th question.