Need Help Creating a ID Number

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

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
 
Matt said:
My plan is to have a request button that the user will press to generate
the
Drawing number.
Click to expand...

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 said:
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

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
Click to expand...
 
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.
Click to expand...

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,
Click to expand...
[quoted text clipped - 22 lines]
Thanks
Matt
Click to expand...
Click to expand...
 
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

mattc66 via AccessMonster.com said:
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.
Click to expand...

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,
Click to expand...
[quoted text clipped - 22 lines]
Thanks
Matt
Click to expand...
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
Click to expand...
 
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
Click to expand...
[quoted text clipped - 47 lines]
Click to expand...
 
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
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
[quoted text clipped - 55 lines]
Click to expand...
 
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.
Click to expand...
Click to expand...
 
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

mattc66 via AccessMonster.com said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
[quoted text clipped - 64 lines]
Click to expand...
 
OK, try
CurrentDrawingNum = ""

UpRider

mattc66 via AccessMonster.com said:
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
Click to expand...
[quoted text clipped - 64 lines]
Thanks again for all your help.
Click to expand...
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
Click to expand...
 
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

mattc66 via AccessMonster.com said:
Nope - I get the same message - Variable not defined.
OK, try
CurrentDrawingNum = ""

UpRider
This is the offending line: CurrentDrawingNum = vbNullString
Click to expand...
[quoted text clipped - 9 lines]
Thanks again for all your help.
Click to expand...
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
Click to expand...
 
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.
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
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

mattc66 via AccessMonster.com said:
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.
Click to expand...
[quoted text clipped - 8 lines]
Thanks again for all your help.
Click to expand...
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
Click to expand...
 
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,
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
Back
Top