Need Help Creating a ID Number

[mattc66 via AccessMonster.com]

Hi All,

I am creating a drawing request tool for my company. The drawing request
number will be the ID number of my record followed by a letter. The first
letter will be A and each revision will follow sequentially B..C..D and etc.

So if my record ID is 1000 the first drawing number will be 1000A and if
there is a revsions it will be 1000B.

My plan is to have a request button that the user will press to generate the
Drawing number. I then will want to store these Drawing numbers in a subform.
Once our cad department has completed the drawing they will save them as the
drawing number. So using my example the first drawing will be saved as 1000A.
dwf. I will create a hyperlink from my subform to these files.

Can some one help me with the code I would need in order create the drawing
number and then how to store it in my subform.

Thanks
Matt

 

[UpRider]

Matt said:

My plan is to have a request button that the user will press to generate
the
Drawing number.

Matt, more procedural info. When the user presses the button, how do we
know whether the new number is for a Rev or an original?
Is the numeric part of the drawing number the PK autonumber for the table?
If so, we need only generate and store the alpha letter, as we already have
the numeric part.
Will there ever be more than 25 Revs? If so what happens after Z?
Also, the drawing number cannot be stored in a subform. It must be stored in
a table.
Can you supply the name of that table and the names of the ID and drawing
number letter.

With this info, I think we can begin to see the big picture for the required
code.

UpRider

 

[mattc66 via AccessMonster.com]

The numeric part of the drawing number is the autonumber from the main form
table. So yes we only need to generate and store the Alpha letter in a
related table. No, the most revisions we have on a drawing 4 or 5. If we get
to Z we got bigger issues.

ID Name: QuoteID
MainForm Name: frmQuotes
TableName: tblQuotes
SubForm Name: sfrmDrawingNumbers
TableName: tblDrawingNumbers

We could store the rev level in it's own feild and then join them in a query.

We can call that feild: revision

Thank you very much for your help.

Matt

Matt said:

My plan is to have a request button that the user will press to generate
the
Drawing number.

Matt, more procedural info. When the user presses the button, how do we
know whether the new number is for a Rev or an original?
Is the numeric part of the drawing number the PK autonumber for the table?
If so, we need only generate and store the alpha letter, as we already have
the numeric part.
Will there ever be more than 25 Revs? If so what happens after Z?
Also, the drawing number cannot be stored in a subform. It must be stored in
a table.
Can you supply the name of that table and the names of the ID and drawing
number letter.

With this info, I think we can begin to see the big picture for the required
code.

UpRider

Hi All,

[quoted text clipped - 22 lines]

Thanks
Matt

 

[UpRider]

Matt, download http://www.dbtc.org/zipmdb/drawingnumbers.zip
2 tables, a form, a subform and a little VBA
It might serve as a guideline that can help you along.

UpRider

The numeric part of the drawing number is the autonumber from the main
form
table. So yes we only need to generate and store the Alpha letter in a
related table. No, the most revisions we have on a drawing 4 or 5. If we
get
to Z we got bigger issues.

ID Name: QuoteID
MainForm Name: frmQuotes
TableName: tblQuotes
SubForm Name: sfrmDrawingNumbers
TableName: tblDrawingNumbers

We could store the rev level in it's own feild and then join them in a
query.

We can call that feild: revision

Thank you very much for your help.

Matt

Matt said:

My plan is to have a request button that the user will press to generate
the
Drawing number.

Matt, more procedural info. When the user presses the button, how do we
know whether the new number is for a Rev or an original?
Is the numeric part of the drawing number the PK autonumber for the table?
If so, we need only generate and store the alpha letter, as we already
have
the numeric part.
Will there ever be more than 25 Revs? If so what happens after Z?
Also, the drawing number cannot be stored in a subform. It must be stored
in
a table.
Can you supply the name of that table and the names of the ID and drawing
number letter.

With this info, I think we can begin to see the big picture for the
required
code.

UpRider

Hi All,

[quoted text clipped - 22 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

That is so awesome.. Thank you..

Matt, download http://www.dbtc.org/zipmdb/drawingnumbers.zip
2 tables, a form, a subform and a little VBA
It might serve as a guideline that can help you along.

UpRider

The numeric part of the drawing number is the autonumber from the main
form

[quoted text clipped - 47 lines]

 

[mattc66 via AccessMonster.com]

One more question - How would I store the last drawing number with rev level
on my main form in a field called CurrentDrawingNum.

Thanks again for all your help.

Matt, download http://www.dbtc.org/zipmdb/drawingnumbers.zip
2 tables, a form, a subform and a little VBA
It might serve as a guideline that can help you along.

UpRider

The numeric part of the drawing number is the autonumber from the main
form

[quoted text clipped - 47 lines]

 

[UpRider]

Matt, create an unbound text box on frmQuotes and name it CurrentDrawingNum
Below is the revised *complete* code module for frmQuotes.
It will do what you want.
Note that the format will force the numeric part of the drawing number to
display as 4 digits, with leading zeros.
That's only for the form display, it is not stored that way in the table.

UpRider

Option Compare Database
Option Explicit


Private Sub cmdAddRev_Click()
Dim strTopRev As Variant
Dim strSQL As String
If IsNull([QuoteID]) Then
MsgBox "Must have a quote before rev can be added"
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
End If
sfrmDrawingNumbers.Visible = True
strTopRev = DMax("[revision]", "tblDrawingNumbers", "[FKQuoteID] = " _
& Forms!frmQuotes![QuoteID])
If Len(strTopRev & vbNullString) = 0 Then
strTopRev = "A"
Else
strTopRev = Asc(strTopRev) + 1
strTopRev = Chr$(strTopRev)
End If
strSQL = "INSERT INTO tblDrawingNumbers (fkquoteID, revision) " _
& "VALUES (" & QuoteID & " , '" & strTopRev & "')"
CurrentDb.Execute strSQL, dbFailOnError
Me.sfrmDrawingNumbers.Requery
Call subUpdateMainform '<<new
End Sub

Private Sub Form_Current()
sfrmDrawingNumbers.Visible = Not Me.NewRecord
Call subUpdateMainform '<<new
End Sub

'below all new
Sub subUpdateMainform()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString
If Not Me.NewRecord Then
strDwgLetterRev = DMax("revision", "tblDrawingNumbers", _
"FKQuoteID = " & [QuoteID])
CurrentDrawingNum = Format(QuoteID, "0000") + strDwgLetterRev
End If
End Sub

 

[mattc66 via AccessMonster.com]

When I replace the code with the below and after I have added the unbound
test box, I get and error message when I open the form. It says Ambiguouse
name detected: Form_Current.

Matt, create an unbound text box on frmQuotes and name it CurrentDrawingNum
Below is the revised *complete* code module for frmQuotes.
It will do what you want.
Note that the format will force the numeric part of the drawing number to
display as 4 digits, with leading zeros.
That's only for the form display, it is not stored that way in the table.

UpRider

Option Compare Database
Option Explicit

Private Sub cmdAddRev_Click()
Dim strTopRev As Variant
Dim strSQL As String
If IsNull([QuoteID]) Then
MsgBox "Must have a quote before rev can be added"
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
End If
sfrmDrawingNumbers.Visible = True
strTopRev = DMax("[revision]", "tblDrawingNumbers", "[FKQuoteID] = " _
& Forms!frmQuotes![QuoteID])
If Len(strTopRev & vbNullString) = 0 Then
strTopRev = "A"
Else
strTopRev = Asc(strTopRev) + 1
strTopRev = Chr$(strTopRev)
End If
strSQL = "INSERT INTO tblDrawingNumbers (fkquoteID, revision) " _
& "VALUES (" & QuoteID & " , '" & strTopRev & "')"
CurrentDb.Execute strSQL, dbFailOnError
Me.sfrmDrawingNumbers.Requery
Call subUpdateMainform '<<new
End Sub

Private Sub Form_Current()
sfrmDrawingNumbers.Visible = Not Me.NewRecord
Call subUpdateMainform '<<new
End Sub

'below all new
Sub subUpdateMainform()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString
If Not Me.NewRecord Then
strDwgLetterRev = DMax("revision", "tblDrawingNumbers", _
"FKQuoteID = " & [QuoteID])
CurrentDrawingNum = Format(QuoteID, "0000") + strDwgLetterRev
End If
End Sub

One more question - How would I store the last drawing number with rev
level
on my main form in a field called CurrentDrawingNum.

Thanks again for all your help.

 

[mattc66 via AccessMonster.com]

Dis-regard I found the error.

When I replace the code with the below and after I have added the unbound
test box, I get and error message when I open the form. It says Ambiguouse
name detected: Form_Current.

Matt, create an unbound text box on frmQuotes and name it CurrentDrawingNum
Below is the revised *complete* code module for frmQuotes.

[quoted text clipped - 55 lines]

 

[mattc66 via AccessMonster.com]

One more error that I can't figure out. Compile Error: Variable not defined
-

Sub subUpdateMainForm()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString

Matt, create an unbound text box on frmQuotes and name it CurrentDrawingNum
Below is the revised *complete* code module for frmQuotes.
It will do what you want.
Note that the format will force the numeric part of the drawing number to
display as 4 digits, with leading zeros.
That's only for the form display, it is not stored that way in the table.

UpRider

Option Compare Database
Option Explicit

Private Sub cmdAddRev_Click()
Dim strTopRev As Variant
Dim strSQL As String
If IsNull([QuoteID]) Then
MsgBox "Must have a quote before rev can be added"
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
End If
sfrmDrawingNumbers.Visible = True
strTopRev = DMax("[revision]", "tblDrawingNumbers", "[FKQuoteID] = " _
& Forms!frmQuotes![QuoteID])
If Len(strTopRev & vbNullString) = 0 Then
strTopRev = "A"
Else
strTopRev = Asc(strTopRev) + 1
strTopRev = Chr$(strTopRev)
End If
strSQL = "INSERT INTO tblDrawingNumbers (fkquoteID, revision) " _
& "VALUES (" & QuoteID & " , '" & strTopRev & "')"
CurrentDb.Execute strSQL, dbFailOnError
Me.sfrmDrawingNumbers.Requery
Call subUpdateMainform '<<new
End Sub

Private Sub Form_Current()
sfrmDrawingNumbers.Visible = Not Me.NewRecord
Call subUpdateMainform '<<new
End Sub

'below all new
Sub subUpdateMainform()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString
If Not Me.NewRecord Then
strDwgLetterRev = DMax("revision", "tblDrawingNumbers", _
"FKQuoteID = " & [QuoteID])
CurrentDrawingNum = Format(QuoteID, "0000") + strDwgLetterRev
End If
End Sub

One more question - How would I store the last drawing number with rev
level
on my main form in a field called CurrentDrawingNum.

Thanks again for all your help.

 

[UpRider]

Does the compile error give you a dialog box with a Debug button? If so,
click that button. The code window should open with the offending line
highlighted in yellow. Please post that line.

UpRider

One more error that I can't figure out. Compile Error: Variable not
defined
-

Sub subUpdateMainForm()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString

Matt, create an unbound text box on frmQuotes and name it
CurrentDrawingNum
Below is the revised *complete* code module for frmQuotes.
It will do what you want.
Note that the format will force the numeric part of the drawing number to
display as 4 digits, with leading zeros.
That's only for the form display, it is not stored that way in the table.

UpRider

Option Compare Database
Option Explicit

Private Sub cmdAddRev_Click()
Dim strTopRev As Variant
Dim strSQL As String
If IsNull([QuoteID]) Then
MsgBox "Must have a quote before rev can be added"
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
End If
sfrmDrawingNumbers.Visible = True
strTopRev = DMax("[revision]", "tblDrawingNumbers", "[FKQuoteID] = " _
& Forms!frmQuotes![QuoteID])
If Len(strTopRev & vbNullString) = 0 Then
strTopRev = "A"
Else
strTopRev = Asc(strTopRev) + 1
strTopRev = Chr$(strTopRev)
End If
strSQL = "INSERT INTO tblDrawingNumbers (fkquoteID, revision) " _
& "VALUES (" & QuoteID & " , '" & strTopRev & "')"
CurrentDb.Execute strSQL, dbFailOnError
Me.sfrmDrawingNumbers.Requery
Call subUpdateMainform '<<new
End Sub

Private Sub Form_Current()
sfrmDrawingNumbers.Visible = Not Me.NewRecord
Call subUpdateMainform '<<new
End Sub

'below all new
Sub subUpdateMainform()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString
If Not Me.NewRecord Then
strDwgLetterRev = DMax("revision", "tblDrawingNumbers", _
"FKQuoteID = " & [QuoteID])
CurrentDrawingNum = Format(QuoteID, "0000") + strDwgLetterRev
End If
End Sub

One more question - How would I store the last drawing number with rev
level
on my main form in a field called CurrentDrawingNum.

Thanks again for all your help.

 

[mattc66 via AccessMonster.com]

This is the offending line: CurrentDrawingNum = vbNullString

Does the compile error give you a dialog box with a Debug button? If so,
click that button. The code window should open with the offending line
highlighted in yellow. Please post that line.

UpRider

One more error that I can't figure out. Compile Error: Variable not
defined

[quoted text clipped - 64 lines]

 

[UpRider]

OK, try
CurrentDrawingNum = ""

UpRider

This is the offending line: CurrentDrawingNum = vbNullString

Does the compile error give you a dialog box with a Debug button? If so,
click that button. The code window should open with the offending line
highlighted in yellow. Please post that line.

UpRider

One more error that I can't figure out. Compile Error: Variable not
defined

[quoted text clipped - 64 lines]

Thanks again for all your help.

 

[mattc66 via AccessMonster.com]

Nope - I get the same message - Variable not defined.

OK, try
CurrentDrawingNum = ""

UpRider

This is the offending line: CurrentDrawingNum = vbNullString

[quoted text clipped - 9 lines]

 

[UpRider]

It can't find the control name CurrentDrawingNum. That should be the name
of the textbox that we recently added to show the drawing number on
frmQuotes (not the subform). The name is on the property sheet tab OTHER
for the textbox. Also make sure the textbox is unbound. Double check that,
then it wouldn't hurt to try
Me.CurrentDrawingNum = ""

UpRider

Nope - I get the same message - Variable not defined.

OK, try
CurrentDrawingNum = ""

UpRider

This is the offending line: CurrentDrawingNum = vbNullString

[quoted text clipped - 9 lines]

Thanks again for all your help.

 

[UpRider]

I have updated http://www.dbtc.org/zipmdb/drawingnumbers.zip

UpRider

Nope - I get the same message - Variable not defined.

OK, try
CurrentDrawingNum = ""

UpRider

This is the offending line: CurrentDrawingNum = vbNullString

[quoted text clipped - 9 lines]

Thanks again for all your help.

 

[mattc66 via AccessMonster.com]

Okay - that works

Thanks

I have updated http://www.dbtc.org/zipmdb/drawingnumbers.zip

UpRider

Nope - I get the same message - Variable not defined.

[quoted text clipped - 8 lines]

 

[mattc66 via AccessMonster.com]

Hi UpRider,

I am getting an error. Open the drawingnumbers database. Create a new record,
but don't create a REV. Then go back one record and then back the new record.
You get an error message Error 94 Invalid use of null.

Do you get the same error?

I have updated http://www.dbtc.org/zipmdb/drawingnumbers.zip

UpRider

Nope - I get the same message - Variable not defined.

[quoted text clipped - 8 lines]

 

[UpRider]

Matt change the function to this:
Sub subUpdateMainform()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString
If Not Me.NewRecord Then
strDwgLetterRev = Nz(DMax("revision", "tblDrawingNumbers", _
"FKQuoteID = " & [QuoteID]))
CurrentDrawingNum = Format(QuoteID, "0000") + strDwgLetterRev
End If
End Sub

The drawing mumber will still display on the main form but without the rev
letter. Is that OK?

UpRider

Hi UpRider,

I am getting an error. Open the drawingnumbers database. Create a new
record,
but don't create a REV. Then go back one record and then back the new
record.
You get an error message Error 94 Invalid use of null.

Do you get the same error?

I have updated http://www.dbtc.org/zipmdb/drawingnumbers.zip

UpRider

Nope - I get the same message - Variable not defined.

[quoted text clipped - 8 lines]

Thanks again for all your help.

 

[mattc66 via AccessMonster.com]

Hi UpRider,

This is working great. I would like to incorporate one more option.

I would like to add the following HyperLink. I created a field in the
tblDrawingNumbers called FileLocation. It's a HyperLink file type. Could you
help me incorporate this into the SQL Statement.

"W:\ROOMS Daily Drawings\DWF\" & [FKQuoteID] & [Revision]&".dwf"

Thanks Matt

Matt change the function to this:
Sub subUpdateMainform()
Dim strDwgLetterRev As String
CurrentDrawingNum = vbNullString
If Not Me.NewRecord Then
strDwgLetterRev = Nz(DMax("revision", "tblDrawingNumbers", _
"FKQuoteID = " & [QuoteID]))
CurrentDrawingNum = Format(QuoteID, "0000") + strDwgLetterRev
End If
End Sub

The drawing mumber will still display on the main form but without the rev
letter. Is that OK?

UpRider

Hi UpRider,

[quoted text clipped - 15 lines]