Here is a something I used recently to increment job numbers for a
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_AfterUp date()
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("SiteCoun ter", "tblCounterType 1"))
intLastType3 = (DMax("Material Counter", "tblCounterType 3"))
dtYear2 = Format(Now, "YY")
dtYear3 = Format(Now, "YY")
Select Case Me.Combo23.Text
Case "Furnish & Install Track Builder"
DoCmd.OpenForm "frmAssignJobNu m_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.openrecordse t("tblCounterTy pe1")
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.openrecordse t("tblCounterTy pe3")
rs.AddNew
rs!MaterialCoun ter = intLastType3 + 1
rs.Update
rs.Close
MsgBox "The new Job# has been recorded", vbInformation, "Tech-con
Job Log"
End Select
End Sub
HTH
Jason
paulmac106@hotm ail.com (PaulMac) wrote in message news:<1ee8a467. 0404010724.2e0f c34d@posting.go ogle.com>...[color=blue]
> Thanks Rick,
>
> Could you please post the Dir() code that you use.
>
> thanks,
> Paul[/color]
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_AfterUp date()
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("SiteCoun ter", "tblCounterType 1"))
intLastType3 = (DMax("Material Counter", "tblCounterType 3"))
dtYear2 = Format(Now, "YY")
dtYear3 = Format(Now, "YY")
Select Case Me.Combo23.Text
Case "Furnish & Install Track Builder"
DoCmd.OpenForm "frmAssignJobNu m_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.openrecordse t("tblCounterTy pe1")
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.openrecordse t("tblCounterTy pe3")
rs.AddNew
rs!MaterialCoun ter = intLastType3 + 1
rs.Update
rs.Close
MsgBox "The new Job# has been recorded", vbInformation, "Tech-con
Job Log"
End Select
End Sub
HTH
Jason
paulmac106@hotm ail.com (PaulMac) wrote in message news:<1ee8a467. 0404010724.2e0f c34d@posting.go ogle.com>...[color=blue]
> Thanks Rick,
>
> Could you please post the Dir() code that you use.
>
> thanks,
> Paul[/color]