last input as a default value

[stickooo]

Hi,

I have to input : "start" and "end"
I want to have "end n" value as a default value for "start n+1" (i.e. start=10, end=24; start=24 end=37; start=37 etc)

Is it possible?
thanks

 

[VBslammer]

Just declare a module-level variable and keep track of the latest 'end' value in that field's AfterUpdate() event. Then, in the form's Current() event, check to see if you are in new record and set the start value to the last end value (or end + 1, whatever the case may be):

Option Compare Database
Option Explicit

Private mlngCurrentEnd As Long

Private Sub EndNumber_AfterUpdate()
If Not IsNull(EndNumber) Then
mlngCurrentEnd = EndNumber
End If
End Sub

Private Sub Form_Current()
If Me.NewRecord And mlngCurrentEnd > 0 Then
Me.StartNumber = mlngCurrentEnd + 1
End If
End Sub

VBSlammer

 

[stickooo]

Hi,

Thanks..it works fine.

However, the problem is this form is open up accordingly to a data from the main form. You can see it below:



Private Sub Form_BeforeInsert(Cancel As Integer)

On Error GoTo Err_Form_BeforeInsert
Me![ClientID] = Forms![Client]![ClientID]

Exit_Form_BeforeInsert:
Exit Sub

Err_Form_BeforeInsert:
MsgBox Err.Description
Resume Exit_Form_BeforeInsert

End Sub


So Basically every client has different data.
If you don't mind, would you tell me how to do this ?

thanks

 

[VBslammer]

Just create a public property in the main form to allow access to the private variable:

Public Property Get NewStartNumber() As Long
If mlngCurrentEnd > 0 Then
NewStartNumber = mlngCurrentEnd + 1
End If
End Property

Then in the called form use:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![ClientID] = Forms![Client].[ClientID]
Me![StartNumber] = Forms![Client].NewStartNumber
End Sub

Usually when you get to the point where you have to access routines from more that one place it's a good idea to put them in a module. You could just create a function in a standard module to get the latest EndNumber + 1:

Public glngEndNumber As Long

Public Function NewStartNumber() As Long
If glngEndNumber > 0 Then
NewStartNumber = glngEndNumber + 1
End If
End Function

This way, if you add a record to either form, you can just change the global variable in the form's EndNumber_AfterUpdate() event and the next 'NewStartNumber' will be valid for whichever form wants it.

VBSlammer