Adding specific text in table field record

[nycman]

Hello,



I use the following function to add a next class name into the tblClass, ClassName field.

 'function returns the next Class Name for frmAddNewClass form

Public Function GetNum() As String
 Dim STR As String
  'no records from current year
   If IsNull(DLookup("ClassName", "tblClass", _
      "left(ClassName,2)='" & Right(CStr(Year(Date)), 2) & "' ")) Then
        GetNum = Right(CStr(Year(Date)), 2) & "-" & "001"
   Else
      STR = CStr(CInt(Right(DMax("ClassName", "tblClass", _
          "left(ClassName,2)='" & Right(CStr(Year(Date)), 2) & "' "), 3)) + 1)
      GetNum = Right(CStr(Year(Date)), 2) & "-" & ZeroLeft(STR, 3)
   End If
End Function

This will result in ClassName being 15-001, 15-002....

How do I add a specific text (CLASS) to appear before the 15-001, like CLASS 15-001, CLASS 15-002 etc.

Thank you

[nycman]

[QUOTE=nycman;291798
How do I add a specific text (CLASS) to appear before the 15-001, like CLASS 15-001, CLASS 15-002 etc.
[/QUOTE]

Ok Got it.

[ssanfu]

Because there wasn't an example, I wasn't sure if you meant the word "Class" or a class name such as "Math".

What does your function look like now?

[nycman]

Because there wasn't an example, I wasn't sure if you meant the word "Class" or a class name such as "Math".

What does your function look like now?

Sorry I meant the word Class.

Public Function GetNum() As String
 Dim STR As String
  'no records from current year
   If IsNull(DLookup("ClassName", "tblClass", _
      "left(ClassName,2)='" & Right(CStr(Year(Date)), 2) & "' ")) Then
        GetNum = "Class" & " " & Right(CStr(Year(Date)), 2) & "-" & "001"
   Else
      STR = CStr(CInt(Right(DMax("ClassName", "tblClass", _
          "left(ClassName,2)='" & Right(CStr(Year(Date)), 2) & "' "), 3)) + 1)
      GetNum = "Class" & " " & Right(CStr(Year(Date)), 2) & "-" & ZeroLeft(STR, 3)
   End If
End Function

[ssanfu]

One suggestion:
Don't need the quoted space - just add it to the back of the word "Class" (doesn't hurt, but easier to add the space to the word)
From

GetNum = "Class" & " " & Right(CStr(Year(Date)), 2) & "-" & ZeroLeft(STR, 3)

to

GetNum = "Class " & Right(CStr(Year(Date)), 2) & "-" & ZeroLeft(STR, 3)

[nycman]

One suggestion:
Don't need the quoted space - just add it to the back of the word "Class" (doesn't hurt, but easier to add the space to the word)
From

GetNum = "Class" & " " & Right(CStr(Year(Date)), 2) & "-" & ZeroLeft(STR, 3)

to

GetNum = "Class " & Right(CStr(Year(Date)), 2) & "-" & ZeroLeft(STR, 3)

Thanks, small problem though. This function fires on Form OnLoad so now if I open this form and then close it the record gets saved in the tblClass immediately. I want the user to be asked if he/she want's the record to be saved Yes/No. I tried putting the code in On Dirty but that did not help. I also tried putting

If MsgBox("Do you want to save this record?", vbYesNo, "Record Change") = vbYes Then
      DoCmd.RunCommand acCmdSaveRecord
Else
      Cancel = True
 End If

in the Form On Close but still no results.

[nycman]

Ok Got this one too!

On Before Update Event

Dim strMsg As String
Dim iResponse As Integer


' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."


' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
DoCmd.RunCommand acCmdUndo


' Cancel the update.
Cancel = True
End If