Increment one field and copy another in a subform

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


"Ms.Re-WA" wrote:

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

Ms.Re-WA

"Klatuu" wrote:

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


"Ms.Re-WA" wrote:

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

"Klatuu" wrote:

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


"Ms.Re-WA" wrote:

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

"Ms.Re-WA" wrote:

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

"Klatuu" wrote:

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


"Ms.Re-WA" wrote:

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?