Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006

    Unanswered: Trying To Develop A New Method For Creating Documents In My DB

    i am trying to make a new way to make documents in my database. we have 3 document types. FAX, RFI or TQ. each one then has a number, for example 10501. The numbers are only used once in the office (each document has it own set though, so there can be a F-10501 and a RFI-10501). Each document is linked to a job. For each job, we organise the documents with a file number.

    So, when the user presses the "New Document" button, a small form appears asking them to choose which document they would like to make. once this happens, the database must then get the job number. this is easy as the user will be "in the job" when they click the button. It then needs to use the job number and find the next un-used (highest) file number. then, it needs to find the next highest unused document number for the selected document type. then with these 4 bits of information (doc type, doc number, job number and filenumber), it must make a new record, then update the form so that the user can enter the rest of the data associated with the document, such as who it is doing to and the main body etc.

    so how can i do this only using code? i cannot work out how to do it. the current way i do it is buy opening numerous 'invisible' forms and organising the data. the first form shows the documents of all jobs, then filters by the required type, then organsises by the number, then adds 1 to the bottom record (the highest current number). then another form does a similar thing for the file number. Its pretty silly and i would like to fix it.

    here is the code i have so far
    Dim NewJobNo, NewDocNo, NewFileNo As String

    NewJobNo = Me![Job No]


    MsgBox "Job Number: " & NewJobNo & Chr(13) & "Doc Number: " & NewDocNo & Chr(13) & "File Number: " & NewFileNo
    the last line is just to check that i have all the right data can anyone help please. i have attached a chopped down version of the database for you to see.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    have a look at the attached and see if it gives you any inspiration.

    basic gameplan is a one-record table holding the next serial number combined with an optimistic concurrency "lock" to prevent two users getting the same serial#.

    demo uses DAO and makes no attempt to automatically reference DAO360 so you will need to manually reference in A2k

    Attached Files Attached Files
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts