[AccessD] Can I do this?

[Stuart McLachlan]

I agree 100% with the danger of DMax in a multui-user situation.  You need a function that 
grabs the next  avaiable number and immediately update your FileSql_No with error trapping.

(At times a second user *will* grab the FileSeq_No between the time a first user does and the 
time that first user writes the next higher number in.   You need to trap this condition, a 
unique index on FileSeq_No will do it, and try again until you can successfully save the new 
number.)


Also, I wouldn't store the file number like that. I would store the three components as 
separate numeric fields and build the complete number on the fly for display purposes.  That 
will make it much easier to filter for all Jun 2015 files for example.



On 2 Jun 2015 at 17:37, Jim Dettman wrote:

> 
>  Not sure I'm following the question...
> 
>  But first comment, if this is a multi-user situation, then using
>  Dmax() is
> unreliable.
> 
>  I would have a procedure to generate the sequential number based on a
>  value
> stored in a table.
> 
>  No need for all the unbound fields, as you can simply do:
> 
>  =GenerateKey("tblFiles") & "-" & Month(Date()) & "-" Day(Date()) 
> 
>  You now have your key.  With that, you can simply save it into the
>  main
> table with either a SQL Insert or use a record set addnew.
> 
>  I have code for the GenerateKey() routine if you want it.
> 
> Jim.
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Tina Norris Fields Sent: Tuesday, June 02, 2015 11:51 AM To:
> DatabaseAdvisors-Access Subject: [AccessD] Can I do this?
> 
> I'm making a database for a local law firm.  They have in place a
> paper system that is complex, but, it works.  They are sort of ready
> to switch to an electronic system, but, it has to look just like the
> paper system already in place.  Once they know they can trust an
> electronic system, they'll allow remodeling to simplify.  That's the
> situation.  I'm working on a particular part of the system now, and
> could use some help.
> 
> A new file number is created from a six-digit sequential number, a
> dash, a two-digit number for the month, a dash, and a two-digit number
> for the year, resulting in numbers (short text) such as this: 
> 011456-05-15.
> 
> I've made a form that has unbound controls for crafting the number. A
> textbox txtMaxSeqNo displays the value DMax("File_SeqNo","tblFiles")
> as a starting point. A button btnMakeNext runs the Event Procedure:
> Private Sub btnMakeNext_Click()
>      TF_number.Value = Format(Int(txtMaxFileSeqNo) + 1, "000000")
>      TF_month.Value = Left(CStr(Date), 2) TF_year.Value =
>      Right(CStr(Date), 2) TF_Date.Value = Date
> End Sub
> 
> I want to add these values as a new record to the existing tblFiles. 
> Can I make a temp table from these values, append them to the real
> table, and delete the temp table?  Can I craft a query from these
> unbound control values and use it to make a temp table, or to append
> directly to the real table?  I'm not seeing how to capture the values
> from this form and put them eventually into the the real table.
> 
> Ultimately, of course, all this will be behind the scenes, attached to
> a click event, but, while I'm building it, I want to see each step
> succeed before moving on.
> 
> I'll appreciate ideas.
> 
> TNF
> 
> -- 
> Tina Norris Fields
> tinanfields-at-torchlake-dot-com
> 231-322-2787
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>