company. Create counter table with one integer filed (i.e.
tblCounter). Create a single record with the starting value (in you
case 001). Then, all you have to do is use the Dmax function to grab
the last value and after that value is used, apply code to insert a
record creating the next available number. Here is the code:
Private Sub Combo23_AfterUpdate()
Dim intLastType3 As Integer
Dim intLastType2 As Integer
Dim dtYear2 As Variant
Dim dtYear3 As Variant
Dim strType2 As String
Dim strType3 As String
intLastType2 = (DMax("SiteCounter", "tblCounterType1"))
intLastType3 = (DMax("MaterialCounter", "tblCounterType3"))
dtYear2 = Format(Now, "YY")
dtYear3 = Format(Now, "YY")
Select Case Me.Combo23.Text
Case "Furnish & Install Track Builder"
DoCmd.OpenForm "frmAssignJobNum_TrackBuilder"
Case "Furnish & Install Site Specific"
strType2 = intLastType2 & "-" & dtYear2
Me.txtJobNumber = strType2
Dim Db As Database, rs As Recordset
Set Db = CurrentDb()
Set rs = Db.openrecordset("tblCounterType1")
rs.AddNew
rs!SiteCounter = intLastType2 + 1
rs.Update
rs.Close
MsgBox "The new Job# has been recorded", vbInformation, "Tech-con
Job Log"
Case "Furnish (Material Only)"
strType3 = "$$" & intLastType3 & "-" & dtYear3
Me.txtJobNumber = strType3
Set Db = CurrentDb()
Set rs = Db.openrecordset("tblCounterType3")
rs.AddNew
rs!MaterialCounter = intLastType3 + 1
rs.Update
rs.Close
MsgBox "The new Job# has been recorded", vbInformation, "Tech-con
Job Log"
End Select
End Sub
HTH
Jason
pa********@hotmail.com (PaulMac) wrote in message news:<1e**************************@posting.google. com>...
Thanks Rick,
Could you please post the Dir() code that you use.
thanks,
Paul