Can the DMax function be used at table level to create sequential numbers?

[Tophan]

I am trying to convert some of my company’s paper documents to a database and haven’t even been able to start any tables yet because I am stumped already. The database is still a concept on paper and I need help just getting it started.

The form I am trying to convert is called a Confirmation of Verbal Instruction and consists of the following fields
CVI #
Date
Contract
Instruction Issued By
Date Issued
Instruction(s)

On the original form the Instruction(s) field is often written in various parts so for example, CVI #1 could consist of instructions 1 through 4. I wanted to find a way of adding a sequential numbering system so the first table would include the fields CVI#, Date, Contract, Issued By, Date Issued. The second table would be linked to Table 1 via the CVI # and would be the instructions only.

My problem is two-fold. First, I need to find a way to make the CVI # in table 1 sequential for each new contract name. Secondly I also need to add sequential numbering for the actual body of the Instruction which will be in table 2.

At the end I want to be able to look for the last CVI # issued for the focus Contract, find the last number and create the next number in the sequence. When a new CVI is created, the numbering sequence in table 2 to restart…so it should look like

CVI #1 (Contract A)
Instructions 1.1, 1.2….

CVI #1 (Contract B)
Instructions 1.1, 1.2, 1.3, 1.4….

CVI #2 (Contract A)
Instruction 2.1

How do I get the sequential numbers to restart with each new CVI?

I was searching this forum last night and came across a few articles on Autonumbering which I know will not work. I did find an article from BaldyWeb “Custom Autonumbers/Sequential Numbers” which was very helpful but I can’t find a way to use this at the table level.

I can visualize what I want the database to be able to do and how the reports are to look but as I said this database is still on paper only as I can’t figure out how to structure the tables. Any help would be greatly appreciated.

 

[Minty]

What is CVI #1 ? Assuming is it the "top level" document number so as long as it is unique does it matter what it is ?

A lot of people get very hung up on sequential numbers when really all they are is a unique reference - like a car number plate or your NI number.

 

[Tophan]

In the construction industry, the Confirmation of Verbal Instruction or CVI number must be in numerical sequence for each job as these instructions form part of the contract documents and are used to price variations to the original contract. So when the Client comes to site and instructs the Project Manager to change a timber floor to a concrete floor, the Project Manager needs to record this verbal instruction and issue to the architect, the Quantity Surveyor, the Client's accountant, etc.

Therefore, when reviewing the final bill we can refer to CVI # 2 to find when that particular verbal instruction was issued. Using any random numbering system will not work in this instance.

 

[Tophan]

I tried using the indexes section in the tables which will work but I am trying to make it user friendly. Right now I have Contract A and Contract B which both started in December 2015. Contract B is already at CVI #8 whereas Contract A is only at CVI#1. I was wondering if there is a way around a user having to search for the previous number before entering a new record or getting the "The changes you requested to the table were not successful because they would create duplicate values in the index...." message

 

[Minty]

Okay - but would I be correct in assuming its the 1st or 2nd or 10th CVI for that contract ?
If so you still store them with the a unique CVI, a contract number and then use a simple count on your report to number the CVI for that contract. The same can apply to the sub comments.
You would order them by the unique Auto number, but display them with the current count of instructions for that Contract / CVI / Comment

 

[Tophan]

Yes you are correct is assuming its the 1st or 2nd or 10th CVI for the contract. The thing I am trying to create is the unique CVI at the table level which can then be visible during entry at the form level. I want to be able while typing up a CVI to see that I am typing CVI #1 for Contract A. Other users should be able to go into the Form, find a particular CVI number and then price it. The report will be generated at the very end when all pricing has been completed so the numbering sequence in the report is not as important as getting a numbering sequence at the table/form level.

 

[Minty]

You can use a DCount to provide the correct number on the form, don't store it. This has the advantage that if for any reason you delete a CVI your numbers won't go out of sequence.

Otherwise you will have to implement some clever coding to keep track of the stored numbers at any point they might get updated or deleted. You can't do this at table level - hence it being better to use and autonumber then simulate the sequence afterwards based on either an entry date or the autonumber.

 

[Tophan]

The thing is, if a CVI is deleted I would WANT the numbering to be out of sequence. A missing CVI number would raise a red flag because once entered it shouldn't be deleted. If an instruction is withdrawn it should be marked as cancelled but not deleted, therefore when the final account is being prepared all paperwork will be in order and no questions raised.

So far the indexes feature in the tables is coming close to what I want it's just that I still have to manually enter the CVI number as opposed to one being automatically generated in sequence.

 

[sneuberg]

We have similar problems in one of our systems. The system must produce sequential UPCs and product numbers. This is further complicated by being a multi-user system. If you have this situation then DMax probably won't work well for you as there's the potential of ending up with duplicates.

We store the next available number in a table and then use code like that which follows to get a number. This code use a table (AutoProductNumberTBL) with two numeric fields, NextProductNumber and LastProductNumber. The LastProductNumber restricts the range of the number. You probably wouldn't need to do that in your application.

This was implement on a form level. I don't know of anyway you could do this in Access at a table level.

Public Function GetProductNumber() As Integer
                      
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ws As DAO.Workspace
Dim strSQL As String

On Error GoTo GetProductNumber_Err

Set ws = DBEngine(0)
Set db = CurrentDb
strSQL = "SELECT * FROM AutoProductNumberTBL;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbDenyRead Or dbDenyWrite)
DBEngine.Idle dbRefreshCache         ' refresh read cache
ws.BeginTrans
    If rs![NextProductNumber] <= rs![LastProductNumber] Then
        GetProductNumber = rs![NextProductNumber]
        rs.Edit
        rs![NextProductNumber] = rs![NextProductNumber] + 1
        rs.Update
    Else
        GetProductNumber = 0  'Return zero string if no more product numbers
    End If
ws.CommitTrans dbForceOSFlush        ' flush the lazy-write cache
rs.Close
db.Close
Exit Function

GetProductNumber_Err:
  Select Case Err.Number
    Case 3008, 3009, 3189, 3211, 3260, 3261, 3262
      ' various locking errors (above)
 
        MsgBox "Table was locked.  Try later."
        Exit Function
   
    Case Else  ' unhandled errors
      MsgBox Error$
  End Select

End Function

 

[Tophan]

Wow...that's amazing! For me to achieve the end result I am looking for I need to find a way to number at the table level. Numbering at table level also allows me to enter activities/instructions in the order in which they are to be physically carried out; also it would give some flow to the printed reports

It's so frustrating because I can think of different types of formulas to achieve what I want but none can be used in the calculated field of a table :banghead:

 

[sneuberg]

I suspect you could do this if you used an SQL Server backend. I believe it has table level triggers.

What end result are you looking for that couldn't be accomplish through a forms interface? Usually Access developers don't want their users to have table level access and jump through hoops to hide the navigation pane.

 

[Tophan]

I haven't even tried visualizing the forms yet cause I am stuck at the tables. Looking back at some of Minty's suggestions I can see how to create numbering in the secondary form. I just need my primary form to work first.

I'm going to keep trying a few things...thanks so much for both yours and Minty's suggestions...I'll let you know if I have any success

 

[pbaldy]

To throw out an untested alternative, sneuberg mentioned SQL Server triggers. Later versions of Access have data macros, which can loosely be thought of as similar to triggers. I have no idea if the DMax() functionality can be done in a data macro, but you may want to explore it. As others have mentioned, I'd be doing it in the form users added records with.

 

[Tophan]

Thanks...I was just investigating the macros option in the table to see what functions are available. Got some reading to do this weekend.

By the way...thanks for posting the "Custom Autonumbers/Sequential Numbers" article...really helped clarify a few things.

I have a couple ideas I want to try that may be a work around to what I am trying to do...this is not going to defeat me!

 

[pbaldy]

Glad it helped! JasonM gets credit for that one.

 

[The_Doc_Man]

OK, let's clear some air. You don't have a calculated field in a table (which we would tell you is wrong.) You have a contractual requirement for an ORDINAL INDEX in a table. Autonumber will not work correctly for this but there are many ways to accomplish your goal.

You are worried about people doing this wrong so that you have inconsistent numbering. The only way to assure that this is always done correctly is to NEVER allow it to be done incorrectly. (OK, sounds tautological, but I have an ulterior motive.) That means that you must NEVER allow your users to enter the CVI from anything other than a form that does the CVI entry and index computations for you. Murphy's Law (what can go wrong WILL go wrong) applies here - but nobody ever remembers the 2nd have of Ed Murphy's quote... "so don't ALLOW things to go wrong."

Your first post said that this is still a concept on paper so I know you aren't yet implementing what I'm talking about. You will see discussions on this forum about "switchboards" or "dispatcher forms" used in the sense that the user NEVER EVER sees the database tables, queries, etc. S/he ONLY sees this control form that has a bunch of buttons or clickable hot-spots where you click the right spot and some action-oriented form comes up, and you perhaps take the trouble to maximize the action form and force it to the top of the screen stack (see "Bring to Top" as a lookup topic.) That way, the user never sees anything but forms. Of course, the controlling form never closes until you click an EXIT button or a GO AWAY button or some other appropriate verbiage. And when the form DOES go away, it closes the application at the same time so that your user STILL doesn't see behind the scenes.

IF you implement this project so that your users can't get to the inner workings of the database without having some kind of privileges or special accounts, then a lot of things can be done inside the FORMS that could not so easily be done by directly mucking tables. The forms have LOTS of "hooks" where you can hang some useful code.

For instance, the predicament you describe, of wanting unique, sequential numbering for CVIs associated with a contract? If you design a CVI entry form, consider this design: Put a combo box on the form to let you select the contract. You can choose a "Click" event or an "Update" event or even just use a "LostFocus" event to have the form look up what it needs to look up about that contract including doing something like:

CurMax = DMax("[CVINum]","[CVITable]", "[ContractNumber] = " & Me![ContractNumber] )

In this model you have put all CVIs in one table but with the contract number as PART of the information you need (and the CVI number to keep it unique). Then you can do a selective DMax (Domain Aggregate) call to find out the highest CVI number currently assigned for that contract.

If you then wish, you can also fill in a box on the form (still as part of the event routine) to say that the next CVI number for this contract is N (where you compute N as CurMax+1 or something like that. But until someone enters your CVI text and clicks a "SUBMIT" button, you don't store the number. Inside the button-click code you have time enough to compute the number you want to store, even to the point that you can re-evaluate that number just in case someone else was entering a CVI for the same contract from another terminal.

From your introductory comments, you are the stage right now where one of my Old Programmer's Rules strongly applies: "If you can't do it on paper, you can't do it in Access." As long as your paper diagrams and descriptions leave you unsure about how to approach a problem, Access won't help you because your problem isn't technical - it is procedural. Once you know what you need to go into the database and how to get it out in the way you want - for every case of "what you need" - then you are ready to implement something.