Difficult Question-Help with setting up specific functions for a F

[Kyla Dockery]

Help--
I am setting up a database for my boss and he wants to be able to select
from a list box in one field to have the next field automatically populate a
corresponding letter and next availabe number in that series of letter number
lists. I have already set up the list box with the four available choices,
and I got help yesterday with having the corresponding letter automatically
populate in the next field, but I need to set it up to where it automatically
pulls the letter and next available number up together. This is an example
of what I need it to look like when it comes up:

Document Type : Tooling (chosen from a list box)

Document Number: T00001 (automatically populates T for Tooling and the next
availble number for that particular list up to T99999.

Please help!!

 

[John Vinson]

Help--
I am setting up a database for my boss and he wants to be able to select
from a list box in one field to have the next field automatically populate a
corresponding letter and next availabe number in that series of letter number
lists. I have already set up the list box with the four available choices,
and I got help yesterday with having the corresponding letter automatically
populate in the next field, but I need to set it up to where it automatically
pulls the letter and next available number up together. This is an example
of what I need it to look like when it comes up:

Document Type : Tooling (chosen from a list box)

Document Number: T00001 (automatically populates T for Tooling and the next
availble number for that particular list up to T99999.

Please help!!

If this is a single-user system, i.e. you're not likely to have two
users both trying to enter new T records at the same moment, it's easy
enough to do this using a Form (table datasheets don't have usable
events!!! so don't use them).

Try putting code like this in the Listbox's AfterUpdate event (click
the ... icon by the event and choose Code Builder). Note that I'm
strongly suggesting that you remove the blanks from your fieldname,
they can be a real pain! The code assumes the fields are named
DocumentType and DocumentNumber, and that the Form controls bound to
these fields are named lstDocumentType and txtDocumentNumber.

Private Sub lstDocumentType_AfterUpdate()
Dim strNext As String
Dim iNext As Integer
If IsNull(Me!txtDocumentNumber) Then ' don't stomp on existing values
strNext = NZ(DMax("[DocumentNumber]", "[yourtable]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")
iNext = Val(Mid(strNext, 2)) ' extract the number as an integer
If iNext = 99999 Then
Msgbox "Close down the office for the year, too many documents"
Exit Sub
Else
Me!txtDocumentNumber = Left(strNext, 1) & _
Format(iNext + 1, "00000")
End If
Else
MsgBox "This document already has a document number assigned!" _
& vbCrLf & "Erase the DocumentNumber before changing Type.", _
vbOKOnly
End If
End Sub

John W. Vinson[MVP]

 

[Kyla Dockery]

John,

Thank you for the code, I am having one (hopefully small) problem:

When I try to select from my list box it says:Microsoft can't find the field
txtDocumentNumber

If IsNull(Me!txtDocumentNumber) Then ' don't stomp on existing values
Did I type something in wrong?

Help--
I am setting up a database for my boss and he wants to be able to select
from a list box in one field to have the next field automatically populate a
corresponding letter and next availabe number in that series of letter number
lists. I have already set up the list box with the four available choices,
and I got help yesterday with having the corresponding letter automatically
populate in the next field, but I need to set it up to where it automatically
pulls the letter and next available number up together. This is an example
of what I need it to look like when it comes up:

Document Type : Tooling (chosen from a list box)

Document Number: T00001 (automatically populates T for Tooling and the next
availble number for that particular list up to T99999.

Please help!!

If this is a single-user system, i.e. you're not likely to have two
users both trying to enter new T records at the same moment, it's easy
enough to do this using a Form (table datasheets don't have usable
events!!! so don't use them).

Try putting code like this in the Listbox's AfterUpdate event (click
the ... icon by the event and choose Code Builder). Note that I'm
strongly suggesting that you remove the blanks from your fieldname,
they can be a real pain! The code assumes the fields are named
DocumentType and DocumentNumber, and that the Form controls bound to
these fields are named lstDocumentType and txtDocumentNumber.

Private Sub lstDocumentType_AfterUpdate()
Dim strNext As String
Dim iNext As Integer
If IsNull(Me!txtDocumentNumber) Then ' don't stomp on existing values
strNext = NZ(DMax("[DocumentNumber]", "[yourtable]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")
iNext = Val(Mid(strNext, 2)) ' extract the number as an integer
If iNext = 99999 Then
Msgbox "Close down the office for the year, too many documents"
Exit Sub
Else
Me!txtDocumentNumber = Left(strNext, 1) & _
Format(iNext + 1, "00000")
End If
Else
MsgBox "This document already has a document number assigned!" _
& vbCrLf & "Erase the DocumentNumber before changing Type.", _
vbOKOnly
End If
End Sub

John W. Vinson[MVP]

 

[jiawen]

good

Help--
I am setting up a database for my boss and he wants to be able to select
from a list box in one field to have the next field automatically populate
a
corresponding letter and next availabe number in that series of letter
number
lists. I have already set up the list box with the four available
choices,
and I got help yesterday with having the corresponding letter
automatically
populate in the next field, but I need to set it up to where it
automatically
pulls the letter and next available number up together. This is an
example
of what I need it to look like when it comes up:

Document Type : Tooling (chosen from a list box)

Document Number: T00001 (automatically populates T for Tooling and the
next
availble number for that particular list up to T99999.

Please help!!

If this is a single-user system, i.e. you're not likely to have two
users both trying to enter new T records at the same moment, it's easy
enough to do this using a Form (table datasheets don't have usable
events!!! so don't use them).

Try putting code like this in the Listbox's AfterUpdate event (click
the ... icon by the event and choose Code Builder). Note that I'm
strongly suggesting that you remove the blanks from your fieldname,
they can be a real pain! The code assumes the fields are named
DocumentType and DocumentNumber, and that the Form controls bound to
these fields are named lstDocumentType and txtDocumentNumber.

Private Sub lstDocumentType_AfterUpdate()
Dim strNext As String
Dim iNext As Integer
If IsNull(Me!txtDocumentNumber) Then ' don't stomp on existing values
strNext = NZ(DMax("[DocumentNumber]", "[yourtable]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")
iNext = Val(Mid(strNext, 2)) ' extract the number as an integer
If iNext = 99999 Then
Msgbox "Close down the office for the year, too many documents"
Exit Sub
Else
Me!txtDocumentNumber = Left(strNext, 1) & _
Format(iNext + 1, "00000")
End If
Else
MsgBox "This document already has a document number assigned!" _
& vbCrLf & "Erase the DocumentNumber before changing Type.", _
vbOKOnly
End If
End Sub

John W. Vinson[MVP]