returing a WBS

  • Thread starter Thread starter Shannon
  • Start date Start date
S

Shannon

Can Microsoft access return a number count.

For example.
I have stored a WBS # of 5.4.3.1 for a record
I there any way/coding that when i add a new record a next
WBS# will increment to the next new record of 5.4.3.2 and
so on? This is doable in microsoft project, don't know if
there's any way to do it in access.

Thanks,
 
Hi,

Do it in a form. There, you can use VBA code to split the string of the
last entered record (keep its value in a form global variable, in the after
update event of the form), and, in the onCurrent event, for a new record,
propose the incremented string:


=================in the OnCurrent event
... ' whatever you already have in this event

Debug.Assert True ' will fail at compile time
' if not under VBA6

Dim x( ) As String

If Me.NewRecord And 0 <> len(globalLastWBS) Then
x=Split(globalLastWBS, ".")
x(3)=CStr(1+val(x(3)))
Me.WBS=Join(x, "." )
End If

.... ' whatever else you may decide to add

======================


where globalLastWBS is the global variable you set in the After Update event
of the form:


globalLastWBS = Me.WBS


(Dim the variable in the form declaration section, the section that is
before any code, after the Option Compare Database and the Option
xplicit )


You need VBA6 (that means, Access 2000 or later) to get Split(), Join(),
and simple array assignment ( as in x=Split( ... ) , as well as
Debug.Assert ).




Hoping it may help,
Vanderghast, Access MVP
 
In addition,
I want the WBS # to automatically put in he WBS #field
when the user try to add a new record. Would you please
be a little more specific of where the code should go
since i have a compile error when i try to declare the
globalLastWBS = Me.WBS

thanks,
 
HI,



It seems we have a discussion going on in two different threads, right?
Anyhow, for this problem,


1- declare the variable in the declaration section (the very top part of
the code) of the form:


Option Compare Database
Option Explicit
Private globalLastWBS as String ' add this line


2- in the FORM AfterUpdate event, not in Button236 AfterUpdate event:

Private Sub Form_AfterUpdate()
globalLastWBS= = Me.WBS
End Sub




where I assume you have a control Me.WBS, actually, in your form.


3- in the on current event of the form (that is the first one in the Event
tab of the Form properties), add the proposed code. If there is already
nothing in it, the whole subroutine is then just:


Private Sub Form_Current()
Dim x( ) As String
If Me.NewRecord And 0 <> len(globalLastWBS) Then
x=Split(globalLastWBS, ".")
x(3)=CStr(1+val(x(3)))
Me.WBS=Join(x, "." )
End If
End Sub






Hoping it may help,
Vanderghast, Access MVP
 
I still have problems.
I still don't know how i can coded so that the next newest
WBS # will show up automatically in my WBS box everytime
the user try to enter a new record.
thanks,
 
Hi,


With the supplied code, no WBS would be suggested as long as the user
doesn't append a record (or modify an existing one), but once that is done,
the "next" WBS would be suggested, for new records. If you don't want that
modifying a new record counts for starting the suggestion (after thought, it
is a little bit strange to do it, in fact), it is a matter to remove the
form after update event, and use the form before update event like:

If Me.NewRecord Then
globalLastWBS= Me.WBS
End if


and now, only if a new record is entered would the code starts making
suggestion for new WBS number.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top