Excel Forum

Code: Select all

Function ModRunPoNo() As String
Dim Db As DAO.Database
Dim Rstpo As DAO.Recordset
Dim Stryear As String                                                 
Dim Strdummy As String                                             
Dim StrSQL As String
Dim StrPlat As String, StrOrderNo As String
Stryear = Format(Right(Year(Date), 2), "00")
Strdummy = "P"
StrOrderNo = Format(Mid(!PONo, 4, 3), "000")
StrPlat = Strdummy & Stryear & StrOrderNo & "-"
StrSQL = "SELECT POHeader.* FROM POHeader WHERE POHeader.PONo" & _
         " Like '" & StrPlat & "*'" & _
         " Order by POHeader.PONo"

Set Db = CurrentDb
Set Rstpo = Db.OpenRecordset(StrSQL, dbOpenDynaset)
    With Rstpo
        If .RecordCount > 0 Then
            .MoveLast
            ModRunPoNo = StrPlat & "-" & Format(Str(Right(!PONo, 3) + 1), "000")
        Else
            ModRunPoNo = StrPlat & "-" & "000"
        End If
        .Close
    End With
End Function