AfterUpdate code to generate new sequential number

[Jan Il]

Hi all Access 2003 - XP Pro SP2

I have a code that Dirk Goldgar provided for a control on a data entry form
for me here some time ago and it works great. I have need of a very similar
function for a new project. The code I now have is in the AfterUpdate event
of the control on my current form:

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub

I need to have a very similar sequential number type of function on a data
entry form that will create a new In-house code when either WK is entered in
the control, like the DBT above. and or automatically generated when data
is entered in one of three other controls on the form.

So far I have tried to apply the above code with adaptations for DBT for the
in-house WK number for the first part, but it does not generate the new
number when entering the WK.
I am also not sure what type of function I will need to add for the second
part, to generate a new number when data is entered one of the three other
controls. Should it be an If or Lookup? I've checked the Help and tried a
few of the examples that I thought might work, but, so far nothing, so
gather I'm going in the wrong direction.

The name of the first control is txtWKCode, where the new WK in-house number
will be generated when WK is entered in the control. The other three
controls are txtMfgUPCCode, txtVendorCode, and txtPartNo. If data is
entered into the txtMfgUPCCode control the WK number is generated and the
code stops looking, and the record is completed. If there is no data
entered in the txtMfgUPCCode then it should look at the txtVendorCode, if
there is data there it stops looking. If there is nothing there then it
looks in the txtPartNo control and if there is data there then it generates
the new number and stops looking. If there is nothing in any of the 3
controls, then it says a new number can not be generated and the record can
not be saved.

Sort of like:

Check the fields:

1. Check txtMfgUPCCode, if something is there create a new WK number and
stop looking. If nothing there, keep looking....
2. Check txtVendorCode, if something is there create a new WK number and
stop looking. If nothing there, keep looking...
3. Check txtPartNo, if something is there create a new WK number and stop
looking. If nothing is there, stop looking and display dialog box "No data
was found, WK number cannot be created. Record cannot be saved.

Something along those lines is the function I need for the new entry form.
Thus, I am not sure exactly what format of code I need to do this kind of
search function.

I would really appreciate any references and/or suggestions.

Jan
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.

 

[Douglas J. Steele]

Sounds as though you need a routine that you can call from the AfterUpdate
event of all three controls.

Something like the following pseudocode:

Sub GenerateNewKey()

If Len(Me.txtMfgUPCCode & "") > 0 Then
' create a new WK number
ElseIf Len(Me.txtVendorCode & "") > 0 Then
' create a new WK number
ElseIf Len(Me.txtPartNo & "") > 0 The
' create a new WK number
Else
MsgBox "No data was found, WK number cannot be created. Record cannot be
saved."
End If

End Sub

Then, in the 3 routines, you'd have something like:

Private Sub txtMfgUPCCode_AfterUpdate()

Call GenerateNewKey

End Sub

 

[Jan Il]

Hi Doug!

Sounds as though you need a routine that you can call from the AfterUpdate
event of all three controls.

Something like the following pseudocode:

Sub GenerateNewKey()

If Len(Me.txtMfgUPCCode & "") > 0 Then
' create a new WK number
ElseIf Len(Me.txtVendorCode & "") > 0 Then
' create a new WK number
ElseIf Len(Me.txtPartNo & "") > 0 Then
' create a new WK number
Else
MsgBox "No data was found, WK number cannot be created. Record cannot
be saved."
End If

End Sub

Then, in the 3 routines, you'd have something like:

Private Sub txtMfgUPCCode_AfterUpdate()

Call GenerateNewKey

End Sub

That does look like a good suggestion to achieve the results I need. I'll
give that a try and see how it goes.

Thank you very much for your time and help, I really appreciate it.


Jan
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.