[AccessD] Can I do this?

[Tina Norris Fields]

Hi Stuart,
Thanks to both you and Jim for the caution about DMax().  This will be a 
multi-user situation, so I appreciate the warning.
You're absolutely right about the need for error trapping; thanks for 
the suggestion to use a unique index on File_SeqNo.
Yeah, about the completed file number - I like your idea better, too.
I'll probably be back for more help in the next bit.  Thanks again.
TNF

Tina Norris Fields
tinanfields-at-torchlake-dot-com
231-322-2787

On 06/02/15 5:59 PM, Stuart McLachlan wrote:
> 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
>>
>