If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
I'm working on a document control database (assigns document numbers and
tracks a document from inception to release and through revisions and signature approval process). Thanks to help from this forum, I've worked out a multi-field "ID" system to meet the requirements for the document numbering system and set up a query to display the document number with the multiple fields concatenated. Essentially: FORMAT: XXXYYZZZZRR-WWW Examples: 301AA2112B-000, 301D1492BB-002, 301TR1973A-003 Each set of letters is its own field (XXX is the main designator, YY is the document type, ZZZZ is the numeric series for the document, RR is the revision increment, and -WWW is essentially identifying multiple parts in an assembly). For sorting and assigning new numbers, I'm only concerned with the XXXYYZZZZ section and my query fills a table with the document numbers' individual fields and a separate index with the concatenated "ID" XXXYYZZZZ. I've set this to presort based on ascending values for these three pieces. I would like to set up another query or form that increments to tell me the next available document number in the ZZZZ series, based on which YY I select (for now, all the XXX indicators are the same, i.e. "301"). This will allow me to issue new numbers for documents without having to run a report to scan through all the numbers or manually go through the table. That's the goal anyway. I appreciate any insight offered! Thank you! Liane |
#2
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
You don't need a query or a form. A DMax function will do it for you.
It would be something like: NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc & " And [DocType] = """ & Me.txtDocType & """"),0) + 1 The DMax finds the current highest number filtered by the other two designatores. If no match is found (it is the first doc for this combination), the Nz function converts the Null returned by DMax to 0. Then 1 is added to the results to give you the next number. If you want it formatted for 4 zeros, then =Format(NextNumber,"0000") -- Dave Hargis, Microsoft Access MVP "LianeMT" wrote: I'm working on a document control database (assigns document numbers and tracks a document from inception to release and through revisions and signature approval process). Thanks to help from this forum, I've worked out a multi-field "ID" system to meet the requirements for the document numbering system and set up a query to display the document number with the multiple fields concatenated. Essentially: FORMAT: XXXYYZZZZRR-WWW Examples: 301AA2112B-000, 301D1492BB-002, 301TR1973A-003 Each set of letters is its own field (XXX is the main designator, YY is the document type, ZZZZ is the numeric series for the document, RR is the revision increment, and -WWW is essentially identifying multiple parts in an assembly). For sorting and assigning new numbers, I'm only concerned with the XXXYYZZZZ section and my query fills a table with the document numbers' individual fields and a separate index with the concatenated "ID" XXXYYZZZZ. I've set this to presort based on ascending values for these three pieces. I would like to set up another query or form that increments to tell me the next available document number in the ZZZZ series, based on which YY I select (for now, all the XXX indicators are the same, i.e. "301"). This will allow me to issue new numbers for documents without having to run a report to scan through all the numbers or manually go through the table. That's the goal anyway. I appreciate any insight offered! Thank you! Liane |
#3
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
Thanks, Dave! Here's what I've interpreted from your note. I apologize that
my variable names are cumbersome, but they fit a scheme I inherited. From your example: NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc & " And [DocType] = """ & Me.txtDocType & """"),0) + 1 I substituted my variables: NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator] = """ & Me.txtGroupDesignator & """"),0) +1 based on: XXX = Main Designator = ProgramDesignator - MainDoc YY = Document Type = GroupDesignator - DocType ZZZZ = Numeric Series = NumberBlock - series Table/Query = DocNumber_Qry - MyTable I've put this DMax function into an existing form where the administrator inputs the document numbers (populating the table/query for tracking the numbers) hoping to facilitate grabbing the next number being issued. Honestly I didn't know where else to put it. Essentially I would like the administrator to be able to enter the Program Designator (currently 301), and select the Group Designator to kick off the NextNumber being reported in a text field on the form where I've put the DMax function. Then the administrator can use the NextNumber to input the NumberBlock (series) identifier) and the other two fields to add this new number to the database. Unfortunately, I haven't linked the DMax function correctly, because I get the #Name? returned in the NextNumber field instead of the NextNumber I'm trying to report. I'm sure there's a more elegant way to do this, so again, I appreciate any input to either do this better or simply to fix the disconnect I have in the current implementation. Thanks much! |
#4
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
I would have a text box on the form for the Number Series.
I would also have a command button positioned next to it to retrieve the next number. In Design view, set the command button's Enabled Property to No. You only want it to work for new records. Then in the form Current event enable the button for new records and disable it for existing records: Me.cmdGetNewNumber.Enabled = Me.NewRecord The put the code in the Click event of the command button. -- Dave Hargis, Microsoft Access MVP "LianeMT" wrote: Thanks, Dave! Here's what I've interpreted from your note. I apologize that my variable names are cumbersome, but they fit a scheme I inherited. From your example: NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc & " And [DocType] = """ & Me.txtDocType & """"),0) + 1 I substituted my variables: NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator] = """ & Me.txtGroupDesignator & """"),0) +1 based on: XXX = Main Designator = ProgramDesignator - MainDoc YY = Document Type = GroupDesignator - DocType ZZZZ = Numeric Series = NumberBlock - series Table/Query = DocNumber_Qry - MyTable I've put this DMax function into an existing form where the administrator inputs the document numbers (populating the table/query for tracking the numbers) hoping to facilitate grabbing the next number being issued. Honestly I didn't know where else to put it. Essentially I would like the administrator to be able to enter the Program Designator (currently 301), and select the Group Designator to kick off the NextNumber being reported in a text field on the form where I've put the DMax function. Then the administrator can use the NextNumber to input the NumberBlock (series) identifier) and the other two fields to add this new number to the database. Unfortunately, I haven't linked the DMax function correctly, because I get the #Name? returned in the NextNumber field instead of the NextNumber I'm trying to report. I'm sure there's a more elegant way to do this, so again, I appreciate any input to either do this better or simply to fix the disconnect I have in the current implementation. Thanks much! |
#5
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
I would have a text box on the form for the Number Series.
I have this. I would also have a command button positioned next to it to retrieve the next number. I added this. In Design view, set the command button's Enabled Property to No. You only want it to work for new records. This makes sense. I did that. Then in the form Current event enable the button for new records and disable it for existing records: Me.cmdGetNewNumber.Enabled = Me.NewRecord After Access complained about “Me” I guessed that I needed to replace “Me” with the name of my form, thusly: DocNumber_Form.cmdNextNumber_Cmd.Enabled = DocNumber_Form.NewRecord Then put the code in the Click event of the command button. Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. I realize it may seem redundant to have both this “Next Document Number” text box and the “Number Series” text box, but here’s my rationale: The purpose of this form is to populate the list of document numbers as they are being issued. I need to know the Next number in the series when I have to issue a brand new number and I’m only concerned with the Number Base of XXXYYZZZZ. But I also need the ability to add the revision and sequence numbers when existing documents get revised or supplemental pieces are added to them (without issuing entirely new numbers). The form has fields for these two pieces of information; I’m just not using them to generate the Next Document Number in the series as they’re not relevant. Anyway. This form/command to get the next number is still broken and Access gives me the following error: “…can’t find the macro ‘DocNumber_Form.’ The macro (or its macro group) doesn’t exist, or the macro is new but hasn’t been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro’s macro group was last saved under.” Huh? I recognize that I’m now beginning to use VBA commands (?) but I haven’t created any macros. Should I? What should they be? This is definitely beyond the realm of my familiarity with Access… Again, my thanks for your help, Dave! |
#6
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
Me.cmdGetNewNumber.Enabled = Me.NewRecord
Doe not go in the properties dialog text box for the current event. You select Code Builder and put it in there. Me. is shortcut for the form name and the correct syntax when using the form name would be: Forms!FormName!ControlName Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. This is contridictory. If you put an expression in the control source of a control, it is not bound to your table and will not update anything. If you want to see what the next number would be, it needs to be in a separate unbound control. But, this could be misleading in a multi user environment. If your have the value stored in a control on your form and another user creates a new record, your number is now incorrect. -- Dave Hargis, Microsoft Access MVP "LianeMT" wrote: I would have a text box on the form for the Number Series. I have this. I would also have a command button positioned next to it to retrieve the next number. I added this. In Design view, set the command button's Enabled Property to No. You only want it to work for new records. This makes sense. I did that. Then in the form Current event enable the button for new records and disable it for existing records: Me.cmdGetNewNumber.Enabled = Me.NewRecord After Access complained about “Me” I guessed that I needed to replace “Me” with the name of my form, thusly: DocNumber_Form.cmdNextNumber_Cmd.Enabled = DocNumber_Form.NewRecord Then put the code in the Click event of the command button. Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. I realize it may seem redundant to have both this “Next Document Number” text box and the “Number Series” text box, but here’s my rationale: The purpose of this form is to populate the list of document numbers as they are being issued. I need to know the Next number in the series when I have to issue a brand new number and I’m only concerned with the Number Base of XXXYYZZZZ. But I also need the ability to add the revision and sequence numbers when existing documents get revised or supplemental pieces are added to them (without issuing entirely new numbers). The form has fields for these two pieces of information; I’m just not using them to generate the Next Document Number in the series as they’re not relevant. Anyway. This form/command to get the next number is still broken and Access gives me the following error: “…can’t find the macro ‘DocNumber_Form.’ The macro (or its macro group) doesn’t exist, or the macro is new but hasn’t been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro’s macro group was last saved under.” Huh? I recognize that I’m now beginning to use VBA commands (?) but I haven’t created any macros. Should I? What should they be? This is definitely beyond the realm of my familiarity with Access… Again, my thanks for your help, Dave! |
#7
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
All right, I found my way into code builder and tried again. I put the
recommended command Me.cmdNextNumber_Cmd.Enabled = Me.NewRecord into the Form_Current section. I put the command NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator] = """ & Me.txtGroupDesignator & """"), 0) + 1 into the NextNumber_Cmd_Click() section (after Dim NextNumber As String) I'm still not getting the command button enabled when activating a new record and don't understand how to report the value for NextNumber. How does one use a separate unbound control to show the value? Thanks. "Klatuu" wrote: Me.cmdGetNewNumber.Enabled = Me.NewRecord Doe not go in the properties dialog text box for the current event. You select Code Builder and put it in there. Me. is shortcut for the form name and the correct syntax when using the form name would be: Forms!FormName!ControlName Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. This is contridictory. If you put an expression in the control source of a control, it is not bound to your table and will not update anything. If you want to see what the next number would be, it needs to be in a separate unbound control. But, this could be misleading in a multi user environment. If your have the value stored in a control on your form and another user creates a new record, your number is now incorrect. -- Dave Hargis, Microsoft Access MVP "LianeMT" wrote: I would have a text box on the form for the Number Series. I have this. I would also have a command button positioned next to it to retrieve the next number. I added this. In Design view, set the command button's Enabled Property to No. You only want it to work for new records. This makes sense. I did that. Then in the form Current event enable the button for new records and disable it for existing records: Me.cmdGetNewNumber.Enabled = Me.NewRecord After Access complained about “Me” I guessed that I needed to replace “Me” with the name of my form, thusly: DocNumber_Form.cmdNextNumber_Cmd.Enabled = DocNumber_Form.NewRecord Then put the code in the Click event of the command button. Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. I realize it may seem redundant to have both this “Next Document Number” text box and the “Number Series” text box, but here’s my rationale: The purpose of this form is to populate the list of document numbers as they are being issued. I need to know the Next number in the series when I have to issue a brand new number and I’m only concerned with the Number Base of XXXYYZZZZ. But I also need the ability to add the revision and sequence numbers when existing documents get revised or supplemental pieces are added to them (without issuing entirely new numbers). The form has fields for these two pieces of information; I’m just not using them to generate the Next Document Number in the series as they’re not relevant. Anyway. This form/command to get the next number is still broken and Access gives me the following error: “…can’t find the macro ‘DocNumber_Form.’ The macro (or its macro group) doesn’t exist, or the macro is new but hasn’t been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro’s macro group was last saved under.” Huh? I recognize that I’m now beginning to use VBA commands (?) but I haven’t created any macros. Should I? What should they be? This is definitely beyond the realm of my familiarity with Access… Again, my thanks for your help, Dave! |
#8
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
On Wed, 30 Jul 2008 12:33:05 -0700, LianeMT
wrote: Thanks, Dave! Here's what I've interpreted from your note. I apologize that my variable names are cumbersome, but they fit a scheme I inherited. From your example: NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc & " And [DocType] = """ & Me.txtDocType & """"),0) + 1 I substituted my variables: NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator] = """ & Me.txtGroupDesignator & """"),0) +1 based on: XXX = Main Designator = ProgramDesignator - MainDoc YY = Document Type = GroupDesignator - DocType ZZZZ = Numeric Series = NumberBlock - series Table/Query = DocNumber_Qry - MyTable I've put this DMax function into an existing form where the administrator inputs the document numbers (populating the table/query for tracking the numbers) hoping to facilitate grabbing the next number being issued. Honestly I didn't know where else to put it. Essentially I would like the administrator to be able to enter the Program Designator (currently 301), and select the Group Designator to kick off the NextNumber being reported in a text field on the form where I've put the DMax function. Then the administrator can use the NextNumber to input the NumberBlock (series) identifier) and the other two fields to add this new number to the database. Unfortunately, I haven't linked the DMax function correctly, because I get the #Name? returned in the NextNumber field instead of the NextNumber I'm trying to report. I'm sure there's a more elegant way to do this, so again, I appreciate any input to either do this better or simply to fix the disconnect I have in the current implementation. Thanks much! I saw this technique mentioned somewhere. Maybe it is something to consider, maybe not: Create a table called DocumentKeys with the fields that make up your key. As an example: CREATE TABLE DocumentKeys ( ProgramDesignator INTEGER NOT NULL, GroupDesignator CHAR(2) NOT NULL, NumberBlock INTEGER NOT NULL, PRIMARY KEY (ProgramDesignator,GroupDesignator)); Function GetDocumentKey(Pdesig As Integer, _ Gdesig As String) As Integer Dim db As DAO.Database Dim rst As DAO.Recordset Dim n As Integer Dim s As String On Error GoTo ErrorHandler Set db = DBEngine(0)(0) s = "SELECT NumberBlock FROM DocumentKeys " & _ "WHERE ProgramDesignator = " & Pdesig & " AND " & _ " GroupDEsignator = '" & Gdesig & "';" Set rst = db.OpenRecordset(s) With rst .MoveFirst n = .Fields(0) .Edit .Fields(0) = n + 1 .Update End With GetDocumentKey = n ExitProcedu On Error Resume Next rst.Close Set rst = Nothing Set db = Nothing Exit Function ErrorHandler: GetDocumentKey = 0 Resume ExitProcedure End Function |
#9
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
The names I used were made up. You need to make the names in the code match
the names of the objects in your form. For example, NextNumber_Cmd appears to be the name of your command button, but the code is: Me.cmdNextNumber_Cmd.Enabled = Me.NewRecord It probably should be: Me.NextNumber_Cmd.Enabled = Me.NewRecord Check your other names. -- Dave Hargis, Microsoft Access MVP "LianeMT" wrote: All right, I found my way into code builder and tried again. I put the recommended command Me.cmdNextNumber_Cmd.Enabled = Me.NewRecord into the Form_Current section. I put the command NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator] = """ & Me.txtGroupDesignator & """"), 0) + 1 into the NextNumber_Cmd_Click() section (after Dim NextNumber As String) I'm still not getting the command button enabled when activating a new record and don't understand how to report the value for NextNumber. How does one use a separate unbound control to show the value? Thanks. "Klatuu" wrote: Me.cmdGetNewNumber.Enabled = Me.NewRecord Doe not go in the properties dialog text box for the current event. You select Code Builder and put it in there. Me. is shortcut for the form name and the correct syntax when using the form name would be: Forms!FormName!ControlName Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. This is contridictory. If you put an expression in the control source of a control, it is not bound to your table and will not update anything. If you want to see what the next number would be, it needs to be in a separate unbound control. But, this could be misleading in a multi user environment. If your have the value stored in a control on your form and another user creates a new record, your number is now incorrect. -- Dave Hargis, Microsoft Access MVP "LianeMT" wrote: I would have a text box on the form for the Number Series. I have this. I would also have a command button positioned next to it to retrieve the next number. I added this. In Design view, set the command button's Enabled Property to No. You only want it to work for new records. This makes sense. I did that. Then in the form Current event enable the button for new records and disable it for existing records: Me.cmdGetNewNumber.Enabled = Me.NewRecord After Access complained about “Me” I guessed that I needed to replace “Me” with the name of my form, thusly: DocNumber_Form.cmdNextNumber_Cmd.Enabled = DocNumber_Form.NewRecord Then put the code in the Click event of the command button. Okay, I did this, too (placed the DMax command syntax from the previous response here). I also then assigned the control source for my Next Document Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command button summons would get reported here. I realize it may seem redundant to have both this “Next Document Number” text box and the “Number Series” text box, but here’s my rationale: The purpose of this form is to populate the list of document numbers as they are being issued. I need to know the Next number in the series when I have to issue a brand new number and I’m only concerned with the Number Base of XXXYYZZZZ. But I also need the ability to add the revision and sequence numbers when existing documents get revised or supplemental pieces are added to them (without issuing entirely new numbers). The form has fields for these two pieces of information; I’m just not using them to generate the Next Document Number in the series as they’re not relevant. Anyway. This form/command to get the next number is still broken and Access gives me the following error: “…can’t find the macro ‘DocNumber_Form.’ The macro (or its macro group) doesn’t exist, or the macro is new but hasn’t been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro’s macro group was last saved under.” Huh? I recognize that I’m now beginning to use VBA commands (?) but I haven’t created any macros. Should I? What should they be? This is definitely beyond the realm of my familiarity with Access… Again, my thanks for your help, Dave! |
#10
|
|||
|
|||
Reporting an incremented "ID" with a sorting requirement
That is not an unreasonable technique. I have seen it used also; however, it
all could be done with a DLookup as I do it to get the current high number and an SQL Update query to save the new number back to the table. Any time you can avoid using a recordset, you should. Not that recordsets are bad, it is just a performance issue. Also, when using Action Queries, the Currentdb.Execute method is the fastest way to do it. It bypasses the Access UI and doesn't trigger warning messages. But, because it doesn't go through Access, but directly to Jet, you need to use th dbFailOnError option to be sure you get an error returned if it fails. But, there is one problem with your code. You are assuming the record will be found. It will not always be, so you have to plan for that. It will not be found for the first occurance of the maing designator and document type combination. -- Dave Hargis, Microsoft Access MVP "Michael Gramelspacher" wrote: On Wed, 30 Jul 2008 12:33:05 -0700, LianeMT wrote: Thanks, Dave! Here's what I've interpreted from your note. I apologize that my variable names are cumbersome, but they fit a scheme I inherited. From your example: NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc & " And [DocType] = """ & Me.txtDocType & """"),0) + 1 I substituted my variables: NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator] = """ & Me.txtGroupDesignator & """"),0) +1 based on: XXX = Main Designator = ProgramDesignator - MainDoc YY = Document Type = GroupDesignator - DocType ZZZZ = Numeric Series = NumberBlock - series Table/Query = DocNumber_Qry - MyTable I've put this DMax function into an existing form where the administrator inputs the document numbers (populating the table/query for tracking the numbers) hoping to facilitate grabbing the next number being issued. Honestly I didn't know where else to put it. Essentially I would like the administrator to be able to enter the Program Designator (currently 301), and select the Group Designator to kick off the NextNumber being reported in a text field on the form where I've put the DMax function. Then the administrator can use the NextNumber to input the NumberBlock (series) identifier) and the other two fields to add this new number to the database. Unfortunately, I haven't linked the DMax function correctly, because I get the #Name? returned in the NextNumber field instead of the NextNumber I'm trying to report. I'm sure there's a more elegant way to do this, so again, I appreciate any input to either do this better or simply to fix the disconnect I have in the current implementation. Thanks much! I saw this technique mentioned somewhere. Maybe it is something to consider, maybe not: Create a table called DocumentKeys with the fields that make up your key. As an example: CREATE TABLE DocumentKeys ( ProgramDesignator INTEGER NOT NULL, GroupDesignator CHAR(2) NOT NULL, NumberBlock INTEGER NOT NULL, PRIMARY KEY (ProgramDesignator,GroupDesignator)); Function GetDocumentKey(Pdesig As Integer, _ Gdesig As String) As Integer Dim db As DAO.Database Dim rst As DAO.Recordset Dim n As Integer Dim s As String On Error GoTo ErrorHandler Set db = DBEngine(0)(0) s = "SELECT NumberBlock FROM DocumentKeys " & _ "WHERE ProgramDesignator = " & Pdesig & " AND " & _ " GroupDEsignator = '" & Gdesig & "';" Set rst = db.OpenRecordset(s) With rst .MoveFirst n = .Fields(0) .Edit .Fields(0) = n + 1 .Update End With GetDocumentKey = n ExitProcedu On Error Resume Next rst.Close Set rst = Nothing Set db = Nothing Exit Function ErrorHandler: GetDocumentKey = 0 Resume ExitProcedure End Function |
|
Thread Tools | |
Display Modes | |
|
|