Engineering Document Manager - finding next available number

[mmaurigi]

If I did it correctly, attached you'll find the database as it currently exists.



Thanks for all the help so far, maybe when you see the database you can suggests improvements. I'm not an expert at this by no means. Also, the sequential number that I would like the program to provide, is only required if the user selects "Standard" from Document Category dropdown.

[Micron]

Some quick comments based on observations.
you have a lot of blank table space
you should have tables for Owner, Client like you did for document and equipment type. With these tables, you'd exert more control over data entry, otherwise, you will probably end up with situations where records don't appear due to data entry errors - like when someone enters Arcedis instead of Arcadis for the job name. These values would be entered in a separate process for where new values are required.
you should fix your tab order
you have an entirely blank row (#8) and zeros for years
is every possible combination of document type and category allowed? If not, the child list should be dependent on the parent list
do you really want to allow a new record when so much information can be missing?
consider having the ability to filter the report rather than present all 240+ pages by default
you will never be able to search on GWTT (whatever that is) without some fancy coding. Is that OK?
consider a datasheet view or listbox for your search results. User could select the one desired rather than have to click through many records one at at time to find the one they want. That's all for now - gotta run. Did not look at any code yet.

[Micron]

re prior comment on filtering report: I see that it is filtered only if you filter the main form first

It's confusing as to whether the database is about documents or drawings
- document number (and maybe other details) should be protected against accidental edits. Consider providing two modes for opening this form: view (all locked) and edit (maybe some locked).
- you have a mix of code and embedded macros. I guess that's not a huge issue, but macros have no error handling capability AFAIK. I may be wrong because I don't use them, thus are not overly familiar with any new developments for fixing their short comings (merely presenting an error message doesn't count).
- objects have obscure names (e.g. Command12) which makes it hard to troubleshoot. This will come back to haunt you (or your successor).
- calculated fields are a big no-no in databases. A key aspect of DrawingData is that you have a concatenated field, which IMO is just as bad. If one of the elements in the concatenation has to change, you cannot fix this value very easily. I would separate the elements into fields and concatenate the parts in the form control(s). Thus if ST ever had to be come STA because you added STB, STC, and STE (we don't like STD's) more on this in the next observation.

- while you have separate tables for document, category and equipment types, you are storing those values in the document table rather than ID's. The significance of this is that if for any reason a type table entry is renamed, you have no way of updating that value throughout DrawingData. There are two fixes for this that come to mind:
1) use type ID fields instead of the actual values. You cannot because you have no ID fields.
2) create table relationships and enforce referential integrity so that updates are propagated throughout DrawingDataHowever, you cannot do this because
a) you have not indexed the value field in any type table
b) you permit DrawingData records to have missing values
IMO, you should at least index the type number fields and enforce referential integrity (create table relationshis).
My preference would be to add autonumber ID fields to the type tables and store the ID's in DrawingData so that any edits to types are automatically represented by the relationship between the ID and its associate value. IIRC, employing referential integrity can introduce some hiccups in data updates (I rarely use RI so I might have a fuzzy recollection on that notion).

- further to the above, DrawingData should have required fields to prevent null data in at least the key fields or coding in the creation process that prevents this.
- on main form, there is code for command154 and cmdReport but no such objects
- consider a separate report generating process with filtering; you might want to have more reports later, and a button on main form for each report might result in crowding

Last but not least, you were given the solution to create your document/drawing number using the next available number but I don't see this part anywhere. Are you expecting someone to incorporate this into your project for you?? I ask because I have not figured out how those values got into the DrawingData table.

[mmaurigi]

Micron:

Thank you for your comments. I understand there are many flaws in how my existing database is created. I am the first to admit that developing these databases is not my strong point. I don't understand the code very well and what you see in my database is a result of internet searches and asking an enormous amount of questions. For example, I agree that document number (and maybe other details) should be protected against accidental edits, The tab order needs to be fixed, but I have no clue on how to right the code for either of these situations. When "Standard" is selected from the Document Category dropdown, that is the only time a sequential number is required as part of the Document Number.

[Micron]

my database is a result of internet searches

Nothing wrong with that. I still rely on the web community for info. You could do that for each recommendation, but here's an answer for one or two:

Tab order: form design view, property sheet, Other tab. Tab stop has to be Yes; easiest is to click the ellipse button (...) for any control tab order to get a list of controls whose tab setting you can re-order. Usual approach is to set a form close button to be first (you will see that first is 0).

Document number based on category: as mentioned, I do not see how you got the concatenation into the table because you haven't used the code I provided, nor does it seem to be handled by any of your embedded macros. You must have dumped the data into your table before posting your db. It is easy to control this, such as incorporating a slight change into what you were already given:

 If Me.Combo141  = "Standard" Then
    sql2 = "INSERT INTO tblDocNum (DocCategory,DocType,DocNum) VALUES ('" & svCat
    sql2 = sql2 & "','" & svType & "','" & svNum & "')"
    db.Execute sql2, dbFailOnError
    Me.txtDocNum = svCat & "-" & svType & "-" & svNum
    rs.Close
    Set rs = Nothing
    Set db = Nothing
Else
   do something else (which has not been revealed)
End If

[sushilkadam]

Hi Everyone
I need your help on this topic
please respond

[ArviLaanemets]

I foresee a future problem with your sequential document number. Unless you plan your app being in use some limited time range only, there will be probably some time when you'll have 999999th document registered - and what do you after that?

Much easier is to determine a realistically possible maximal number of documents registered in year/month/day. Add an order to this number to be on safe side, and your document number will be either in format like yyyynnnn or yyyymmnnn or yyyymmdnn. You can keep same numbering for all document for same period, or you can have separate numbering for every category (and type).

To get this number whenever user registers a new document an event must be started. The best way will be to disable all regular controls o form and set a hidden button for document registering visible. Document category and type must be determined before (e.g. as linked fields from parent form), or they must be selected before the buttons enabled. The click event of button then queries the table for max value of [n] part for current period, and adds 1 to it. After that the new document number is composed, the controls for fields required for registering (category, type, number/ID) are evaluated (NB! Controls! Not fields!), other form controls are set enabled/disabled as you want, and he registering button is set invisible. User can always break the registering until he record is not saved pressing Esc twice.

In case you use single numbering for period, you can consider another approach. You have table with fields like Period, Number, and InUse. Whenever any user starts the app, an Open event checks this table for current period, and when it is not found, deletes all previous entries and fills the table with all possible numbers for current period.
Now all you need is BeforeUpdate Event of form to check the document number control, and when it is empty, read a free number from this table, calculate and evaluate document number for according control, mark the number in table as in use, and proceed with saving.