Increment one field and copy another in a subform

[Ms.Re-WA]

I am a math teacher trying to create a data base for the district for a test
taken by over 500 students. I have tables for students, questions on the
test, and the actual test answers. The main form shows the student
information, and the subform shows the answers for the student for the test.
The fields are Student id, Question id, Day, Question #, and Student
Response. The Day and Question # are determined by the unique Question id.
I have Access 2000, but the district has Access 2003. I'm working on it at
home but can transport via thumb drive and work on it at the district offices.

What I need is a way to keep the student number but increment the Question #
after each Student Response in entered up to the last question, so that the
secretary who entered the data can just type the student's answers then start
the next student.

I read through most of the other posts about this topic and none seem to fit
my situation. I hope someone out there can help me. Thanks in advance.

Ms.Re-WA

 

[SteveM]

Exactly how are you detrmining the Question# by the unique QuestionID?
If it is already determined why do you want to increment it?

Do you want to create a field that is the concatenation of StudentID and
Question# or some other field that represents the order in which each
question was answered?

Steve

 

[Ms.Re-WA]

You are correct. When I entered a new student's data in form view, the
student number did automatically show up. However, the question id, which is
the primary key in the Questions table, defaulted to 0. I need it to update
to the next number until the last answer is added.

To answer SteveM's question, the question id is the primary key in the
Questions table. Since the test was taken in two days using a test numbered
1-18 for Day 1 and another one numbered 1-19 for day 2, I used the question
id from 1-37 and have a day number and question # as fields in the question
table that will appear in the subform so the data entry person sees the
question # as it appears on the test.

All I need is a way to force the question id to increment up to 37 (the
maximum question id).

Again, thanks for the help.

Ms.Re-WA

 

[Klatuu]

It sounds like you are using a single form for the subform.
If you used a datasheet form or continuous form, you would have all the
answers displayed and wouldn't have to worry about it.
Another way would be to filter the subform's record source so only
unanswered questions would be included.

 

[Ms.Re-WA]

When I open the form, it shows exactly what I want which is the data for that
student from questions 1-37. It is when I an adding a new student and have
no data for that student that the problem occurs. The data entry person sees
an empty one line data sheet. When they click on the student response cell,
the correct student number in entered, but the question id is 0. I can
change the default to 1, but I don't want it to remain 1 for the rest of the
list. Is there a way to force the default in that column to change until it
gets to the max and then reset to 1?

I hope I've explained it better this time.

Ms.Re-WA

 

[Klatuu]

You could do that.
In the subform, use the Current event to prepopulate the new record.

Private Sub Form_Current()
Dim lngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[QuestionNumber]", "AnswerTable", "[Student]
= " & Me.Parent.txtStudentID),0)) + 1
If lngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = lngQNbr
End If
Etc.
End If
This is only the basic. It is a way to determine the highest current number
that exists and add 1 to it for the next record.

 

[Ms.Re-WA]

Here is what I tried to put in the Current event in the subform. If tells me
there is an error:end of statement in the Nbr = line and highlights the Nz.
Am I supposed to have parentheses between DMax and Nz and another at the end?

I replaced QuestionNumber with my field name of [Question id] and
"AnswerTable with my table name of "Test Data" and Student with [Student id]
which is the primary key for the student table and one of the fields in the
test data table. I am not sure what txtStudentID is and whether I am
supposed to replace your "txtQuestionNumber" with "txt[Question id]".

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

What programming background I have is not enough for me to decide what the
"Etc." is supposed to be between the End if's.

Thanks for helping me. I think we're getting really close.

Ms.Re-WA

 

[Ms.Re-WA]

Ok, I worked on it all weekend and here's what I came up with:

Dim IngQNbr As Long
Dim A, B As Integer
If Me.NewRecord Then
A = DMax("[entry id]", "Test Data")
B = DLookup("[Question id]", "Test Data", "[entry id]=" & A)
IngQNbr = B + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

I put this is the OnCurrent event in the subform and it works OK except that
it gives the message box "No More Qeustions" before the blank form opens and
again after I start entering in the first field in the parent form.

I've tried to put it in the BeforeUpdate, AfterUpdate, and several other
events, but don't get what I want: the Question Id increasing by 1 (starting
at 1) until it completes #37.

Is my code wrong or incomplete? Or is there a better place to put the code
so that it works correctly?

Thanks for the help.

Ms.Re-WA

 

[Klatuu]

The error you are getting is because Nbr is not declared. It should be
lngQNbr.

txtStudentID would be the control that has the student primary key value.
"txtQuestionNumber" is the control that shows the question number.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
lngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Here is what I tried to put in the Current event in the subform. If tells me
there is an error:end of statement in the Nbr = line and highlights the Nz.
Am I supposed to have parentheses between DMax and Nz and another at the end?

I replaced QuestionNumber with my field name of [Question id] and
"AnswerTable with my table name of "Test Data" and Student with [Student id]
which is the primary key for the student table and one of the fields in the
test data table. I am not sure what txtStudentID is and whether I am
supposed to replace your "txtQuestionNumber" with "txt[Question id]".

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

What programming background I have is not enough for me to decide what the
"Etc." is supposed to be between the End if's.

Thanks for helping me. I think we're getting really close.

Ms.Re-WA

You could do that.
In the subform, use the Current event to prepopulate the new record.

Private Sub Form_Current()
Dim lngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[QuestionNumber]", "AnswerTable", "[Student]
= " & Me.Parent.txtStudentID),0)) + 1
If lngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = lngQNbr
End If
Etc.
End If
This is only the basic. It is a way to determine the highest current number
that exists and add 1 to it for the next record.

 

[Ms.Re-WA]

I'm sorry, but it still does not work. It continues to give me an error and
highlighting the Nz part of the statement. I am working in Access 2000, if
that is a consideration. I tried changing Me.Parent.txt[student number] to
Me.Parent.[student number] but it did not solve the problem. I continue to
get a compile error.

I know that the code I sent in the more recent post works, but not
perfectly. Could you help me edit that one to work better?

Thanks for your patience with an inexperienced user.

Ms.Re-WA

The error you are getting is because Nbr is not declared. It should be
lngQNbr.

txtStudentID would be the control that has the student primary key value.
"txtQuestionNumber" is the control that shows the question number.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
lngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Here is what I tried to put in the Current event in the subform. If tells me
there is an error:end of statement in the Nbr = line and highlights the Nz.
Am I supposed to have parentheses between DMax and Nz and another at the end?

I replaced QuestionNumber with my field name of [Question id] and
"AnswerTable with my table name of "Test Data" and Student with [Student id]
which is the primary key for the student table and one of the fields in the
test data table. I am not sure what txtStudentID is and whether I am
supposed to replace your "txtQuestionNumber" with "txt[Question id]".

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

What programming background I have is not enough for me to decide what the
"Etc." is supposed to be between the End if's.

Thanks for helping me. I think we're getting really close.

Ms.Re-WA

You could do that.
In the subform, use the Current event to prepopulate the new record.

Private Sub Form_Current()
Dim lngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[QuestionNumber]", "AnswerTable", "[Student]
= " & Me.Parent.txtStudentID),0)) + 1
If lngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = lngQNbr
End If
Etc.
End If
This is only the basic. It is a way to determine the highest current number
that exists and add 1 to it for the next record.

 

[Klatuu]

It would be very helpful if you would post the error number and show the
line of code where it fails.
This code is preferable, it is a matter of getting the syntax correct.
--
Dave Hargis, Microsoft Access MVP

I'm sorry, but it still does not work. It continues to give me an error and
highlighting the Nz part of the statement. I am working in Access 2000, if
that is a consideration. I tried changing Me.Parent.txt[student number] to
Me.Parent.[student number] but it did not solve the problem. I continue to
get a compile error.

I know that the code I sent in the more recent post works, but not
perfectly. Could you help me edit that one to work better?

Thanks for your patience with an inexperienced user.

Ms.Re-WA

The error you are getting is because Nbr is not declared. It should be
lngQNbr.

txtStudentID would be the control that has the student primary key value.
"txtQuestionNumber" is the control that shows the question number.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
lngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Here is what I tried to put in the Current event in the subform. If tells me
there is an error:end of statement in the Nbr = line and highlights the Nz.
Am I supposed to have parentheses between DMax and Nz and another at the end?

I replaced QuestionNumber with my field name of [Question id] and
"AnswerTable with my table name of "Test Data" and Student with [Student id]
which is the primary key for the student table and one of the fields in the
test data table. I am not sure what txtStudentID is and whether I am
supposed to replace your "txtQuestionNumber" with "txt[Question id]".

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

What programming background I have is not enough for me to decide what the
"Etc." is supposed to be between the End if's.

Thanks for helping me. I think we're getting really close.

Ms.Re-WA

:

You could do that.
In the subform, use the Current event to prepopulate the new record.

Private Sub Form_Current()
Dim lngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[QuestionNumber]", "AnswerTable", "[Student]
= " & Me.Parent.txtStudentID),0)) + 1
If lngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = lngQNbr
End If
Etc.
End If
This is only the basic. It is a way to determine the highest current number
that exists and add 1 to it for the next record.

 

[Ms.Re-WA]

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

Ms.Re-WA

It would be very helpful if you would post the error number and show the
line of code where it fails.
This code is preferable, it is a matter of getting the syntax correct.
--
Dave Hargis, Microsoft Access MVP

I'm sorry, but it still does not work. It continues to give me an error and
highlighting the Nz part of the statement. I am working in Access 2000, if
that is a consideration. I tried changing Me.Parent.txt[student number] to
Me.Parent.[student number] but it did not solve the problem. I continue to
get a compile error.

I know that the code I sent in the more recent post works, but not
perfectly. Could you help me edit that one to work better?

Thanks for your patience with an inexperienced user.

Ms.Re-WA

The error you are getting is because Nbr is not declared. It should be
lngQNbr.

txtStudentID would be the control that has the student primary key value.
"txtQuestionNumber" is the control that shows the question number.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
lngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


:

Here is what I tried to put in the Current event in the subform. If tells me
there is an error:end of statement in the Nbr = line and highlights the Nz.
Am I supposed to have parentheses between DMax and Nz and another at the end?

I replaced QuestionNumber with my field name of [Question id] and
"AnswerTable with my table name of "Test Data" and Student with [Student id]
which is the primary key for the student table and one of the fields in the
test data table. I am not sure what txtStudentID is and whether I am
supposed to replace your "txtQuestionNumber" with "txt[Question id]".

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

What programming background I have is not enough for me to decide what the
"Etc." is supposed to be between the End if's.

Thanks for helping me. I think we're getting really close.

Ms.Re-WA

:

You could do that.
In the subform, use the Current event to prepopulate the new record.

Private Sub Form_Current()
Dim lngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[QuestionNumber]", "AnswerTable", "[Student]
= " & Me.Parent.txtStudentID),0)) + 1
If lngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = lngQNbr
End If
Etc.
End If
This is only the basic. It is a way to determine the highest current number
that exists and add 1 to it for the next record.

 

[Klatuu]

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

Ms.Re-WA

It would be very helpful if you would post the error number and show the
line of code where it fails.
This code is preferable, it is a matter of getting the syntax correct.
--
Dave Hargis, Microsoft Access MVP

I'm sorry, but it still does not work. It continues to give me an error and
highlighting the Nz part of the statement. I am working in Access 2000, if
that is a consideration. I tried changing Me.Parent.txt[student number] to
Me.Parent.[student number] but it did not solve the problem. I continue to
get a compile error.

I know that the code I sent in the more recent post works, but not
perfectly. Could you help me edit that one to work better?

Thanks for your patience with an inexperienced user.

Ms.Re-WA

:

The error you are getting is because Nbr is not declared. It should be
lngQNbr.

txtStudentID would be the control that has the student primary key value.
"txtQuestionNumber" is the control that shows the question number.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
lngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


:

Here is what I tried to put in the Current event in the subform. If tells me
there is an error:end of statement in the Nbr = line and highlights the Nz.
Am I supposed to have parentheses between DMax and Nz and another at the end?

I replaced QuestionNumber with my field name of [Question id] and
"AnswerTable with my table name of "Test Data" and Student with [Student id]
which is the primary key for the student table and one of the fields in the
test data table. I am not sure what txtStudentID is and whether I am
supposed to replace your "txtQuestionNumber" with "txt[Question id]".

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student id] =
" & Me.Parent.txt[Student id]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = IngQNbr
End If
End If
End Sub

What programming background I have is not enough for me to decide what the
"Etc." is supposed to be between the End if's.

Thanks for helping me. I think we're getting really close.

Ms.Re-WA

:

You could do that.
In the subform, use the Current event to prepopulate the new record.

Private Sub Form_Current()
Dim lngQNbr As Long
If Me.NewRecord Then
Nbr = DMax Nz((DLookup("[QuestionNumber]", "AnswerTable", "[Student]
= " & Me.Parent.txtStudentID),0)) + 1
If lngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.txtQuestionNumber = lngQNbr
End If
Etc.
End If
This is only the basic. It is a way to determine the highest current number
that exists and add 1 to it for the next record.

 

[Ms.Re-WA]

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

 

[Ms.Re-WA]

I did it! I finally figured out that the student id is named "Student
Number" in the form and is tied to "Student id" in the subform, which caused
the error concerning not finding the field.

I got to thinking about the process that you were trying to do, and reversed
the Nz and DMax commands, and didn't see the need for the DLookup.

It works fine when I put it in the BeforeInsert Property. If I put it in
the Current property, it gives me an error about improper use of Null in the
first line about the "If Me.NewRecord" line.

I do have one problem. If I accidently try to enter an answer after 37, it
correctly gives me the message box, BUT then it adds a record for question 1.
Is there a stop or end command that will not allow it to return to the "If
Me.NewRecord" line after the MsgBox?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

End Sub

Thanks for sticking with me on this project.

Ms.Re-WA

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

 

[Klatuu]

Great. I was thinking about this last night and realized the DLookup was not
needed. I don't even know why I put it there. My apologies for the errors,
but at least you got there.

I do wonder why you need to do this:
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

Why not just:
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
Me.Parent![Student number]), 0) + 1

--
Dave Hargis, Microsoft Access MVP

I did it! I finally figured out that the student id is named "Student
Number" in the form and is tied to "Student id" in the subform, which caused
the error concerning not finding the field.

I got to thinking about the process that you were trying to do, and reversed
the Nz and DMax commands, and didn't see the need for the DLookup.

It works fine when I put it in the BeforeInsert Property. If I put it in
the Current property, it gives me an error about improper use of Null in the
first line about the "If Me.NewRecord" line.

I do have one problem. If I accidently try to enter an answer after 37, it
correctly gives me the message box, BUT then it adds a record for question 1.
Is there a stop or end command that will not allow it to return to the "If
Me.NewRecord" line after the MsgBox?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

End Sub

Thanks for sticking with me on this project.

Ms.Re-WA

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP


:

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

 

[Ms.Re-WA]

I originally had the two lines combined as you suggested, but it kept giving
me an error, so I separated it to find the problem, which I discovered was
using student id instead of student number. But it helped find the boo-boo.

Can I put an "End Sub" command after the Msgbox line to prevent a new line
of data appearing? Or is there a better way to do that? With this final
question, I think we've solved the problem.

Again, thanks for working with me.

Ms.Re-WA

Great. I was thinking about this last night and realized the DLookup was not
needed. I don't even know why I put it there. My apologies for the errors,
but at least you got there.

I do wonder why you need to do this:
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

Why not just:
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
Me.Parent![Student number]), 0) + 1

--
Dave Hargis, Microsoft Access MVP

I did it! I finally figured out that the student id is named "Student
Number" in the form and is tied to "Student id" in the subform, which caused
the error concerning not finding the field.

I got to thinking about the process that you were trying to do, and reversed
the Nz and DMax commands, and didn't see the need for the DLookup.

It works fine when I put it in the BeforeInsert Property. If I put it in
the Current property, it gives me an error about improper use of Null in the
first line about the "If Me.NewRecord" line.

I do have one problem. If I accidently try to enter an answer after 37, it
correctly gives me the message box, BUT then it adds a record for question 1.
Is there a stop or end command that will not allow it to return to the "If
Me.NewRecord" line after the MsgBox?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

End Sub

Thanks for sticking with me on this project.

Ms.Re-WA

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

:

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP


:

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

 

[Klatuu]

You can't have two End Subs, it will upset the compiler and we know how picky
he is.
What you can do is set the Cancel argument of the event to True. This will
cause the insert to not happen. Then, you need to undo the record, so right
after the message box:

Cancel = True
Me.Undo
--
Dave Hargis, Microsoft Access MVP

I originally had the two lines combined as you suggested, but it kept giving
me an error, so I separated it to find the problem, which I discovered was
using student id instead of student number. But it helped find the boo-boo.

Can I put an "End Sub" command after the Msgbox line to prevent a new line
of data appearing? Or is there a better way to do that? With this final
question, I think we've solved the problem.

Again, thanks for working with me.

Ms.Re-WA

Great. I was thinking about this last night and realized the DLookup was not
needed. I don't even know why I put it there. My apologies for the errors,
but at least you got there.

I do wonder why you need to do this:
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

Why not just:
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
Me.Parent![Student number]), 0) + 1

--
Dave Hargis, Microsoft Access MVP

I did it! I finally figured out that the student id is named "Student
Number" in the form and is tied to "Student id" in the subform, which caused
the error concerning not finding the field.

I got to thinking about the process that you were trying to do, and reversed
the Nz and DMax commands, and didn't see the need for the DLookup.

It works fine when I put it in the BeforeInsert Property. If I put it in
the Current property, it gives me an error about improper use of Null in the
first line about the "If Me.NewRecord" line.

I do have one problem. If I accidently try to enter an answer after 37, it
correctly gives me the message box, BUT then it adds a record for question 1.
Is there a stop or end command that will not allow it to return to the "If
Me.NewRecord" line after the MsgBox?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

End Sub

Thanks for sticking with me on this project.

Ms.Re-WA

:

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

:

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP


:

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

 

[Ms.Re-WA]

Halleluja! It works! Thanks for the help and your patience!

Ms.Re-WA

You can't have two End Subs, it will upset the compiler and we know how picky
he is.
What you can do is set the Cancel argument of the event to True. This will
cause the insert to not happen. Then, you need to undo the record, so right
after the message box:

Cancel = True
Me.Undo
--
Dave Hargis, Microsoft Access MVP

I originally had the two lines combined as you suggested, but it kept giving
me an error, so I separated it to find the problem, which I discovered was
using student id instead of student number. But it helped find the boo-boo.

Can I put an "End Sub" command after the Msgbox line to prevent a new line
of data appearing? Or is there a better way to do that? With this final
question, I think we've solved the problem.

Again, thanks for working with me.

Ms.Re-WA

Great. I was thinking about this last night and realized the DLookup was not
needed. I don't even know why I put it there. My apologies for the errors,
but at least you got there.

I do wonder why you need to do this:
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

Why not just:
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
Me.Parent![Student number]), 0) + 1

--
Dave Hargis, Microsoft Access MVP


:

I did it! I finally figured out that the student id is named "Student
Number" in the form and is tied to "Student id" in the subform, which caused
the error concerning not finding the field.

I got to thinking about the process that you were trying to do, and reversed
the Nz and DMax commands, and didn't see the need for the DLookup.

It works fine when I put it in the BeforeInsert Property. If I put it in
the Current property, it gives me an error about improper use of Null in the
first line about the "If Me.NewRecord" line.

I do have one problem. If I accidently try to enter an answer after 37, it
correctly gives me the message box, BUT then it adds a record for question 1.
Is there a stop or end command that will not allow it to return to the "If
Me.NewRecord" line after the MsgBox?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

End Sub

Thanks for sticking with me on this project.

Ms.Re-WA

:

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

:

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP


:

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?

 

[Klatuu]

Great!
Glad I could help.
--
Dave Hargis, Microsoft Access MVP

Halleluja! It works! Thanks for the help and your patience!

Ms.Re-WA

You can't have two End Subs, it will upset the compiler and we know how picky
he is.
What you can do is set the Cancel argument of the event to True. This will
cause the insert to not happen. Then, you need to undo the record, so right
after the message box:

Cancel = True
Me.Undo
--
Dave Hargis, Microsoft Access MVP

I originally had the two lines combined as you suggested, but it kept giving
me an error, so I separated it to find the problem, which I discovered was
using student id instead of student number. But it helped find the boo-boo.

Can I put an "End Sub" command after the Msgbox line to prevent a new line
of data appearing? Or is there a better way to do that? With this final
question, I think we've solved the problem.

Again, thanks for working with me.

Ms.Re-WA

:

Great. I was thinking about this last night and realized the DLookup was not
needed. I don't even know why I put it there. My apologies for the errors,
but at least you got there.

I do wonder why you need to do this:
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

Why not just:
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
Me.Parent![Student number]), 0) + 1

--
Dave Hargis, Microsoft Access MVP


:

I did it! I finally figured out that the student id is named "Student
Number" in the form and is tied to "Student id" in the subform, which caused
the error concerning not finding the field.

I got to thinking about the process that you were trying to do, and reversed
the Nz and DMax commands, and didn't see the need for the DLookup.

It works fine when I put it in the BeforeInsert Property. If I put it in
the Current property, it gives me an error about improper use of Null in the
first line about the "If Me.NewRecord" line.

I do have one problem. If I accidently try to enter an answer after 37, it
correctly gives me the message box, BUT then it adds a record for question 1.
Is there a stop or end command that will not allow it to return to the "If
Me.NewRecord" line after the MsgBox?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = Me.Parent![Student number]
IngQNbr = Nz(DMax("[Question id]", "Test Data", "[Student id] = " &
IngQNbr), 0) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

End Sub

Thanks for sticking with me on this project.

Ms.Re-WA

:

I'm sorry, but it still does not work. I added a parenthesis after the ,0
part for it to accept it, but then it gives me a compile error: Argument not
optional and highlights DMax.

If I am understanding the logic correctly, you are looking for the question
id(s) of the new student, if it exists, then giving it a value of 0 if it
does not exist, then finding the maximum. The DMax function requires the
expression and the domain and will return Null if it does not exist, so are
we missing the domain name after the first right parentheses? And would it
make sense to do the Nz (DMax(DLookup(... instead of the DMax(Nz(DLookup(...?

I have been trying to use the help topics and I cannot find the information
on Me.Parent syntax. When I was trying to use this in my own attempts, it
tells me it cannot find "|" in the expression "[Student id] = " &
me.parent.[Student id]. I'm at a loss to figure out what it means. Should
it be me!parent![Student id]?

We all make mistakes. It's how we learn. Thanks for helping me with mine.

Ms.Re-WA

Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
id] = " & Me.Parent.[Student id])), 0)) + 1
If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If

:

My bad. I left a syntax error. Should be:
IngQNbr = DMax(Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

Sorry for the confusion, but it is hard not to make mistakes when you don't
have the compiler to spit at you when you loose control of your fingers.

--
Dave Hargis, Microsoft Access MVP


:

Here is exactly as it appears. The error is Compile Error, Expected: End of
statement, and it highlights the Nz in the IngQNbr = line.

Private Sub Form_Current()
Dim IngQNbr As Long
If Me.NewRecord Then
IngQNbr = DMax Nz((DLookup("[Question id]", "Test Data", "[Student
Number] = "& Me.Parent.[Student Number]),0)) + 1

If IngQNbr > 37 Then
MsgBox "No More Questions"
Else
Me.Question_id = IngQNbr
End If
End If
End Sub

What did I miss?