John- There's a little problem!!

[Guest]

I put in everything exactly as you had it (except of course for Entering my
table name) and nothing happened. Here is what I did:

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]", "[Engineering_Tooling_Table]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")
iNext = Val(Mid(strNext, 2)) ' extract the number as an integer
If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
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 Vinson]

I put in everything exactly as you had it (except of course for Entering my
table name) and nothing happened. Here is what I did:

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]", "[Engineering_Tooling_Table]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")
iNext = Val(Mid(strNext, 2)) ' extract the number as an integer
If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
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

"Nothing Happened" meaning... what? What steps did you take and what
did you see? Do you get any errors if you click Debug... Compile <your
database>?

You can put a Breakpoint in the code: click the mouse in the grey
vertical bar next to the If IsNull... line. This will let you step
through the code, and you can hover the mouse over strNext and iNext
to see if you're getting the values set as you expect.

John W. Vinson[MVP]

 

[Guest]

I did the Debug, compile Engineer and it gave me an error on my very last
Else statement saying that: Compile Error- Else without If. How do I fix
this? I tried researching it myself through the help guide, but everything I
try it doesn't like and gives my syntax errors.

I put in everything exactly as you had it (except of course for Entering my
table name) and nothing happened. Here is what I did:

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]", "[Engineering_Tooling_Table]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")
iNext = Val(Mid(strNext, 2)) ' extract the number as an integer
If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
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

"Nothing Happened" meaning... what? What steps did you take and what
did you see? Do you get any errors if you click Debug... Compile <your
database>?

You can put a Breakpoint in the code: click the mouse in the grey
vertical bar next to the If IsNull... line. This will let you step
through the code, and you can hover the mouse over strNext and iNext
to see if you're getting the values set as you expect.

John W. Vinson[MVP]

 

[John Vinson]

I did the Debug, compile Engineer and it gave me an error on my very last
Else statement saying that: Compile Error- Else without If. How do I fix
this? I tried researching it myself through the help guide, but everything I
try it doesn't like and gives my syntax errors.

hm. Are you CERTAIN that the code you posted (which does not give this
error when I put it into a form) is exactly the code in your form
module? Please doublecheck.

You can step through the code, "playing computer" - each IF statement
should have

IF <some condition> THEN
<do this if true>
ELSE
<do this if false>
END IF

or you can leave out the ELSE and the <do this if false> if you wish.
It sounds like the code (and it might be in some other SUB!!) has an
ELSE after an END IF, or without any IF at all.


John W. Vinson[MVP]

 

[Guest]

John,
Thank you for being so patient with me and for helping me to try to figure
this thing out!
Okay, I reentered the code and now I don't get any kind of debug error, but
when I actually go into the form and choose something (ex:Tooling) from the
list in Document Type, I expect to see T00001 populate in the Document Number
field. It will let me choose from the list, but nothing populates. Is there
anything else I need to set up to get this to work?

 

[John Vinson]

Okay, I reentered the code and now I don't get any kind of debug error, but
when I actually go into the form and choose something (ex:Tooling) from the
list in Document Type, I expect to see T00001 populate in the Document Number
field. It will let me choose from the list, but nothing populates. Is there
anything else I need to set up to get this to work?

Well, let's check some assumptions (my questions interspersed in the
code with >>> before them):

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]", _
"[Engineering_Tooling_Table]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")

What is the actual value returned by Me!lstDocumentType? It matches the DocumentType field in [Engineering_Tooling_Table]?
You're picking "Tooling" - I'm a bit worried that you may have different Tables (Tooling_Table???) for each type!
When you debug, what is returned in strNext?

iNext = Val(Mid(strNext, 2)) ' extract the number as an integer

If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
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]

 

[Guest]

There is a text box on the form named DocumentNumber with no txt attached to
it, with a control source of DocumentNumber. In the table design view, this
field's datatype is text.
The datatype of DocumentNumber is text. There are no values in the table
yet because it should populate from X00001 for each DocumentType chosen.

The value returned by by Me!lstDocumentType should be either Tooling;
Electrical; Pneumatic; or Facility. In the DocumentType field in
Engineering_Tooling_Table in the LookUp tab the Display Control is Combo Box,
The Row Source Type is Value List, the Row Source is Tooling; Electrical;
Pneumatic; Facility.

I only have one table, and its named Engineering Tooling Table. There are
no other tables in my database.

When I Debug I have simply gone into Debug and chosen Compile Engineer. It
doesn't return anything. I have also tried clicking beside the line of code
that you specified to highlight it red and tried to add watch to it. I don't
know if that is what I'm supposed to do but one of the lines (strNext)
returned this:
Watch : : strNext = Nz(DMax("[DocumentNumber]",
"[Engineering_Tooling_Table]", "[DocumentType] = '" & Me!lstDocumentType &
"'"), "X00000") : <Out of context> : Empty : Form_Engineering Tooling.lstD
The iNext stated that: it was an invalid watch expression.

I don't know how to execute a line, and don't even really know what that
means, and I don't get any error messages when I try to type info into the
database and save and close it.

I hope this helps!!!

Okay, I reentered the code and now I don't get any kind of debug error, but
when I actually go into the form and choose something (ex:Tooling) from the
list in Document Type, I expect to see T00001 populate in the Document Number
field. It will let me choose from the list, but nothing populates. Is there
anything else I need to set up to get this to work?

Well, let's check some assumptions (my questions interspersed in the
code with >>> before them):

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]", _
"[Engineering_Tooling_Table]", _
"[DocumentType] = '" & Me!lstDocumentType & "'"), "X00000")

What is the actual value returned by Me!lstDocumentType? It matches the DocumentType field in [Engineering_Tooling_Table]?
You're picking "Tooling" - I'm a bit worried that you may have different Tables (Tooling_Table???) for each type!
When you debug, what is returned in strNext?

iNext = Val(Mid(strNext, 2)) ' extract the number as an integer

If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
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]

 

[John Vinson]

There is a text box on the form named DocumentNumber with no txt attached to
it, with a control source of DocumentNumber. In the table design view, this
field's datatype is text.

My code assumes that the CONTROL is named txtDocumentNumber. Access
defaults to using the control name identical to the fieldname, but
this can cause confusion (as in this case)... I'd suggest renaming the
textbox.

The datatype of DocumentNumber is text. There are no values in the table
yet because it should populate from X00001 for each DocumentType chosen.
The value returned by by Me!lstDocumentType should be either Tooling;
Electrical; Pneumatic; or Facility.

And the prefix to the documentnumber should be T, E, P, F
respectively?

In the DocumentType field in
Engineering_Tooling_Table in the LookUp tab the Display Control is Combo Box,
The Row Source Type is Value List, the Row Source is Tooling; Electrical;
Pneumatic; Facility.

Well... I *DESPISE* the lookup field misfeature, but in this case it
might be OK (since you used a value list).

I only have one table, and its named Engineering Tooling Table. There are
no other tables in my database.

When I Debug I have simply gone into Debug and chosen Compile Engineer. It
doesn't return anything. I have also tried clicking beside the line of code
that you specified to highlight it red and tried to add watch to it. I don't
know if that is what I'm supposed to do but one of the lines (strNext)
returned this:
Watch : : strNext = Nz(DMax("[DocumentNumber]",
"[Engineering_Tooling_Table]", "[DocumentType] = '" & Me!lstDocumentType &
"'"), "X00000") : <Out of context> : Empty : Form_Engineering Tooling.lstD
The iNext stated that: it was an invalid watch expression.
I don't know how to execute a line, and don't even really know what that
means, and I don't get any error messages when I try to type info into the
database and save and close it.

You "execute" the code by selecting something from the listbox. When
you have a Breakpoint (you don't really need a Watch in this case) the
AfterUpdate event will fire when you click Tooling in the listbox; the
program will start to execute; when it reaches the red line it will
stop and open the VBA editor for you. You can then hit F8 to execute a
single line, or F9 to let the code run to completion.


Realizing that you have no data in your table, I now see I was in
error in one part of the code. Change

If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
Exit Sub
Else
Me!txtDocumentNumber = Left(strNext, 1) & _
Format(iNext + 1, "00000")
End If

to

If iNext = 99999 Then
MsgBox "No Additional Numbers Available!"
Exit Sub
Else
Me!txtDocumentNumber = Left(Me!lstDocumentType, 1) & _
Format(iNext + 1, "00000")
End If

to extract the first letter of the *DOCUMENT TYPE* rather than the
first letter of the previous record's DocumentNumber.

John W. Vinson[MVP]

 

[Guest]

Hi, Kyla.

You're going to have another little problem somewhere down the line.

Change:

Dim iNext As Integer

To:

Dim iNext As Long

Otherwise, your document numbers will never get past 32767. If you know for
a fact your numbers will never get that high, then you can keep iNext as an
Integer data type, but change:

If iNext = 99999 Then

To:

If iNext = 32767 Then

And I recommend adding error handling to your subroutine.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[John Vinson]

Change:

Dim iNext As Integer

To:

Dim iNext As Long

Whoops!

Thanks Gunny. That was an error on my part!

John W. Vinson[MVP]

 

['69 Camaro]

Happens to me all the time when I reuse code from other applications. "That
code worked just fine in the other application. What's diff . . . whoops!"



Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.