Code is failing

[CJ]

Happy New Year Groupies!!
I hope 2008 is exciting and prosperous for everybody.

Today's problem, I have two very similar pieces of code on two different
forms. The issue is that one always works, the other does not
and I can not figure out the problem.

The code is below:
Everything associated with "Sequence" is fine. Basically, I am generating a
new, incremental number when a command button is pushed.
I want the same thing to happen for "Disposal" but the number is not
increasing and I get the error that there is a syntax error in my
Update statement.

I did not write this code, I had newsgroup assistance, so I don't understand
enough to fix it myself.

Public Function fcnGetNextSequence() As String
fcnGetNextSequence = DLookup("seqno", "tblMLTSequence")
End Function

Public Function fcnGetNextDisposal() As String
fcnGetNextDisposal = DLookup("DispSeqNo", "tblDisposalSequence")
End Function

Public Function fcnUpdate_tblMLTSequence(Optional strStart As String)
Dim strSQL As String
Dim strLtr As String
Dim strNum As Variant
Dim strConcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblMLTSequence"), 4)
strLtr = Left(DLookup("seqno", "tblMLTSequence"), 1)
Else
strNum = Right(strStart, 4)
strLtr = Left(strStart, 1)
End If
If strNum = "9999" Then
strNum = "0001"
strLtr = Asc(strLtr) + 1
strLtr = Chr$(strLtr)
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strConcat = strLtr + strNum
strSQL = "UPDATE tblMLTSequence set seqno = " & Chr$(39) & strConcat &
Chr$(39)
CurrentDb.Execute strSQL, dbFailOnError
End Function


Public Function fcnUpdate_tblDisposalSequence(Optional strStart As String)
Dim strDispSQL As String
Dim strNum As Variant
If Len(strStart) = 0 Then
strNum = Left(DLookup("DispSeqNo", "tblDisposalSequence"), 4)
Else
strNum = Left(strStart, 4)
End If
If strNum = "9999" Then
strNum = "0001"
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strDispSQL = "UPDATE tblDisposalSequence set DispSeqNo = "
CurrentDb.Execute strDispSQL, dbFailOnError
End Function

 

[George Nicholson]

You've told Access which field to update, but haven't told it what you want
that field updated to.

Change this line:
strDispSQL = "UPDATE tblDisposalSequence set DispSeqNo = " & Chr$(39) &
strNum & Chr$(39)

 

[CJ]

Well, that wasn't so difficult was it ??!!!??

Thanks George!!

Could you please tell me what " & Chr$(39) & strNum & Chr$(39) " actually
does? If I know what's going on, I might be able to solve it myself next
time.......or not <g>

 

[George Nicholson]

chr(39) is the Ascii code for a single quote. You could also code it as (or
see others code it as):
strDispSQL = "UPDATE tblDisposalSequence set DispSeqNo = '" & strNum & "'"

If strNum = "0002" then either approach would equate to:

strDispSQL = "UPDATE tblDisposalSequence set DispSeqNo = '0002'"

 

[CJ]

Wonder of wonders I actually understand ALL of this code....there's hope for
me yet!

Thanks again George.