05-04-06, 22:14 #1Registered User
- 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]
'#INSERT MORE HERE#
MsgBox "Job Number: " & NewJobNo & Chr(13) & "Doc Number: " & NewDocNo & Chr(13) & "File Number: " & NewFileNo
05-05-06, 14:00 #2Cavalier King Charles
- 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
izycurrently using SS 2008R2