Incrementing a field without Autonumber

[tcveltma]

Hi Everyone,

I know this topic has been posted a lot, so I apologize for having to post another one, but the answers I've been finding seem to be more advanced than I know how to use.

I have a table called [MOC Tracking Log], with a field named [MOC ID Number]. The general format for this number is supposed to be (yyyy-0001). The last 4 digits are supposed to increment each time a new record is created (ie, 2007-0002, 2007-0003 etc). I have found some useful code (i think) to do the incrementation part itself. However:

1) I don't know how to set the original format to be (yyyy-0001)

2) I don't know where to put the code.

So far, I have this:

Private Sub Form_Load()
 
Format (Date = "yyyy")
 
 
Dim tdf As TableDef
Dim fld As Field
Dim num As Double
 
num = "0001"
 
Set tdf = CurrentDb.TableDefs("[MOC Tracking Log]")
Set fld = tdf.Fields("[MOC ID Number]")
 
fld.Value = (Date & "-" & num)
 
fld = ("SELECT Max(Right([MOC ID Number])) As MaxNum FROM [MOC Tracking Log]")
 
    num = fld!MaxNum + 1
 
End Sub
 

which is basically a random jumble of code snippets I've found and can only somewhat make sense of.

Any clarification, or links to understandable tutorial sites would be greatly appreciated.

Thanks,
Tiffany

[JConsulting]

Hi Everyone,

I know this topic has been posted a lot, so I apologize for having to post another one, but the answers I've been finding seem to be more advanced than I know how to use.

I have a table called [MOC Tracking Log], with a field named [MOC ID Number]. The general format for this number is supposed to be (yyyy-0001). The last 4 digits are supposed to increment each time a new record is created (ie, 2007-0002, 2007-0003 etc). I have found some useful code (i think) to do the incrementation part itself. However:

1) I don't know how to set the original format to be (yyyy-0001)

2) I don't know where to put the code.

So far, I have this:

Private Sub Form_Load()
 
Format (Date = "yyyy")
 
 
Dim tdf As TableDef
Dim fld As Field
Dim num As Double
 
num = "0001"
 
Set tdf = CurrentDb.TableDefs("[MOC Tracking Log]")
Set fld = tdf.Fields("[MOC ID Number]")
 
fld.Value = (Date & "-" & num)
 
fld = ("SELECT Max(Right([MOC ID Number])) As MaxNum FROM [MOC Tracking Log]")
 
    num = fld!MaxNum + 1
 
End Sub
 

which is basically a random jumble of code snippets I've found and can only somewhat make sense of.

Any clarification, or links to understandable tutorial sites would be greatly appreciated.

Thanks,
Tiffany

Form load initially, then you would want this each time you started a new record.

dim MyCount as long
if dcount("*","[MOC Tracking Log]")=0 then
me.[MOC ID Number].value = Format(Date(),"yyyy") & "-0001"
else
MyCount = myval = Right(DMax("[MOC ID Number]", "[MOC Tracking Log]"), 4)
me.[MOC ID Number].value = Format(Date(),"yyyy") & mycount
end if
 
 

J

[hyperpau]

Hi Everyone,

I know this topic has been posted a lot, so I apologize for having to post another one, but the answers I've been finding seem to be more advanced than I know how to use.

I have a table called [MOC Tracking Log], with a field named [MOC ID Number]. The general format for this number is supposed to be (yyyy-0001). The last 4 digits are supposed to increment each time a new record is created (ie, 2007-0002, 2007-0003 etc). I have found some useful code (i think) to do the incrementation part itself. However:

1) I don't know how to set the original format to be (yyyy-0001)

2) I don't know where to put the code.

So far, I have this:

Private Sub Form_Load()
 
Format (Date = "yyyy")
 
 
Dim tdf As TableDef
Dim fld As Field
Dim num As Double
 
num = "0001"
 
Set tdf = CurrentDb.TableDefs("[MOC Tracking Log]")
Set fld = tdf.Fields("[MOC ID Number]")
 
fld.Value = (Date & "-" & num)
 
fld = ("SELECT Max(Right([MOC ID Number])) As MaxNum FROM [MOC Tracking Log]")
 
num = fld!MaxNum + 1
 
End Sub
 

which is basically a random jumble of code snippets I've found and can only somewhat make sense of.

Any clarification, or links to understandable tutorial sites would be greatly appreciated.

Thanks,
Tiffany

Change this:

 fld.Value = (Date & "-" & num)
 

to this

 fld.Value = (Format(Date, "yyyy") & "-" & num)
 

[tcveltma]

dim MyCount as long
if dcount("*","[MOC Tracking Log]")=0 then
me.[MOC ID Number].value = Format(Date(),"yyyy") & "-0001"
else
MyCount = myval = Right(DMax("[MOC ID Number]", "[MOC Tracking Log]"), 4)
me.[MOC ID Number].value = Format(Date(),"yyyy") & mycount
end if
 

Thanks for the help, it seems to be working so far, with the exception of a minor compiler issue. Could you also tell me what the proper format is to replace "Me." for a table and field?

Thanks,
Tiffany

[JConsulting]

Thanks for the help, it seems to be working so far, with the exception of a minor compiler issue. Could you also tell me what the proper format is to replace "Me." for a table and field?

Thanks,
Tiffany

Hey,
replacing as in putting a variable in place of??

forms(sForm).form(sField).value for a simple form. Subforms are different.

[tcveltma]

Ok thanks, I'll try that

Tiffany